Unable to insert into identity column with linked servers

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
Andrew_WiegandAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
wqwConnect With a Mentor Commented:
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
 
Gustavo Perez BuenrostroCommented:
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
 
Andrew_WiegandAuthor Commented:
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
 
Jon_RaymondCommented:
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
All Courses

From novice to tech pro — start learning today.