scope_identity() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

scope_identity()

Author

Topic

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2009-12-28 : 11:54:05

declare @job_id=?
SELECT @job_id = SCOPE_IDENTITY()

When I run this it suddenly started giving me an error:

[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error

any insight is welcome.
Cheers
Paresh

Regards
Paresh Motiwala
Boston, USA

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-28 : 12:04:49

I believe this is not the entire code that you are showing here. Can you post the entire SQL tht you are trying to execute.

How are you executing this? Try running, whatever query you are running, in Management Studio to see if it gives you a correct result.

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2009-12-28 : 12:41:06

EXEC [dbo].[name of sp]
@comp_id = N'abc',
@user_id = N'dataops',
@daystocomplete = 0,
@rawdata_loc = myspreadsheet.xls',
@datainfo_loc = N'abc_automatic',
@etluser = N'dataops',
@etlstatus = N'PROCESSING',
@priority = 4,
@jobtype = N'DU',
@comment = N'abc automatic',
@job_id = ? Output
SELECT @job_id = SCOPE_IDENTITY()

the error I got is from SSMS.
it also happens in the ssis package.

Regards
Paresh Motiwala
Boston, USA

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-12-28 : 13:16:15

In a SSMS query window this question mark would through a syntax error:
@job_id = ? output.

Also, since @job_id seems to be an OUTPUT parameter then why are you attempting to reassign the value with scope_identity()?

I suspect that if you comment out those two lines of code you will still get the error meaning it is probably caused by code inside the SP you are calling.

Be One with the Optimizer
TG

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2009-12-28 : 13:19:01

CREATE PROCEDURE [dbo].[name of sp]
-- Add the parameters for the stored procedure here
@comp_id nvarchar(10),
@user_id nvarchar(50),
@daystocomplete int,
@rawdata_loc nvarchar(255),
@datainfo_loc nvarchar(255),
@etluser nvarchar(50),
@etlstatus nvarchar(50),
@priority int,
@jobtype nvarchar(50),
@comment nvarchar(500),
@job_id int OUTPUT

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @duedate smalldatetime
SET @duedate = CONVERT(smalldatetime,CONVERT(varchar(10),dateadd(d,@daystocomplete,getdate()),110))

-- Insert statements for procedure here
INSERT dbo.tbletlmain(comp_id,user_id,date_created,date_due,rawdata_loc,datainfo_loc,etluser,etl_status,priority_code,job_type,comment)
VALUES (@comp_id,@user_id,getdate(),@duedate,@rawdata_loc,@datainfo_loc,@etluser,@etlstatus,@priority,@jobtype,@comment)

SELECT @job_id = SCOPE_IDENTITY()

END

hope this helps.

Regards
Paresh Motiwala
Boston, USA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-28 : 13:32:43

Sounds like you've got a trigger on tbletlmain that is causing the error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-12-28 : 13:36:17

Looks like you VALUES has eleven items but your insert ColumnList only has 10. That is the problem.

Since your SP is getting the @job_id value via scope_identity then your calling code should only be:
SELECT @job_id

NOT:
SELECT @job_id = scope_identity()

EDIT:
Woops - Looks like I missed a comma. your column counts look correct :)

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-12-28 : 13:44:18

Looks like your calling code was missing a beginning quote on @raw_dataloc. Try this:


declare @job_id int

EXEC [dbo].[name of sp]
@comp_id = N'abc',
@user_id = N'dataops',
@daystocomplete = 0,
@rawdata_loc = 'myspreadsheet.xls',
@datainfo_loc = N'abc_automatic',
@etluser = N'dataops',
@etlstatus = N'PROCESSING',
@priority = 4,
@jobtype = N'DU',
@comment = N'abc automatic',
@job_id = @job_id Output

SELECT @job_id

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources