Pranay Rana Pranay Rana - 4 months ago 6
SQL Question

SQL Server identity issue

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.

gbn gbn
Answer

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

Comments