Solved

Unable to insert into identity column with linked servers

Posted on 2000-03-01
4
580 Views
Last Modified: 2008-02-26
Hi,
If I'm inserting records and I want to specify a value for an identity column on a local table I do this and everything works fine:
set identity_insert <table> on
insert <table> (f1 , f2) select 1 , 'blah'

But when I try to do the same thing from a linked server I encounter an error. I execute this SQL after setting up the linked server:
set identity_insert <server>.<database>.<owner>.<table> on

error message I encounter:
Server: Msg 8103, Level 16, State 1, Line 1
Table '<server>.<database>.<owner>.<table>' does not exist or cannot be opened for SET operation.

Can you not execute a SET command on a linked server? If not how can I explicitly insert values into the identity columns of linked tables?

Thanks!
Andrew
0
Comment
Question by:Andrew_Wiegand
4 Comments
 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2573170
Andrew_Wiegand,

You cannot execute SET operation on a linked server. See "SET IDENTITY_INSERT (T-SQL)" topic in BOL.

Why don't you use a remote stored procedure?

PD: More info is needed for better advice.
0
 

Author Comment

by:Andrew_Wiegand
ID: 2573400
SET IDENTITY_INSERT topic in BOL does not offer any information regarding executing the command with linked servers.  Remote Stored Procedures are a legacy feature of SQL Server 6.5 and I would rather not use them in 7.0.  Let me know what information you need and I'd be happy to provide it.  Like I mentioned above my problem is this:

How do I insert explicit values into the identity column of a table on another server using linked servers?

Perhaps this is not allowed?

Andrew
0
 
LVL 6

Expert Comment

by:Jon_Raymond
ID: 2575306
Just an idea:  Could you create a trigger on the table to run set identity_insert when an identity error occurs.  You might also need another flag field to determine if the insert is supposed to do this.
0
 
LVL 4

Accepted Solution

by:
wqw earned 300 total points
ID: 2575827
the other nasty error you might get when trying to set indetity_insert on linked server table is
Server: Msg 117, Level 15, State 1, Line 7
The object name '<server>.<db>.<owner>.' contains more than the maximum number of prefixes. The maximum is 2.

this is when elevating to distrbuted transaction.

well, i managed to insert values in a table on a linked server but don't ask me how. it's ugly. ok, here it is:

select * from openquery(boraweb,'select top 0 * from test1 set identity_insert test1 on insert test1 (id,c2)
values (25,''y0'')')

i told you it is ugly :-))

</wqw>
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now