SET IDENTITY_INSERT ON remote server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SET IDENTITY_INSERT ON remote server

Author

Topic

Kristen
Test

22859 Posts

Posted - 2007-05-23 : 13:08:56

How do I do:

SET IDENTITY_INSERT MyRemoteServer.MyRemoteDatabase.dbo.MyRemoteTable ON

Server: Msg 8103, Level 16, State 1, Line 1
Table 'MyRemoteServer.MyRemoteDatabase.dbo.MyRemoteTable' does not exist or cannot be opened for SET operation.

SELECT *
FROM OPENQUERY(MyRemoteServer, 'SET IDENTITY_INSERT MyRemoteDatabase.dbo.MyRemoteTable ON')

Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'SET IDENTITY_INSERT MyRemoteDatabase.dbo.MyRemoteTable ON'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='SQLOLEDB', Query=SET IDENTITY_INSERT MyRemoteDatabase.dbo.MyRemoteTable ON'].

SELECT TOP 10 * FROM MyRemoteServer.MyRemoteDatabase.dbo.MyRemoteTable

does give me data ...

Kristen

sshelper
Posting Yak Master

216 Posts

Posted - 2007-05-23 : 13:16:04

How about this:

EXECUTE [MyRemoteServer].[MyRemoteDatabase].[dbo].[sp_executesql] N'SET IDENTITY_INSERT dbo.MyRemoteTable ON'

SQL Server Helper
http://www.sql-server-helper.com

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-23 : 13:17:42

That worked.

sshelper
Posting Yak Master

216 Posts

Posted - 2007-05-23 : 13:21:38

Based on the error message, you can also try returning a dummy row in your OPENQUERY:

SELECT *
FROM OPENQUERY(MyRemoteServer, 'SET IDENTITY_INSERT MyRemoteDatabase.dbo.MyRemoteTable ON SELECT GETDATE() AS Now')

SQL Server Helper
http://www.sql-server-helper.com

Kristen
Test

22859 Posts

Posted - 2007-05-23 : 14:00:18

'Triffic. Nice solutions guys, however it brings up the next problem.

What I'm trying to do is "replicate" some rows to a remote database.

So I've got a script that does UPDATE This and INSERT That directly into the remote database.

That works fine, but a few of the tables have IDENTITY columns ...

If I do the ExecuteSQL thingie, and THEN try to INSERT directly to the remote table I get:

Server: Msg 7344, Level 16, State 1, Line 8
OLE DB provider 'SQLOLEDB' could not INSERT INTO table '' because of column 'MyIdentityColumn'. The user did not have permission to write to the column.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the provider: [COLUMN_NAME=Col1 STATUS=DBSTATUS_S_OK], [COLUMN_NAME=Col2 STATUS=DBSTATUS_S_OK], [COLUMN_NAME=Col3 STATUS=DBSTATUS_S_OK], [COLUMN_NAME=Col4 STATUS=DBSTATUS_S_OK], [COLUMN_NAME=Col5 STATUS=DBSTATUS_S_OK], [COLUMN_NAME...

I suppose I will have to turn off the IDENTITY property on the Remote table. I was hoping to keep them identical so any change-scripts to one could be re-used on the other database "as-is"

Kristen

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-23 : 14:13:11

quote:
Originally posted by sshelper

How about this:

EXECUTE [MyRemoteServer].[MyRemoteDatabase].[dbo].[sp_executesql] N'SET IDENTITY_INSERT dbo.MyRemoteTable ON'

SQL Server Helper
http://www.sql-server-helper.com

Shouldnt the INSERT be part of the same batch too?
You cannot execute the SET command first followed by another INSERT right?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-23 : 14:17:18

Also, not sure if this will work for you but you could create a proc with your INSERT//UPDATE scripts along with the corresponding SET IDENTITY_INSERT's and execute the proc there?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-05-23 : 15:03:37

Yup, they are good points, but no doubt you have guessed that it aint quite that simple from where I'm sitting!

We have stuff to mechanically generate an "upsert" script for this sort of "staging + poor-man's-replication" and for the first time I'm trying to "just execute it" across a remote-server-link.

If I make an Sproc then keeping it up to date when the table structure changes becomes a chore (whereas now I just re-create the "upsert" script and I'm done ...)

I'm gonna drop the IDENTITY columns as the only inserter is me, and the table in the original database has an IDENTITY attribute column, and that's going to be good enough.

"You cannot execute the SET command first followed by another INSERT right?"

Actually I think you can, but not sure about different users / sessions etc.

SET IDENTITY_INSERT is "sticky". So you can do that, go away, come back, and do your INSERT. PLUS you must UnSet it before you can Set it on another table ...

But whether some-other-Joe could Insert into a table that you have SET IDENTITY_INSERT on, I don't know.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources