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

Getting Msg 18456 in referencing a database from a linked server

Trying to use linked server for the first time and having some problems.
I have 2 servers "ServerA" and "ServerB". From ServerA I want to be able to
access data from ServerB. I ran the following on ServerA
USE master
GO
EXEC sp_addlinkedserver 'ServerB', N'SQL Server'
GO
Then I ran the following SQL on ServerA
SELECT * FROM SERVERB.DBNAME.dbo.TABLENAME
And I get the following message:
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Can someone tell me how I can get past this problem?
Thanks in advance!
What security type are you using?
Standard or Windows?
Your sp_addlinkedserver will default to use "current security credentials"
to authenticate to serverb.
You should try adding your linked server passing an SQL
authentication/Valid Windows account depending on your security type.
e.g. sp_addlinkedsrvlogin ServerB ,false ,null , SQLUSER, USERPASSWORD"
Immy
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:ed748v7TEHA.3420@.TK2MSFTNGP09.phx.gbl...
> Trying to use linked server for the first time and having some problems.
> I have 2 servers "ServerA" and "ServerB". From ServerA I want to be
able to
> access data from ServerB. I ran the following on ServerA
> USE master
> GO
> EXEC sp_addlinkedserver 'ServerB', N'SQL Server'
> GO
> Then I ran the following SQL on ServerA
> SELECT * FROM SERVERB.DBNAME.dbo.TABLENAME
> And I get the following message:
> Server: Msg 18456, Level 14, State 1, Line 1
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> Can someone tell me how I can get past this problem?
> Thanks in advance!
>
>
|||Thanks Immy...sp_addlinkedsrvlogin was exactly what I needed!
"Immy" <imtiaz_ullah@.hotmail.com> wrote in message
news:u8xksz7TEHA.1472@.TK2MSFTNGP12.phx.gbl...
> What security type are you using?
> Standard or Windows?
> Your sp_addlinkedserver will default to use "current security
credentials"[vbcol=seagreen]
> to authenticate to serverb.
> You should try adding your linked server passing an SQL
> authentication/Valid Windows account depending on your security type.
> e.g. sp_addlinkedsrvlogin ServerB ,false ,null , SQLUSER, USERPASSWORD"
> Immy
> "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> news:ed748v7TEHA.3420@.TK2MSFTNGP09.phx.gbl...
problems.
> able to
>

Getting Msg 18456 in referencing a database from a linked server

Trying to use linked server for the first time and having some problems.
I have 2 servers "ServerA" and "ServerB". From ServerA I want to be able to
access data from ServerB. I ran the following on ServerA
USE master
GO
EXEC sp_addlinkedserver 'ServerB', N'SQL Server'
GO
Then I ran the following SQL on ServerA
SELECT * FROM SERVERB.DBNAME.dbo.TABLENAME
And I get the following message:
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Can someone tell me how I can get past this problem?
Thanks in advance!What security type are you using?
Standard or Windows?
Your sp_addlinkedserver will default to use "current security credentials"
to authenticate to serverb.
You should try adding your linked server passing an SQL
authentication/Valid Windows account depending on your security type.
e.g. sp_addlinkedsrvlogin ServerB ,false ,null , SQLUSER, USERPASSWORD"
Immy
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:ed748v7TEHA.3420@.TK2MSFTNGP09.phx.gbl...
> Trying to use linked server for the first time and having some problems.
>
> I have 2 servers "ServerA" and "ServerB". From ServerA I want to be
able to
> access data from ServerB. I ran the following on ServerA
> USE master
> GO
> EXEC sp_addlinkedserver 'ServerB', N'SQL Server'
> GO
>
> Then I ran the following SQL on ServerA
> SELECT * FROM SERVERB.DBNAME.dbo.TABLENAME
>
> And I get the following message:
> Server: Msg 18456, Level 14, State 1, Line 1
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
>
> Can someone tell me how I can get past this problem?
>
> Thanks in advance!
>
>
>|||Thanks Immy...sp_addlinkedsrvlogin was exactly what I needed!
"Immy" <imtiaz_ullah@.hotmail.com> wrote in message
news:u8xksz7TEHA.1472@.TK2MSFTNGP12.phx.gbl...
> What security type are you using?
> Standard or Windows?
> Your sp_addlinkedserver will default to use "current security
credentials"
> to authenticate to serverb.
> You should try adding your linked server passing an SQL
> authentication/Valid Windows account depending on your security type.
> e.g. sp_addlinkedsrvlogin ServerB ,false ,null , SQLUSER, USERPASSWORD"
> Immy
> "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> news:ed748v7TEHA.3420@.TK2MSFTNGP09.phx.gbl...
> > Trying to use linked server for the first time and having some
problems.
> >
> > I have 2 servers "ServerA" and "ServerB". From ServerA I want to be
> able to
> > access data from ServerB. I ran the following on ServerA
> > USE master
> > GO
> > EXEC sp_addlinkedserver 'ServerB', N'SQL Server'
> > GO
> >
> > Then I ran the following SQL on ServerA
> > SELECT * FROM SERVERB.DBNAME.dbo.TABLENAME
> >
> > And I get the following message:
> > Server: Msg 18456, Level 14, State 1, Line 1
> > Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> >
> > Can someone tell me how I can get past this problem?
> >
> > Thanks in advance!
> >
> >
> >
>

Getting Msg 18456 in referencing a database from a linked server

Trying to use linked server for the first time and having some problems.
I have 2 servers "ServerA" and "ServerB". From ServerA I want to be able to
access data from ServerB. I ran the following on ServerA
USE master
GO
EXEC sp_addlinkedserver 'ServerB', N'SQL Server'
GO
Then I ran the following SQL on ServerA
SELECT * FROM SERVERB.DBNAME.dbo.TABLENAME
And I get the following message:
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Can someone tell me how I can get past this problem?
Thanks in advance!What security type are you using?
Standard or Windows?
Your sp_addlinkedserver will default to use "current security credentials"
to authenticate to serverb.
You should try adding your linked server passing an SQL
authentication/Valid Windows account depending on your security type.
e.g. sp_addlinkedsrvlogin ServerB ,false ,null , SQLUSER, USERPASSWORD"
Immy
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:ed748v7TEHA.3420@.TK2MSFTNGP09.phx.gbl...
> Trying to use linked server for the first time and having some problems.
> I have 2 servers "ServerA" and "ServerB". From ServerA I want to be
able to
> access data from ServerB. I ran the following on ServerA
> USE master
> GO
> EXEC sp_addlinkedserver 'ServerB', N'SQL Server'
> GO
> Then I ran the following SQL on ServerA
> SELECT * FROM SERVERB.DBNAME.dbo.TABLENAME
> And I get the following message:
> Server: Msg 18456, Level 14, State 1, Line 1
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> Can someone tell me how I can get past this problem?
> Thanks in advance!
>
>|||Thanks Immy...sp_addlinkedsrvlogin was exactly what I needed!
"Immy" <imtiaz_ullah@.hotmail.com> wrote in message
news:u8xksz7TEHA.1472@.TK2MSFTNGP12.phx.gbl...
> What security type are you using?
> Standard or Windows?
> Your sp_addlinkedserver will default to use "current security
credentials"
> to authenticate to serverb.
> You should try adding your linked server passing an SQL
> authentication/Valid Windows account depending on your security type.
> e.g. sp_addlinkedsrvlogin ServerB ,false ,null , SQLUSER, USERPASSWORD"
> Immy
> "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> news:ed748v7TEHA.3420@.TK2MSFTNGP09.phx.gbl...
problems.[vbcol=seagreen]
> able to
>

Getting meta-data for Linked Servers

My customer has a .NET application that reads meta data from SQL Server, Oracle, DB2, and several propritary databases. Because each DBMS stores the meta data using various techniques, they have written custom code for each DBMS. They are working on a generic ODBC/OLEDB suppport, but in the interim I was trying to use SQL Server to link to an Access database. The Access linked server works fine for queries in Query Analyzer, but I would like to be able to programatically read the metadata for an Access DB (tables, columns, types, etc) via the linked server. SQL Server's usual mechanism for storing meta-data in the Master database aparently is not used for Linked Servers.

Does SQL Server expose Linked Server meta data?

How would one retrieve this meta data if it is exposed?

Thanks,

Ben

Hi Ben,

No SQL Server does not store metadata about linked servers (excluding connection information stored in sys.servers/sysservers). You would need to interrogate the actual linked server for this. So, if it were MSAccess, you would need to refer to the system tables (such as MSysObjects) etc.

Cheers,

Rob

|||

I was afraid that was the answer.

Thanks