select @f_name=f_name FROM dbo.tbl_user_profile WHERE user_id = @user_id;
END
RETURN
The proc runs perfectly from the Management Console. In my ASP.net form (C#), the following always results in: "Invalid attempt to read when no data is present." (I'll highlight the line where the error results):
SqlConnection conn = new SqlConnection(<connection string data>);
SqlDataReader reader;
SqlCommand comm = new SqlCommand("dbo.PayWeb_getUserProfile", conn);
comm.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter f_name = new SqlParameter("@f_name", SqlDbType.VarChar, 50);
comm.Parameters.Add("@user_id_str", SqlDbType.VarChar, 36).Value = Membership.GetUser().ProviderUserKey.ToString().ToUpper();
reader = comm.ExecuteReader();
Response.Write("Num: " + reader["@f_name"].ToString()); // ERROR: Invalid attempt to read when no data is present.
reader.Close();
conn.Close();
The data is there. When I put a watch on 'Reader', I see the correct first_name data there.
For a particular application i retrieve records using stored procedure and bind the same to the data grid and display the same. The data are fetched from a table say A joined with couple of tables to staisfy the requirement. Now this table A has around 3700000 + records. The Select statement is as
Select column1, column 2, column 3...................... column 25 from table A
inner join hash table D on A.Column3 = D.Column3 and A.Column4 = D.Column4 and a.nColumn1 = @ParameterVariable Inner join table B on a.nColumn1 =b.nColumn1 inner join table C on A.column2 = C.column2
Indices defined on A are IDX1 : Column 3, Column 4 IDX2 : Column 1
Around 350 records per second are inserted into the table A during the peak operation time. During this time when executing the procedure, it takes around 4 mins to fetch just 25000 records.
I need to fine tune the procedure. Please suggest me on same.
Hi i have the following stored procedure which should retrieve data, the problem is that when the user enters a name into the textbox and chooses an option from the dropdownlist it brings back duplicate data and data which should be appearing because the user has entered the exact name they are looking for into the textbox. For instance Pmillio Jones Pmillio Jones Pmillio Jones Robert Walsh Here is my stored procedure; ALTER PROCEDURE [dbo].[stream_UserFind] -- Add the parameters for the stored procedure here @userName varchar(100), @subCategoryID INT,@regionID INT AS SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName, Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategoriesON Users.userID= UserSubCategories.userIDINNER JOIN SubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHEREuserName LIKE COALESCE(@userName, userName) OR SubCategories.subCategoryID = COALESCE(@subCategoryID,SubCategories.subCategoryID);
Hi. I have a stored procedure "sp1" which returns a value (with the sql statement Return @ReturnValue). Is it possible for my asp.net page to retrieve this return value, and to do it declaratively (meaning without writing code to connect to the database in the code behind). If it is possible to do it like this please tell me how, and if not please tell me how to do it with code. Thanks in advance .
Is there a way to retrieve the parameter list for a given stored procedure?
I am trying to create a program that will autogenerate a list of stored procedures and their parameters so that changes to the database can be accurately reflected in code.
We have a domain where all computers are on GMT(Greenwitch Mean Time). We have an access front end that timestamps certain fields according to the client time that the program is running on, but we will be moving our client workstations off of GMT time and keep our SQL Server on GMT time, and want to keep the timestamps GMT.
So, I wanted to know if it was possible to create a stored procedure that gets the Server's time and returns it to the Access frontend for entry into the timestamp fields?
Or, if anyone has a better idea of how to get the time from the server to use on the clients, I would greatly appreciate it!!!
Please excuse me if this question has been asked before; I couldn’t find it. Perhaps someone could point me to the solution. A few years ago I wrote an order-entry application in classic ASP that I am now re-writing in ASP.NET 2.0. I am having very good success however I can’t figure out how to retrieve data from a stored procedure. I am using the FormView & SqlDataSource controls with a stored procedure to insert items into an order. Every time an item is inserted into the order the stored procedure does all kinds of business logic to figure out if the order qualifies for pricing promotions, free shipping, etc. If something happens that the user needs to know about the stored procedure returns a message with this line (last line in the SP) SELECT @MessageCode AS MessageCode, @MessageText AS MessageText I need to retrieve both the @MessageCode and the @MessageText values in my application. In classic ASP I retrieved these values by executing the SP with a MyRecordset.Open() and accessing them as normal fields in the recordset. In ASP.NET I have specified the SP as the InsertCommand for the SqlDataSource control. I have supplied all the parameters required by my SP and told the SqlDataSource that the insert command is a “StoredProcedure�. Everything actuly works just fine. The items are added to the order and the business logic in the SP works fine. I just have no way of telling the user that something important has happened. Does anyone know how I can pickup these values so that I can show them to my users? Bassicly if @MessageCode <> 0 I want to show @MessageText on the screen.
I'm generating a list of parameters needed by stored procedures, and I'd like to know which ones have default values assigned to them. To retrieve the parameter information I use:
sp_sproc_columns @Procedure_Name='InsertUser''
However, the column that is supposed to give the default value, 'COLUMN_DEF' always returns as NULL, even when that column has a default value assigned to it. i.e. CREATE PROCEDURE InsertUser@UserID INT = 10,.....
And then if I do a sp_sproc_columns @Procedure_Name='InsertUser'', the COLUMN_DEF value for the @UserID column is still NULL.
Does anyone know what I'm doing wrong and how I can retrieve the default value?
Hello!I use a procedure to insert a new row into a table with an identitycolumn. The procedure has an output parameter which gives me theinserted identity value. This worked well for a long time. Now theidentity value is over 700.000 and I get errors whiles retrieving theinserted identitiy value. If I delete rows and reset the identityeverything works well again. So I think it is a data type problem.My Procedure:create procedure InsertProduct@NEWID int outputasbeginset nocount oninsert into PRODUCT(D_CREATED)values(getdate()+'')set nocount offselect @NEWID = @@IDENTITYendMy C# code:SqlCommand comm = new SqlCommand("InsertProduct", sqlCon);comm.CommandType = CommandType.StoredProcedure;comm.Parameters.Add(new SqlParameter("@NEWID",System.Data.SqlDbType.Int)).Direction =System.Data.ParameterDirection.Output;try{SqlDataReader sqlRead = comm.ExecuteReader();object o = comm.Parameters["@NEWID"].Value;//...}catch ( Exception ex ){throw ex;}The object o is alwaya System.DbNull. I also tried to use bigint.Any hints are welcomeCiaoSusanne
We are using a simple CLR sp where an SQL statement is executed inside the same. THe execution happens in a server of different context that is connected inside CLR sp. The result set is returned in tabular format using pipe. But, when we access the result as below
it give the following error. Any help is appreciated. It seems to be some error with distibuted transaction. We tried adding BEgin Distributed trnasaction, but still the error comes.
Msg 6522, Level 16, State 1, Procedure TestProc, Line 0 A .NET Framework error occurred during execution of user-defined routine or aggregate "TestProc": System.Transactions.TransactionException: The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025) ---> System.Runtime.InteropServices.COMException: The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025) System.Runtime.InteropServices.COMException: at System.Transactions.Oletx.ITransactionShim.Export(UInt32 whereaboutsSize, Byte[] whereabouts, Int32& cookieIndex, UInt32& cookieSize, CoTaskMemHandle& cookieBuffer) at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts) System.Transactions.TransactionException: at System.Transactions.Oletx.OletxTransactionManager.ProxyException(COMException comException) at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts) at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx) at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx) at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction) at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open()
I am accessing data (YTD Hours for a selected employee) through the view "dbo_TIMS_HSTU_HoursHist_Insync" which is connected by ODBC to my application. The result "HSTUHoursPriorPeriods" is used to determine the rate of pay for a specific employee and is run literall each time a user enters a pay record at data entry.
Because the view itself returns about 1,000 records, before I select the specific record and associated Hours, it is a little slow from a data entry standpoint.
I understand I can achieve the same result through an SQL Procedure, but for the life of me I don't know how.
Hi all..I'm trying to send multiple INT values to a Stored Procedure that willbe handled in an IN statement.ASP Code:strSQL = "SP_Get_Selections '29, 32'where 29 and 32 are 2 integer valuesNow, in my stored procedure... I would like to look these values up inan IN statement likeCREATE Procedure SY_GET_SELECTIONS@authorid varchar(20)SELECT * FROM Authors WHERE AuthorID IN (@authorid)But when I use this, I get [Microsoft][ODBC SQL Server Driver][SQLServer]Syntax error converting the varchar value '29, 32' to a columnof data type int.Any help would be great.Thanks
Code: USE [byrndb] GO /****** Object: StoredProcedure [dbo].[sp_GetLikeJobsByJobNumber] Script Date: 08/28/2012 15:17:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
Why when I run
Code: USE [byrndb] GO DECLARE@return_value int EXEC@return_value = [dbo].[sp_GetLikeJobsByJobNumber] @number = N'23913' SELECT'Return Value' = @return_value GO
Am I getting an "Msg 8114, Level 16, State 5, Procedure sp_GetLikeJobsByJobNumber, Line 16
Error converting data type varchar to numeric." error? Line 16 is "@number varchar = null"
I am working on a project to write SSIS packages to update a SQL Server 2005 database with data from an Oracle 10g database. Unfortunately, our development ETL box is running 32 bit Windows Server 2003 while the production ETL box is running 64 bit Itanium Windows Server 2003. We have created SQL Server Agent jobs to execute all of our packages. All of our jobs run without error on the 32 bit development box, but in our preproduction tests they are failing in the 64 bit box.
In my testing, I've found that I can successfully pull character data from Oracle. For example, I can write a package that retrieves the data for the following query:
SELECT 'test' FROM dual
However, the following fails:
SELECT 1 FROM dual
I have logging turned on in my package. When the package fails, I typically see informational messages in the log up to the point of failure, but no actual error event logs are generated. The only error information I have is in the SQL Server Agent job history log, which contains the following:
Executed as user: SERVERNAMESYSTEM. The return value was unknown. The process exit code was -2147483646. The step failed.
I have also tried using the 64 bit version of the DTS Wizard to create a sample package, and observe the same behavior. When I run the DTS Wizard, queries that return numeric data cause DTSWizard.exe to exit and produce the following message in the application event log:
Event Type: Error Event Source: .NET Runtime 2.0 Error Reporting Event Category: None Event ID: 1000 Date: 2/1/2007 Time: 12:36:13 PM User: N/A Computer: SERVERNAME Description: Faulting application dtswizard.exe, version 9.0.2047.0, stamp 443f5e50, faulting module oracore10.dll, version 10.2.0.1, stamp 435841b0, debug? 0, fault address 0x00000000001e4910.Has anyone else experienced similar behavior, and know of a solution aside of wrapping all retrieval of numeric data in to_chars in our Oracle queries?
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.
I am building a stored procedure that changes based on the data that is available to the query. See below. The query fails on line 24, I have the line highlighted like this. Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.
What I am looking to do is use a complicated stored procedure to getdata for me while in another stored procedure.Its like a view, but a view you can't pass parameters to.In essence I would like a sproc that would be like thisCreate Procedure NewSprocASSelect * from MAIN_SPROC 'a','b',.....WHERE .........Or Delcare Table @TEMP@Temp = MAIN_SPROC 'a','b',.....Any ideas how I could return rows of data from a sproc into anothersproc and then run a WHERE clause on that data?ThanksChris Auer
I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task. I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert. Thanks
Hi, somebody can tell me how to create a correct Stored procedure (with commit and rollback) that return errors to my code for save it in a log file .... I would like to know the right method for a SP with parameters and return error value Thanks
hi, i want to take the first n values from my Categorii table, here is my stored procedure: SELECT ROW_NUMBER() OVER (ORDER BY CategoryID) AS RowNumber, CategoryID, Name, Description FROM Categorii WHERE DepartamentID = @DepartamentID AND RowNumber <= 5 i get the error: invalid column name RowNumber why? what should i do? if i execute the procedure without the AND RowNumber <= 5 i get the RowNumber values 1 to 9 (that means it works)...but what should i do to retrive only the first n? thank you
I've a stored procedure which returns values based on 7 criterias. It was working fine and returned the values properly. I added one more criteria for returning values from 2 database columns based on minimum and maximum values. It's not working properly and gives syntax error. Could someone tell me what mistake I'm doing? Thanks. ALTER procedure [dbo].[USP_Account_Search_Mod] @ClientCode VARCHAR(7) = '' ,@DebtorName VARCHAR(25) = '',@DebtorNumber INT = 0 ,@AccountNumber VARCHAR(30) = '' ,@ReferenceNumber VARCHAR(30) = '',@Tier INT = 0 ,@Status VARCHAR(5) = '' ,@UserID INT ,@Month DateTime = NULL ,@FromDate DateTime = NULL ,@ToDate DateTime = NULL,@OriginalMin decimal = 0 ,@OriginalMax decimal = 0,@CurrentMin decimal = 0 ,@CurrentMax decimal =0 ,@lstAmountSelect VARCHAR(3),@IsActive bit = 1
ASDECLARE @SQLTier1Select VARCHAR(2000) ,@SQLTier2Select VARCHAR(2000) ,@Criteria VARCHAR(2000) ,@SQL VARCHAR(8000) ,@CRI1 VARCHAR(100) ,@CRI2 VARCHAR(100) ,@CRI3 VARCHAR(100) ,@CRI4 VARCHAR(100) ,@CRI5 VARCHAR(100) ,@CRI6 VARCHAR(200) ,@CRI7 VARCHAR(500) ,@CRI8 VARCHAR(500) ,@CRI9 VARCHAR(500) SELECT @CRI1 = '' ,@CRI2 = '' ,@CRI3 = '' ,@CRI4 = '' ,@CRI5 = '' ,@CRI6 = '' ,@CRI7 = '' ,@CRI8='' ,@CRI9='' ,@Criteria = '' SELECT @DebtorName = REPLACE(@DebtorName,'''',''''''); Print @DebtorName if(SELECT UserTypeID FROM dbo.tbl_Security_Users Where UserID = @UserID) = 3 AND @ClientCode = '' return (-1)IF LEN(@DebtorName) > 0 SET @CRI1 = ' AND Name like ' + '''%' + @DebtorName + '%'''IF @DebtorNumber > 0 SET @CRI2 = ' AND Number = ' + CAST(@DebtorNumber AS VARCHAR(7))IF LEN(@AccountNumber) > 1 SET @CRI3 = ' AND AccountNumber like ' + '''%' + @AccountNumber + '%'''IF LEN(@ReferenceNumber) > 0 SET @CRI4 = ' AND Account like ' + '''%' + @ReferenceNumber + '%'''IF LEN(@ClientCode) > 1 SET @CRI5 = ' AND Customer = ' + '''' + @ClientCode + '''' SET @Status = RTRIM(@Status) IF ((@Status Not IN ('ALL','ALA','ALI')) AND (LEN(@Status)>1)) BEGIN
IF(@Status = 'PAID') SET @CRI6 = '' IF(@Status = 'CANC') SET @CRI6 = ' AND Code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryCancelledT1 = 1 OR SearchCategoryCancelledT2 = 1)'
END --PRINt @CRI6IF LEN(CONVERT(CHAR(8), @Month, 112)) > 0 BEGIN
IF(LEN(CONVERT(CHAR(8), @FromDate, 112)) > 0 AND LEN(CONVERT(CHAR(8), @ToDate, 112)) > 0 ) BEGIN SET @CRI7 = ' AND Received BETWEEN ' + '''' + CONVERT(CHAR(8), @FromDate, 112)+ '''' + ' AND ' + '''' + CONVERT(CHAR(8), @ToDate, 112) +''''END ELSEBEGIN SET @CRI7 = ' AND DATEPART(mm, Received) = DATEPART(mm, ' + '''' + CONVERT(CHAR(8), @Month, 112) + '''' + ') AND DATEPART(yy, Received) = DATEPART(yy, ' + '''' + CONVERT(CHAR(8), @Month, 112) + ''''
END END IF @lstAmountSelect='ALL' SET @CRI8='' else IF @lstAmountSelect = 'DR' BEGIN SET @CRI8=' AND OriginalBalance >= '+ convert(Varchar,@OriginalMin) + 'AND OriginalBalance<=' + convert(Varchar,@OriginalMax)+' AND CurrentBalance >= '+ convert(Varchar,@CurrentMin) + 'AND CurrentBalance<=' +convert(Varchar,@CurrentMax) END ELSE IF @lstAmountSelect = 'OLC' BEGIN SET @CRI8=' AND OriginalBalance < CurrentBalance ' END ELSE IF @lstAmountSelect = 'OGC' BEGIN SET @CRI8=' AND OriginalBalance > CurrentBalance ' END ELSE IF @lstAmountSelect = 'OEC' BEGIN SET @CRI8=' AND OriginalBalance = CurrentBalance ' END SELECT @Criteria = @CRI1 + @CRI2 + @CRI3 + @CRI4 + @CRI5 + @CRI6 + @CRI7 + @CRI8
--PRINT @Criteria --PRINT @CRI7 if @Status = 'ALL' OR @Status = 'ALA' OR @Status = 'ALI' --All Period BEGIN if(@Status = 'ALL') --All Active BEGIN SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryAllT1 = 1)'
SELECT @SQLTier2Select = 'SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryAllT2 = 1)' END if(@Status = 'ALA') --All Active BEGIN SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryActiveT1 = 1)'
SELECT @SQLTier2Select = 'SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryActiveT2 = 1)' END if(@Status = 'ALI') --All Inactive BEGIN SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryInactiveT1 = 1)'
SELECT @SQLTier2Select = 'SELECT TOP 1000 * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryInactiveT2 = 1)' ENDEND ELSE IF @Status = 'PAID' BEGIN SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))' SELECT @SQLTier2Select = 'SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000Tier2.dbo.payhistory ph1 LEFT JOIN Collect2000Tier2.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT2 = 1))'END ELSE BEGINSELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria SELECT @SQLTier2Select = 'SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria END SELECT @SQL = CASE @Tier WHEN 0 THEN @SQLTier1Select + ' UNION ' + @SQLTier2Select + 'ORDER BY NAME ASC' WHEN 1 THEN @SQLTier1Select + 'ORDER BY NAME ASC' WHEN 2 THEN @SQLTier2Select + 'ORDER BY NAME ASC 'END PRINT @SQL --SELECT @SQL EXEC (@SQL)
When I'm trying to execute my stored procedure I'm getting the following code Line 35: Incorrect syntax near '@SQL'. Here is my procedure. Could someone tell me what mistake I'm doing.Alter procedure [dbo].[USP_SearchUsersCustomers_New] @UserID INT ,@RepName VARCHAR(50) ,@dlStatus VARCHAR(5) = '' as Declare @Criteria VARCHAR(500) ,@SQL VARCHAR(8000)
SELECT @Criteria = ''SET NOCOUNT ON if (@dlStatus <>'ALL' AND (LEN(@dlStatus)>1)) BEGIN if(@dlStatus='ALA') SET @Criteria='AND dbo.tbl_Security_Users.IsActive=1' else SET @Criteria='AND dbo.tbl_Security_Users.IsActive=0' END --If the user is an Admin, select from all users. if(dbo.UDF_GetUsersRole(@UserID) = 1) BEGIN@SQL = 'SELECT U.UserID --,U.RoleID ,ISNULL((Select TOP 1 R.RoleName From dbo.tbl_Security_UserRoles UR INNER JOIN dbo.tbl_Security_Roles R ON R.RoleID = UR.RoleIDWhere UR.UserID = U.UserID), 'Unassigned') as 'RoleName' ,U.UserName ,U.Name ,U.Email ,U.IsActive ,U.Phone FROM dbo.tbl_Security_Users U --INNER JOIN dbo.tbl_Security_Roles R ON U.RoleID = R.RoleID WHERE U.NAME LIKE @RepName AND U.UserTypeID < 3'+ @Criteria
Hi,I want to use a variable to put a value in a table but it doesn't seems to works. How can i do that? I have bolded and underlined the text that i think is not correct.What syntax can i use to make it work?Thanks----------------------------------------------------------------------dbo._UpdateImage(@ID int,@ImageID int) ASBegin Declare @PhotosThumb nvarchar(50)Declare @Photos nvarchar(50) SET @PhotosThumb = 'PhotosThumb' + convert(nvarchar, @ImageID)SET @Photos = 'Photos' + convert(nvarchar, @ImageID) SET NOCOUNT ON IF @ImageID = 1 UPDATE PhotosSET @PhotosThumb = 'Logo_thumb.gif',@Photos = 'Logo320x240.gif'WHERE ID = @ID ELSE UPDATE PhotosSET @PhotosThumb = NULL,@Photos = NULLWHERE ID = @ID SET NOCOUNT OFFEND
Yo people, got a little problem with this stored procedure, i go to save it and it kicks out these errors: Incorrect syntax near the keyword 'Drop'.Incorrect syntax near 'Go'.Incorrect syntax near 'Go'.'CREATE/ALTER PROCEDURE' must be the first statement in the query batch I dont no about this sort of stuff so a good break down of what wrong would be good, below is the whole procedure. CREATE PROCEDURE dbo.SQLDataSource1 Drop Table PersonGo Create Table Person (PersonID Int Identity, PersonEmail Varchar(255),PersonName Varchar(255), PersonSex Char(1),PersonDOB DateTime, PersonImage Image,PersonImageType Varchar(255) )
Drop Proc sp_person_isp Go Create Proc sp_person_isp @PersonEmail Varchar(255),@PersonName Varchar(255), @PersonSex Char(1),@PersonDOB DateTime, @PersonImage Image, @PersonImageType Varchar(255) As BeginInsert into Person (PersonEmail, PersonName, PersonSex, PersonDOB, PersonImage, PersonImageType) Values (@PersonEmail, @PersonName, @PersonSex, @PersonDOB, @PersonImage, @PersonImageType) End
Dim CN = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring")) Dim CM As New SqlCommand("spCCF_CrossTab", CN) CM.CommandType = CommandType.StoredProcedure CM.Parameters.Add(New SqlParameter("@LocationID", "CCFIF")) CM.Parameters.Add(New SqlParameter("@BeginDate", dtbStart.Text)) CM.Parameters.Add(New SqlParameter("@EndDate", dtbEnd.Text)) CN.Open() DR = CM.ExecuteReader(CommandBehavior.CloseConnection) dgReport.DataSource = DR dgReport.DataBind()
A SQL exception is thrown: Incorrect syntax near the keyword 'END'
But I turned on tracing in Enterprise Manager, the following request is sent to SQL: exec spCCF_CrossTab @LocationID = N'CCFIF', @BeginDate = N'11/3/2003', @EndDate = N'11/4/2003' In query analyzer the above line executes without error and returns the expected information.
My stored procedure is:
CREATE PROCEDURE spCCF_CrossTab @LocationID varchar(10), @BeginDate varchar(10), @EndDate varchar(10) AS
select @select='SELECT dbo.ActionCodes.Name AS Action FROM dbo.Productivity_CCF LEFT OUTER JOIN dbo.ActionCodes ON dbo.Productivity_CCF.ActionID = dbo.ActionCodes.ID LEFT OUTER JOIN dbo.UserInfo ON dbo.Productivity_CCF.UserID = dbo.UserInfo.ID WHERE (dbo.Productivity_CCF.[Date] BETWEEN CONVERT(DATETIME, ''' + @BeginDate + ''', 101) AND CONVERT(DATETIME, ''' + @EndDate + ''', 101)) GROUP BY dbo.UserInfo.UserName, dbo.ActionCodes.Name order by Action' select @sumfunc= 'COUNT(ActionID)' select @pivot='UserName' select @table= 'UserInfo' select @where='(dbo.UserInfo.LocationID = ''' + @LocationID + ''' and dbo.UserInfo.Inactive<>1 )'
DECLARE @sql varchar(8000), @delim varchar(1) SET NOCOUNT ON SET ANSI_WARNINGS OFF
EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2') EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' + @pivot + ' Is Not Null and ' + @where)
SELECT @delim=(CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) WHEN 0 THEN '' ELSE '''' END) FROM tempdb.information_schema.columns WHERE table_name='##pivot' AND column_name='pivot'
I am trying to get a returned value from the stored procedure below CREATE PROC insert_and_return_id ( @parameter1 varchar, @parameter2 varchar
) AS DECLARE @newID int SELECT @newID = 0 INSERT INTO tbltest (field1, field2) VALUES (@parameter1, @parameter2) IF(@@ROWCOUNT > 0) BEGIN SELECT @newID = @@IDENTITY END RETURN @newID GO ___________________________ My asp Code looks like this ___________________________ Function InserTest(value1, value2) Dim objConn, objRs, objCmd ' Create a connection to the database Set objConn = Server.CreateObject("ADODB.Connection") objConn.open "DSN=" & CONNECTION_STRING ' Create the query command Set objCmd = Server.CreateObject("ADODB.Command") Set objCmd.ActiveConnection = objConn objCmd.CommandText = "insert_and_return_id" objCmd.CommandType = adCmdStoredProc ' Create the parameter for output and returned valueand populate it objCmd.Parameters.Append objCmd.CreateParameter("parameter1", adVarChar, adParamInput, 255, value1) objCmd.Parameters.Append objCmd.CreateParameter("parameter2", adVarChar, adParamInput, 255, value2) objCmd.Parameters.Append objCmd.CreateParameter("newID", adInteger, adParamReturnValue, 4)
objCmd.Execute objCmd0 response.write objCmd.Parameters("newID") 'objCmd.Close End Function
And I get the following ASP Error Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function insert_and_return_id has too many arguments specified. /netwasp/tester.asp, line 62
I only just started to use sp's hence it might be something really simple, Can anyone help, cheers?
I am trying to swap two rows in a table .. I am stuck with this error since a long time.. can anyone guess where the problem is ? create procedure was working fine in query analyzer but when used it in the stored procedure. I am getting these .. can anyone help me out please ... Your help will be greatly appreciated.. UpdateRowsReorderUp is my storedprocedure ... and i am using MS Sql 2000 .. am I doing something really wrong which i'm not supposed to ????? Thanks friends.. Procedure 'UpdateRowsReorderUp' expects parameter '@nextlowestsortID', which was not supplied. CREATE PROCEDURE [dbo].[UpdateRowsReorderUp] ( @intsortID int, @nextlowestsortID int, @MemberID int ) AS Select @nextlowestsortID=(Select Top 1 SortID from SelectedCredits where SortID<@intsortID order by SortID DESC) UPDATE SelectedCredits SET SortID= CASE WHEN SortID = @nextlowestsortID then @intsortID WHEN SortID = @intsortID then @nextlowestsortID ELSE SortID End WHERE MemberID = @MemberID SELECT * FROM SelectedCredits WHERE MemberID= @MemberID ORDER BY SortID GO ************** // this is my script on the page void moveup(Object s, DataListCommandEventArgs e) { objcmd= new SqlCommand("UpdateRowsReorderUp",objConn); objcmd.CommandType = CommandType.StoredProcedure; objcmd.Parameters.Add("@intsortID",intsortID); objcmd.Parameters.Add("@MemberID",Session["MemberID"]); objRdr= objcmd.ExecuteReader(); dlSelCredits.DataSource = objRdr; dlSelCredits.DataBind(); objRdr.Close(); objConn.Close(); BindData(); }