getting the identity...

Hi
I want to be able to return the identity of an insert from a linked server?
However when the code below runs I get returned a null value
...
insert into [presentationlap].scimitar.dbo.tblCoredata (lDataTypeID, sData,
lParentID)
select TOP 1 lDataTypeID, sData, lParentID from dbo.tblCoredata where
lcoreID = @.lcoreID
select scope_identity
...
BOL states
"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."
However I have tried creating a SP on the linked server which just returns
the scope_identity but this also returns null
Any ideas?
Thanks
RippoCan you please post your table as well.
"Facts are stupid things."
Ronald Reagan
"Richard Wilde" wrote:

> Hi
> I want to be able to return the identity of an insert from a linked server
?
> However when the code below runs I get returned a null value
> ...
> insert into [presentationlap].scimitar.dbo.tblCoredata (lDataTypeID, sData,
> lParentID)
> select TOP 1 lDataTypeID, sData, lParentID from dbo.tblCoredata where
> lcoreID = @.lcoreID
> select scope_identity
> ...
> BOL states
> "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 identi
ty
> value and return it to the calling connection on the local server."
> However I have tried creating a SP on the linked server which just returns
> the scope_identity but this also returns null
> Any ideas?
> Thanks
> Rippo
>
>|||ok no problem
CREATE TABLE [dbo].[tblCoreData] (
[lCoreID] [int] IDENTITY (1, 1) NOT NULL ,
[lDataTypeID] [smallint] NOT NULL ,
[sData] [nvarchar] (3900) COLLATE Latin1_General_CI_AS NULL ,
[lParentID] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCoreData] WITH NOCHECK ADD
CONSTRAINT [PK_tblCoreData] PRIMARY KEY CLUSTERED
(
[lCoreID]
) ON [PRIMARY]
GO
CREATE INDEX [IX_tblCoreData] ON [dbo].[tblCoreData]([lDataTypeID]) ON
[PRIMARY]
GO
CREATE INDEX [IX_tblCoreData_1] ON [dbo].[tblCoreData]([lParentID]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[tblCoreData] ADD
CONSTRAINT [FK_tblCoreData_tblCoreData] FOREIGN KEY
(
[lParentID]
) REFERENCES [dbo].[tblCoreData] (
[lCoreID]
),
CONSTRAINT [FK_tblDeviceData_tblDeviceDataTypes] FOREIGN KEY
(
[lDataTypeID]
) REFERENCES [dbo].[tblDataTypes] (
[lDataTypeID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO|||Richard,
Create a stored procedure with an output parameter in the linked server and
invoke it from your server.
AMB
"Richard Wilde" wrote:

> Hi
> I want to be able to return the identity of an insert from a linked server
?
> However when the code below runs I get returned a null value
> ...
> insert into [presentationlap].scimitar.dbo.tblCoredata (lDataTypeID, sData,
> lParentID)
> select TOP 1 lDataTypeID, sData, lParentID from dbo.tblCoredata where
> lcoreID = @.lcoreID
> select scope_identity
> ...
> BOL states
> "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 identi
ty
> value and return it to the calling connection on the local server."
> However I have tried creating a SP on the linked server which just returns
> the scope_identity but this also returns null
> Any ideas?
> Thanks
> Rippo
>
>|||I have still not able to return the identity of an insert from a linked
server.
I have a table on the linked server
CREATE TABLE [dbo].[test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[test] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
and have this stored procedure on the linked server
CREATE PROCEDURE dbo.uspReturnScope_Identity
AS
select scope_identity()
GO
on another server i have created a link to the above server and have
tried to run the following code in QA.
insert into [presentationlap].synctest.dbo.test (test) values('A test')
exec [presentationlap].synctest.dbo.uspReturnScope_Identity
However the identity is never returned. Can anyone help me trace the
problem further. Thank you
Richard|||On 22 Mar 2005 02:31:45 -0800, Rippo wrote:

>I have still not able to return the identity of an insert from a linked
>server.
(snip)
>and have this stored procedure on the linked server
>CREATE PROCEDURE dbo.uspReturnScope_Identity
>AS
>select scope_identity()
>GO
>on another server i have created a link to the above server and have
>tried to run the following code in QA.
>insert into [presentationlap].synctest.dbo.test (test) values('A test')
>exec [presentationlap].synctest.dbo.uspReturnScope_Identity
>However the identity is never returned. Can anyone help me trace the
>problem further. Thank you
Hi Richard,
I don't know very much about linked servers, but based on what you
write, I think that the remote call to the stored procedure runs in it's
own, seperate scope. That would explain why SCOPE_IDENTITY won't work.
Can't you change the logic to encapsulate the insert in a stored
procedure on a linked server, and have that procedure return the
scope_identity after performing the insert?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Identity values do not span its scope across servers so you cannot get
identity values back using queries with 4-part naming conventions. One
workaround in such cases is to use sp_ExecuteSQL like:
EXEC presentationlap.synctest.dbo.sp_ExecuteSQL N'
INSERT test ( test ) VALUES ( ''A test'' )
SELECT SCOPE_IDENTITY()'
Anith|||Anith
Thank you for the workaround. This will be perfect for what I need. I
misinterpited what BOL stated!
Thanks again
Richardsql