Sql server Identity issue
-
I have 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 value of identity how can i do that the main issue over here is "IMDECONP38"- server name that i used if i remove this i can able to get the value of identity in my parameter
-
I have 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 value of identity how can i do that the main issue over here is "IMDECONP38"- server name that i used if i remove this i can able to get the value of identity in my parameter
-
Are you running this script connected to server "IMDECONP38" or is that a linked server from which you are running the query?
yes its from link server
-
yes its from link server
scope_identity and @@Identity only works on the server its executed on, so it won't return a value from a linked server. You would need to create a stored proc on the linked server that returns the scope_identity value. You could then call that from your query. See here for more information: http://msdn.microsoft.com/en-us/library/ms187342.aspx