I have a linked server, I want to add a record to the table on the linked server, Is it possible take @@identity from another server with linked server? (SQL Server 2005)
Remarks. After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL.
Once we insert a row in a table, the @@IDENTITY function column gives the IDENTITY value generated by the statement. If we run any query that did not generate IDENTITY values, we get NULL value in the output. The SQL @@IDENTITY runs under the scope of the current session.
Open SQL Server Management Studio; go to Server Objects -> Linked Server. Under Linked Server node we have Providers node that already having installed provides and that mapped to SQL Server.
SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use. IDENT_CURRENT('tableName') returns the last identity value generated for a specific table in any session and any scope.
You can create a stored procedure on your linked server that will return the identity.
You should be using SCOPE_IDENTITY()
rather than @@IDENTITY
, by the way.
See this related question (Best way to get identity of inserted row?).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With