sql server programming: Identity column accross remote servers -- sql server programming

Identity column accross remote servers

Pierson C
6/27/2005 11:13:01 PM

sql server programming:

All,
I am doing a fairly simple ETL accross from a SQL Server to a Linked Server.
I am doing the processing with a stored procedure and t-sql. The tables I
am loading contain a foreign key which is an identity field of another table.
I am using SCOPE_IDENTITY to attain the value inserted as the primary key in
one table so that I may assign it as the foreign key in the other.

After the insert, I assign the Identity to a variable to insert into the
next table as so:
SET @key = SCOPE_IDENTITY()
This value is always null.

My issue is, I believe because I am running the stored procedure on my local
machine and inserting into the linked server. I believe that the
SCOPE_IDENTY() is not picking up the most recent identity inserted on the
linked server.

Can anyone confirm my suspicion? Suggest a work around?

Prompt responses would be appreciated as I am working towards a tight
deadline(as we all do!).

RE: Identity column accross remote servers

John Bell
6/27/2005 11:44:03 PM

Hi

You assumption is not unreasonable especially if your linked server is not a
SQL server!!!

For @@IDENTITY this restriction is documented on
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp

"The scope of the @@IDENTITY function is 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."

John

[quoted text, click to view]

Re: Identity column accross remote servers

David Portas
6/28/2005 12:00:00 AM

SCOPE_IDENTITY() applies to the local server. You could run the proc on the
other server.

Are you really using a cursor to do this? IMO it's far better to do a
set-based INSERT and forget SCOPE_IDENTITY. An example was discussed in this
thread:

http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/269283ff377591fa/712ce05369e2f53b

--
David Portas
SQL Server MVP
--

Re: Identity column accross remote servers

Pierson C
6/28/2005 7:56:09 AM

Thanks all for the quick responses! I have my issue resolved by using a
set-base insert.

Pierson

[quoted text, click to view]

Re: Identity column accross remote servers

Anith Sen
6/28/2005 9:08:43 AM

See if this helps:
http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/msg/323472cbcb1ce0a8

--
Anith

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.