SQL Server identity issue
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.
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.CustomerMasterOUTPUT 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
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()GODECLARE @id intEXEC IMDECONP38.Customer.dbo.InsertCustomer 'Fred','Bedrock','a@b','5',@id OUTGO