SQL Server identity issue

Question

I have a query like below

declare @str_CustomerID int
Insert into IMDECONP38.[Customer].dbo.CustomerMaster
( CustomerName , CustomerAddress , CustomerEmail , CustomerPhone )
values ( ‘werw12e’ , ‘jkj12kj’ , ‘3212423sdf’ , ‘1212121′
)

select @str_CustomerID= scope_identity()

After execution it returns null in my parameter.

I want to get the value of identity. How can I do that?

The main issue over here is "IMDECONP38" - the server name that I used. If I remove this I can get the value of identity in my parameter.

There actually is an identity column in the table, right? Silly question, but just in case . . . — MJB, Apr 12 '10 at 12:14

score 9 · Answer 1 · edited May 23 '17 at 12:24

9

See this old question for a similar problem: You cannot retrieve a scoped variable like SCOPE_IDENTITY() from another server. Instead, you should use a stored procedure on the remote server to achieve this.

edited May 23 '17 at 12:24

Community

1
1

answered Apr 12 '10 at 12:33

Prutswonder

9,476
3
26
38

Good call. Best advice so far. – gbn Apr 12 '10 at 18:24

gbn · Accepted Answer · 2010-04-12T18:25:23.280

5

When you use "IMDECONP38" then you break SCOPE_IDENTITY because

the INSERT scope is now on the IMDECONP38 linked server
SCOPE_IDENTITY runs on the local server, not IMDECONP38

If on SQL Server 2005, try the OUTPUT clause but I'm not sure how it works for a linked server call

Insert into IMDECONP38.[Customer].dbo.CustomerMaster
OUTPUT INSERTED.ID   --change as needed
( CustomerName , CustomerAddress , CustomerEmail , CustomerPhone )
values ( ‘werw12e’ , ‘jkj12kj’ , ‘3212423sdf’ , ‘1212121′
)

Edit: Prutswonder said it first: use a stored proc on the linked server

edited Apr 12 '10 at 18:25

answered Apr 12 '10 at 12:19

gbn

408,740
77
567
659

1

Sql server giving error : Remote tables are not allowed to be the target of a DML statement with an OUTPUT clause, or the target of the INTO clause. – Pranay Rana Apr 12 '10 at 12:24

score 3 · Answer 3 · answered Apr 14 '10 at 10:58

Use a stored procedure in the remote database.

CREATE PROCEDURE InsertCustomer (@name varchar(100), @address varchar(100), 
    @email varchar(100), @phone varchar(100), @id int OUT)
AS
    INSERT INTO dbo.CustomerMaster 
    (CustomerName , CustomerAddress , CustomerEmail , CustomerPhone ) 
    VALUES (@name, @address, @email, @phone)

    SET @id = SCOPE_IDENTITY()
GO

DECLARE @id int
EXEC IMDECONP38.Customer.dbo.InsertCustomer 'Fred','Bedrock','a@b','5',@id OUT
GO

score 0 · Answer 4 · answered Jul 26 '16 at 13:45

For sql server 2012, to get the name of the identity column

select col_name(sys.all_objects.object_id, column_id) as id from    sys.identity_columns 
join sys.all_objects on sys.identity_columns.object_id = sys.all_objects.object_id
where sys.all_objects.object_id = object_id('system_files')

If you have a Linked server you can use this:

select iden.name as id 
from [LINKED_SERVER_NAME].[DATABASE_NAME].sys.identity_columns as iden
join [LINKED_SERVER_NAME].[DATABASE_NAME].sys.all_objects allobj
    on iden.object_id = allobj.object_id
where allobj.name = 'TABLE NAME HERE'

score -2 · Answer 5 · edited Apr 13 '10 at 18:11

-2

select @@IDENTITY AS 'variablename'

edited Apr 13 '10 at 18:11

gbn

408,740
77
567
659

answered Apr 12 '10 at 12:13

David Fox

10,382
8
47
76

5

using `@@IDENTITY` is dangerous because it returns the last ID generated from any scope. – James Apr 12 '10 at 12:15
1

I've never wanted to upvote a comment multiple times until now. – John Buchanan Sep 29 '10 at 21:37

score -2 · Answer 6 · edited May 12 '11 at 05:51

-2

Please check if there are any triggers set for your table. This will cause a problem when you use SCOPE_IDENTITY() to get the last inserted identity field.

HTH

edited May 12 '11 at 05:51

Pranay Rana

170,430
35
234
261

answered Apr 12 '10 at 12:21

Raja

3,568
3
26
39

SET NOCOUNT ON does not affect SCOPE_IDENTITY. Or do you have an MSDN reference to back this up. As my question notes, only DataAdaptors can be affected by SET NOCOUNT ON here http://stackoverflow.com/questions/1483732/set-nocount-on-usage – gbn Apr 12 '10 at 12:22
@gbn: I had this same problem and SET NoCount OFF did the trick for me. Anyways I have taken that out of my answer :-). – Raja Apr 12 '10 at 12:39
1

Triggers affect the value of @@Identity not scope_identity() – HLGEM Apr 12 '10 at 13:51

SQL Server identity issue

6 Answers6