September 13, 2011 at 10:54 am
I use ident_current to get ID which just inserted.
1) The code below is working in current database:
select ident_current('history')
2) The code below is not working in another server:
select ident_current('ServerA.live.dbo.history')
How to make code in 2) working?
September 13, 2011 at 10:55 am
Use the output clause of insert.
September 13, 2011 at 11:27 am
From BOL:
The scope of the @@IDENTITY function is current session on the local server on which it is executed. This function cannot be applied to remote or linked servers. To obtain an identity value on a different server, execute a stored procedure on that remote or linked server and have that stored procedure (which is executing in the context of the remote or linked server) gather the identity value and return it to the calling connection on the local server.
I'm pretty sure the scope of IDENT_CURRENT is also to the local server and you have the option above to get this value on a different server (create a stored procedure on the remote server that returns the value and execute this stored procedure).
Another approach is to query the sys.identity_columns table on the remote server and obtain the information last_value column. Caution: I don't know if this system table is as reliable as the IDENT_CURRENT function.
September 13, 2011 at 11:30 am
I've always use scope_identity because it was safer (sql 2000).
Since 2K5, the output clause exists which is a better tool IMHO.
September 13, 2011 at 12:01 pm
Agree on output clause being the best option 2K5 onwards - but it looks like the OP is looking to get this value from a remote server in which case:
- either the results of the output clause have to be persisted in the remote server or
- one of these functions (IDENT_CURRENT or SCOPE_IDENTITY) have to be wrapped in a remote procedure and invoked or
- hacks options like sys.identity_columns can be considered if appropriate (I've used this before when a ballpark estimate of the identity column value was needed and it worked fine)
September 13, 2011 at 2:56 pm
output into @whatever
select * from whatever
Then you get the results back. Can't say if there's something better without seeing the whole code.
Other option would be to import the data from the other side.
August 7, 2013 at 7:48 am
Hi,
I too have similar requirement. I need to get Ident_current for all the tables from another server.
DO you have any SP for this?? please reply to me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply