question

aRookieBIdev avatar image
aRookieBIdev asked

Return Identity insert from a remote stored proc execution

Hi , I have a storedprocedure usp_InsertNewEmployee in a remote server. I have set up linked server and a synonym for the stored procedure. I could execute the stored procedure as Exec usp_InsertNewEmployee @EmployeeName = 'John' But I need to execute the stored procedure as well as return the EmployeeId inserted to the new table which in an identity in that table . How can I use SCOPE_IDENTITY() to return this through open query. Kindly provide an example. Thanks NK
sql server 2012linked-serverremoteidentity-insert
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image sp_lock commented ·
Have you tried storing the SCOPE_IDENTITY in a local variable and returning that?
0 Likes 0 ·
aRookieBIdev avatar image aRookieBIdev commented ·
I am not allowed to make changes to the sp usp_InsertNewEmployee that exists in the remote server.
0 Likes 0 ·
sp_lock avatar image sp_lock commented ·
What data/columns are returned from usp_InsertNewEmployee
0 Likes 0 ·
aRookieBIdev avatar image aRookieBIdev commented ·
It does not return any value... The SP does an insert to the employee table which has an identity field employeeid . I want to get the employeeid inserted by the sp . The sp is executed in the remote linked server .
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.