Need help reading identity across linked server

Question

Need help reading identity across linked server

Mark Anthony Lloyd

Grasshopper

Points: 18
More actions
March 23, 2005 at 3:17 pm

#163990

Hello,
i have two sql server (2k) instances; we'll call them SQL1 and SQL2. SQL1 has a link (added using sp_addlinkedserver) to SQL2.
in SQL2 i have a table with an identity column we'll call this table 'tableX'.
if i perform an insert into SQL2.remotedb.dbo.TableX while connected to SQL1 the insert works fine.
the problem is that @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT('tablex') all return DBNull if executed from SQL1. Note that IDENT_CURRENT('tablex') returns the correct value if executed from SQL2.
The question is how do i get the identity from SQL1 for an insert performed against the linked table in SQL2. i want to read the identity value immediately after the insert from the same connection which was used to perform the insert.
any help would be greatly appreciated.
Thank You!
Mark

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

G.R.Prithiviraj Kulasingham SSCarpal Tunnel Points: 4104 More actions · Answer 1

This is one of the areas where you may need to query system tables. The following query returns all tables and their identity values. (I assume that the identiy columns are Int. But you can extend the functionality for other columns too.

SELECT o.name Table_Name, c.name Column_Name,

ASCII(LEft(c.printfmt,1))+ (ASCII(Substring(c.printfmt,2, 1)) *256)+(ASCII(Substring(c.printfmt,3, 1))*256*256)+( ASCII(Substring(c.printfmt,4, 1)) *256*256*256)

FROM sql2.remotedb.dbo.syscolumns c

INNER JOIN sql2.remotedb.dbo.sysobjects o

ON c.id = o.id

WHERE o.xtype = 'U' -- User table

AND c.colstat & 1 = 1

However, I prefer another method.

Create a stored procedure in SQl2.RemoteDb "TableX_Insert". The procdeure can insert and return the identity value.

call the procedure from sql1 using exec sql2.remotedb.dbo.TableX_Insert. The returning resultset will tell you the identity value.

Hope thsi will address your problem

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/

G.R.Prithiviraj Kulasingham SSCarpal Tunnel Points: 4104 More actions · Answer 2

Weird! Ident_Current('TableX') should be null if you are running the query from SQL1. (I assume you don’t have the same table in SQL1)

However, If it is working..Dont break it.!

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/

Mark Anthony Lloyd Grasshopper Points: 18 More actions · Answer 3

Prithiviraj,

thanks for taking the time to reply to my inquiry...

i tried the sql your present. however, it returns incorrect results. i have not had time to determine why but can send you the details of the test i used to make this assertion.

it seems to me that the real question here is how to get ident_current to execute on sql2 rather than on sql1. for now, i have implemented a proc on SQL2 which simply executes the select ident_current() and returns the result.

i would prefer however to find a solution which does not require the use of a specialized proc or udf...

any further input would be appreciated...

thanks,

mark

Peter Wright SSCommitted Points: 1966 More actions · Answer 4

Mark

I think you and Prithiviraj have taken this as far as you can. You have to execute the identity check on the remote SQL Server (SQL2) and pass the result to the other Server (SQL1).

You can either do this by writing a stored procedure to execute ident_current on SQL2 (as you have done) but you either have to hard code the table name or build a statement to be executed by sp_executesql.

Alternately you could use Pritviraj's SQL to create a procedure on SQL2, adding a paramater to specify the tablename:

CREATE PROC IdentityTest @table VARCHAR(20)

AS