Get @@Identity from a remote SQL Server

I am using SQL 2000 server. I have a SP that insert a
record to a remote server table with Identity column as
the ID. After the insert, I try to use @.@.Identity to get
the newly inserted ID back. I got NULL. However, it works
when I run the same SP against a DB on the same server.
How can I get the ID back from a remote server?
Thank you for any inputTom
It works.Thank you a lot.
>--Original Message--
>Try:
>declare @.id int
>exec OtherServer.OtherDB.dbo.sp_executesql
> N'insert MyTable (ColA, ColB) values (@.a, @.b)
> select @.id = @.@.identity'
>, '@.a int, @.b int, @.id int'
>, @.a = 1, @.b = 2, @.id = @.id out
>--
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>..
>"omninews" <cxie@.omnichoice.com> wrote in message
news:015801c377f8$140a7410$a001280a@.phx.gbl...
> I am using SQL 2000 server. I have a SP that insert a
>record to a remote server table with Identity column as
>the ID. After the insert, I try to use @.@.Identity to get
>the newly inserted ID back. I got NULL. However, it works
>when I run the same SP against a DB on the same server.
> How can I get the ID back from a remote server?
> Thank you for any input
>|||Tom and others,
It works but sometimes I get dead lock. Because this
exec statement starts a new process on the remote server.
After this I have other update/delete might agaist the
same remote database objects which are within the same
procedure ,therefore the main thread. Looks like the exec
started process does not cleanup itself after it finishes
it's job. Is there a way to kill itself after it is done?
Thank you!
>--Original Message--
>Tom
> It works.Thank you a lot.
>
>>--Original Message--
>>Try:
>>declare @.id int
>>exec OtherServer.OtherDB.dbo.sp_executesql
>> N'insert MyTable (ColA, ColB) values (@.a, @.b)
>> select @.id = @.@.identity'
>>, '@.a int, @.b int, @.id int'
>>, @.a = 1, @.b = 2, @.id = @.id out
>>--
>> Tom
>>----
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>..
>>"omninews" <cxie@.omnichoice.com> wrote in message
>news:015801c377f8$140a7410$a001280a@.phx.gbl...
>> I am using SQL 2000 server. I have a SP that insert a
>>record to a remote server table with Identity column as
>>the ID. After the insert, I try to use @.@.Identity to get
>>the newly inserted ID back. I got NULL. However, it
works
>>when I run the same SP against a DB on the same server.
>> How can I get the ID back from a remote server?
>> Thank you for any input
>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0090_01C3866C.3C5B1D20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Deadlocks are a bigger issue. You'll have to ensure that you keep your =transactions "narrow", i.e. do only the essential work inside the =transaction. Also, make sure you access the resources in the exact same =order in all transactions. You may want to consider doing the insert =via a stored proc on the remote server, instead of using sp_executesql.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"omninews" <cxie@.omnichoice.com> wrote in message =news:014f01c3842b$d18f3770$7d02280a@.phx.gbl...
Tom and others,
It works but sometimes I get dead lock. Because this exec statement starts a new process on the remote server. After this I have other update/delete might agaist the same remote database objects which are within the same procedure ,therefore the main thread. Looks like the exec started process does not cleanup itself after it finishes it's job. Is there a way to kill itself after it is done?
Thank you!
>--Original Message--
>Tom
> It works.Thank you a lot.
>
>>--Original Message--
>>Try:
>>declare @.id int
>>exec OtherServer.OtherDB.dbo.sp_executesql
>> N'insert MyTable (ColA, ColB) values (@.a, @.b)
>> select @.id =3D @.@.identity'
>>, '@.a int, @.b int, @.id int'
>>, @.a =3D 1, @.b =3D 2, @.id =3D @.id out
>>-- >> Tom
>>----
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>..
>>"omninews" <cxie@.omnichoice.com> wrote in message >news:015801c377f8$140a7410$a001280a@.phx.gbl...
>> I am using SQL 2000 server. I have a SP that insert a >>record to a remote server table with Identity column as >>the ID. After the insert, I try to use @.@.Identity to get >>the newly inserted ID back. I got NULL. However, it works >>when I run the same SP against a DB on the same server.
>> How can I get the ID back from a remote server?
>> Thank you for any input
>.
>
--=_NextPart_000_0090_01C3866C.3C5B1D20
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Deadlocks are a bigger issue. =You'll have to ensure that you keep your transactions "narrow", i.e. do only the =essential work inside the transaction. Also, make sure you access the resources =in the exact same order in all transactions. You may want to consider =doing the insert via a stored proc on the remote server, instead of using sp_executesql.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"omninews" wrote in =message news:014f01c3842b$d1=8f3770$7d02280a@.phx.gbl...Tom and others, It works but sometimes I get dead lock. =Because this exec statement starts a new process on the remote server. After =this I have other update/delete might agaist the same remote database =objects which are within the same procedure ,therefore the main thread. Looks like =the exec started process does not cleanup itself after it finishes =it's job. Is there a way to kill itself after it is done? =Thank you! >--Original Message-->Tom> It works.Thank you a lot.>>>>--Original Message-->Try:>>declare @.id int>exec OtherServer.OtherDB.dbo.sp_executesql> =N'insert MyTable (ColA, ColB) values (@.a, @.b)> =select @.id =3D @.@.identity'>, '@.a int, @.b int, @.id =int'>, @.a =3D 1, @.b =3D 2, @.id =3D @.id =out>>-- > Tom>>---=-->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server =MVP>Columnist, SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>..>>=;"omninews" =wrote in message >news:015801c377f8$140a7410$a001280a@.phx.gbl...> &=nbsp; I am using SQL 2000 server. I have a SP that insert a >record =to a remote server table with Identity column as >the ID. After =the insert, I try to use @.@.Identity to get >the newly inserted ID =back. I got NULL. However, it works >when I run the same SP =against a DB on the same server.> How can I get the ID back =from a remote server?>> Thank you for any input>>.>

---

--=_NextPart_000_0090_01C3866C.3C5B1D20--