I've got 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 during my parameter.
I wish to get the need for identity. How do i do this?
The primary problem right here is "IMDECONP38" - the server title which i used. Basically remove i could possibly get the need for identity during my parameter.
See this old question for the same problem: You can't retrieve a scoped variable like
SCOPE_IDENTITY() from another server. Rather, you need to use a saved procedure around the remote server to do this.
If you use "IMDECONP38" then you definitely break SCOPE_IDENTITY because
- the Place scope has become around the IMDECONP38 linked server
- SCOPE_IDENTITY works on the local server, not IMDECONP38
If on SQL Server 2005, try the OUTPUT clause but I am unsure how it operates for any 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 stated it first: make use of a saved proc around the linked server
Make use of a saved procedure within 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
select @@IDENTITY AS 'variablename'
Book if you will find any triggers looking for your table. This makes an issue if you use
SCOPE_IDENTITY() to find the last placed identity area.