SQL Server identity issue

Question

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.

Answer 1

select @@IDENTITY AS 'variablename'

Answer 2

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

Answer 3

Please check if there are any triggers set for your table. This will cause a problem when you use SCOPE_IDENTITY() to get the last inserted identity field.

HTH

Answer 4

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.

Answer 5

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()
GO

DECLARE @id int
EXEC IMDECONP38.Customer.dbo.InsertCustomer 'Fred','Bedrock','a@b','5',@id OUT
GO