hello everyone,
I am using VS 2005 and sql server 2000. I have a web application which uses a connectionstring with a user id and pwd. Everything is fine, I can execute normal sql statements from the code behind. I cannot execute a stored procedure...I get this error.
Could not locate entry in sysdatabases for database 'exec usp_add2DB (my parameters are included here, deleted for simplicity reasons)
No entry found with that
name. Make sure that the name is entered correctly.
I have given execute permission for the user, and the name of the storedproc is correct. the name of the server is also correct....pls pls help me what is wrong here...
My C# asp.net web application is throwing an exception and the message is the following:
RAISERROR could not locate entry for error 60002 in sysmessages. The error occurs in the stored procedure. Can anyone tell me what I need to do to fix this problem? Note that if I manually run the stored procedure, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74 RAISERROR could not locate entry for error 60002 in sysmessages.
Hey there, could someone please help me!! I had created a database in sql server but since then installed windows xp pro and now when i go through the microsoft sql server dsn configuration the database is not valid and therefore i cannot change the default database as it says it doesn't exist. So then when i type in command prompt use 'db' it says it doesn't exist but when i try to create 'db' it says that the database already exists. I get the error message, could not locate entry in sysdatabases for 'cti' (database name). When i look in mysql - data - along with master etc there is my cti.mdf and log files?? I'm stuck??? Is it that the files just aren't registering through sql server??? Please help!
I am getting the exception - System.Data.SqlClient.SqlException: Could not locate entry in sysdatabases for database. Does anyone has any idea, how to resolve this? Thanks
is there a way when making a stored procedure if i can enter the information in multiple tables?? say the primary key into another table to link the relationship? or should i just pull it out and then put in?
Hello Friends, I have created few reports in SSRS 2005 on Windows 2003 Standard. Previouly I was not able to view reports from my .ASPX page which contained a ReportViewer, after going throuhg some other threads I was able to create a snap-shot for my reports . But still I'm not able to view reports, each time I try to view the page I get a message box with a error as : "Unable to locate control SSRS 2005: oReportCell" the same error is displayed when i try to access my reports from "http://localhost/reports.....". But the funny thing is I'm able to view the reports when I use the following URL: "http://localhost/reportserver/pages/reportviewer.aspx?/<report>". I have applied all the Hotfixes & installed the latest Service packs. Any suggestions ???????
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]
"High priority system task thread: Operating system error Exception 0xAE encountered."
we are seeing this on one of our development servers, running SQL 2005 Enterprise SP1. The symptom is that after the sql service has been running a while (order of days), this error starts getting written to the sql server log file over and over, and eventually the logfile fills the drive.
Apparently it's fixed by this hotfix, but our ops team doesn't want to install this - they'd rather just cycle the service and delete the log when it happens.
I was wondering if anyone else has seen it might know the root cause so we might be able to workaround it without cycling the service. I know, lame question. The correct answer is "install the hotfix" or "install sp2" but our ops team won't do that for now...
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.
When I try to run my application using VWD from the file system I can connect and run the application and it connects to the remote SQL 2000 server with no problem.However, when I publish to my web server I get the folloiwng error message: Anyone got any ideas?? Configuration Error Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately. Parser Error Message: The entry 'MCLConnectionString' has already been added.Source Error: Line 11: <appSettings/> Line 12: <connectionStrings> Line 13: <add name="MCLConnectionString" connectionString="Data Source=GEACO1INTR1;Initial Catalog=MCL;Persist Security Info=True;User ID=ASPNET;Password=PAUL0001" providerName="System.Data.SqlClient"/> Line 14: </connectionStrings> Line 15: <system.web>Source File: c:inetpubwwwrootmcltestweb.config Line: 13
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(); }
I have a stored procedure to which I pass the following parameters @Date smalldatetime, @Amount decimal(15,3) @Exg_Rate decimal(5,3)Inside this stored procedure I call another one passing to it those parameters like thatEXECUTE dbo.[Opening_Balance_AfterInsert] @Date, @Amount*@Exg_RateI receive an error at the above line saying: Incorrect syntax near '*'if I try to put the expression between rounded brackets I receive the error Incorrect syntax near '('How can I pass that expression?
String[1]: the Size property has an invalid size of 0.I am using a Stored Procedure, shown hereCREATE PROCEDURE GetImagePath( @ID INT, @ImagePath VARCHAR(50) OUTPUT) AS SET NOCOUNT ONSELECT @ImagePath = path FROM dbo.docs WHERE table1= @IDRETURNGOHere is the asp.net code that I am usingSqlConnection conn = new SqlConnection(strConnection);conn.Open();SqlParameter param = new SqlParameter("@ImagePath", SqlDbType.VarChar);param.Direction = ParameterDirection.Output;SqlCommand cmd = new SqlCommand("GetImagePath", conn);cmd.Parameters.AddWithValue("@ID", 100);cmd.Parameters.Add(param);cmd.CommandType = CommandType.StoredProcedure;cmd.ExecuteNonQuery();cmd.Dispose();conn.Dispose();TextBox1.Text = param.Value.ToString();When I run it, I get the following error String[1]: the Size property has an invalid size of 0. The stored procedure is correct because I tested it wtih Query Analyzer. I cant seem to figure out what is causing this error.Any help would be appreciated.
I am using SQL Server 2005 and wrote a stored procedure as shown below.CREATE PROCEDURE [dbo].[GetUsers]( @StartRowIndex INT, @MaximumRows INT, @SortExpression VARCHAR(50))ASBEGIN DECLARE @SQL VARCHAR(1000); DECLARE @Start VARCHAR(10); SET @Start = CONVERT(VARCHAR(10), @StartRowIndex + 1); DECLARE @End VARCHAR(10); SET @End = CONVERT(VARCHAR(10), @StartRowIndex + @MaximumRows); SET @SQL = ' WITH Data AS( SELECT UserID, Username, FirstName, LastName, ROW_NUMBER() OVER(ORDER BY ' + @SortExpression + ') AS RowNumber FROM Users) SELECT UserID, Username, FirstName, LastName FROM Data WHERE RowNumber BETWEEN ' + @Start + ' AND ' + @End EXEC(@SQL);ENDIn VS 2005, I am using a DataSet. Then I created an ObjectDataSource which binds the GridView control. However, I am getting the following error: Incorrect syntax near ')'After playing around with it, the error is from the line:ROW_NUMBER() OVER(ORDER BY ' + @SortExpression + ') AS RowNumber FROM Users)If I change the line to ROW_NUMBER() OVER(ORDER BY UserID') AS RowNumber FROM Users) it works fine, except for sorting.I dont understand what I might be doing wrong.
I am writing a stored procedure where I am adding a column to a table, doing some manipulation using the new column and then deleting the column. The problem is when I try to save the stored procedure, it gives me an error because it cannot find the new field on the table. How can I tell SQL to not compile a section or whole stored procedure?
does anyone see anything wrong with this stored procedure?? I keep getting a syntax error.
CREATE PROCEDURE [InsertGCTerms] AS INSERT INTO [CommissionEmployee_Exclusionsdb].[dbo].[GCEmployeeTerms]([TM #],[FirstName],[LastName],[SocialSecurityNumber],[DateHired],[DepartmentName],[Title]) SELECT a.TM#, a.LASTNAME, a.FIRSTNAME, a.SSN#, a.JOBTITLE, a.HIREDATE, a.DEPT# FROM GOVEMPLYS AS a WHERE a.STATUS = 'TERMINATED' RETURN GO
DECLARE my_cursor CURSOR FOR SELECT spid,blocked from sys.sysprocesses where program_name ='PurveyanceImport' and status = 'suspended' and cmd = 'UPDATE'
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @tSPId,@tblocked
WHILE @@FETCH_STATUS = 0 BEGIN
Kill @tSPId
FETCH NEXT FROM my_cursor INTO @tSPId,@tblocked END
SET @ExecuteSQL = @ExecuteSQL + 'Select MRNumber From #TempContractorTable '
IF (@Status = 'Load') BEGIN SET @ExecuteSQL = @ExecuteSQL + ' Where datediff(day, CallDate, getdate()) > 5 ORDER BY '+ @SortCol + ' ' + @SortOrder + '' END IF (@Status = 'Search') BEGIN IF LEN(@Fdate)>0 AND LEN(@Tdate)>0 BEGIN SET @ExecuteSQL=@ExecuteSQL + ' Where CallDate >= ''' + @FDate + ''' AND CallDate <= ''' + @TDate + ''' ' END ELSE IF LEN(LTRIM(RTRIM(@Fdate)))>0 BEGIN SET @ExecuteSQL=@ExecuteSQL + 'Where CallDate >= ''' + @FDate + ''' ' END ELSE IF LEN(LTRIM(RTRIM(@Tdate)))>0 BEGIN SET @ExecuteSQL=@ExecuteSQL + 'Where CallDate <= ''' + @TDate + ''' ' END SET @ExecuteSQL=@ExecuteSQL + 'ORDER BY '+ @SortCol + ' ' + @SortOrder + ' '
error in stored procedure an error or column name is missing for SELECT INTO statement,verify each column has name,for other statements look for empty alias name alies defiened as "" or [] add a name or single space as the alias name,an object name or column is missing or empty
stored procedure ALTER PROCEDURE dbo.spM3_ByLocationSearch
AS SET NOCOUNT ON DECLARE @Select NVARCHAR(4000), @Where NVARCHAR(4000), @SqlFinal NVARCHAR(4000), @ErrorMsgID INT, @ErrorMsg VARCHAR(200) SET @Select=''
SET @Where=''
SET @Select = 'WITH SearchLocList AS (select * from ( SELECT dp.depottrack_inventory_serial_number FROM depottrack DP, (SELECT depottrack_inventory_serial_number ,MAX(DEPOTTRACK_CREATEDDATE) AS DEPOTTRACK_CREATEDDATE FROM depottrack
) a left join ( SELECT I.INVENTORY_SERIAL_NUMBER AS CONTAINER#, product_class+ ' | ' + product_description as SIZE | TYPE, inventory_status as STATUS, inventory_vendor_id as VENDOR, ISNULL(INVENTORY_COST_PURCHASE,0) AS PURCHASE PRICE, ISNULL(INVENTORY_COST_UPFIT,0) + ISNULL(INVENTORY_COST_MATERIAL,0) AS UPFIT, ISNULL(INVENTORY_COST_DRAYAGE,0) AS DRAYAGE, ISNULL(INVENTORY_COST_PURCHASE,0) + ISNULL(INVENTORY_COST_UPFIT,0) + ISNULL(INVENTORY_COST_MATERIAL,0) + ISNULL(INVENTORY_COST_DRAYAGE,0) AS TOTAL from INVENTORY I LEFT JOIN VW_IV_COST_PURCHASE P on I .INVENTORY_GUID = P.INVENTORY_GUID LEFT JOIN VW_IV_COST_UPFIT U on I .INVENTORY_GUID = U.INVENTORY_GUID LEFT JOIN VW_IV_COST_DRAYAGE D on I .INVENTORY_GUID = D.INVENTORY_GUID LEFT JOIN VW_IV_COST_MATERIAL M on I .INVENTORY_GUID = M.INVENTORY_GUID left join product on i.inventory_product_id = product.product_id ) b on a.depottrack_inventory_serial_number = b.CONTAINER# where container# is not null order by depottrack_inventory_serial_number ) Select * from SearchLocList'
IF (@DEPOTTRACK_CREATEDDATE IS NOT NULL) BEGIN IF (@Where <>'' )
IF(@depottrack_locid<>"") BEGIN IF (@Where <>'' ) SET @Where =@Where + ' AND SearchLocList.depottrack_locid=''' + @depottrack_locid + '''' ELSE SET @Where = 'AND SearchLocList.depottrack_locid =''' + @depottrack_locid+ '''' END
IF ((@DEPOTTRACK_CREATEDDATE IS NOT NULL)) BEGIN IF (@Where <>'' )
ELSE SET @Where = 'SearchLocList. CONVERT(VARCHAR(10),DP.depottrack_createddate,101)<=''' + CONVERT(VARCHAR,@DEPOTTRACK_CREATEDDATE,101) + '''' END
IF (@OrderBy IS NULL) BEGIN SET @OrderBy= 'SearchLocList.DP.depottrack_createddate, DP.depottrack_inventory_serial_number' END
IF (@Where <> '') BEGIN SET @SqlFinal= @Select + ' where ' + @Where + ' order by ' + @OrderBy END ELSE BEGIN SET @SqlFinal= @Select + ' order by ' + @ORDERBY END
I have created a stored procedure for transferring data from one table to another table with a simple condition. but i am getting error 'Incorrect syntax near the keyword 'select'. may be it is a syntax error but i m not able to resolved it.as i have not worked in stored procedures before.
below is my code
///
alter procedure trans_data3 @nop int, @nop1 int as set @nop=select noofparts from test_products set @nop=select noofparts from test_products1 insert test_products1(prod_name,rate,qty) select prod_name,rate,qty from test_products --if (select @nop=noofparts from test_products1) > 0
if @nop > 0 and @nop1 > 0 insert test_products1_parts(noofparts,weight,height) select noofparts,weight,height from test_products_parts
go
///
in my code I want if column 'noofparts'of table 'test_products'>0 then second insert command should execute.
