September 27, 2012 at 10:24 am
I have a data archiving process that SELECTS data out of one server and INSERTS it into another via a linked server connection. It's a very simple script (below), while I have NO ISSUES running the command, I cannot compile the procedure It appears that you can't set the IDENTITY_INSERT across the linked server because it has more than 2 prefixes!
BEGIN TRY
/* Truncate the table before loading */
TRUNCATE TABLE [NAS2-RPS].CORELIBRARY_ARCHIVE.dbo.MessengerLog
/* Archive the data */
SET IDENTITY_INSERT [NAS2-RPS].CORELIBRARY_ARCHIVE.dbo.MessengerLog ON
INSERT INTO [NAS2-RPS].CORELIBRARY_ARCHIVE.dbo.MessengerLog (SequenceNumber, MessengerPrimaryKey, MessengerTableName, MessengerOperationType)
SELECT SequenceNumber, MessengerPrimaryKey, MessengerTableName, MessengerOperationType
FROM CORELIBRARY_STAGING.dbo.MessengerLog
SELECT @Error = @@ERROR, @BatchSize = @@ROWCOUNT
SET IDENTITY_INSERT [NAS2-RPS].CORELIBRARY_ARCHIVE.dbo.MessengerLog OFF
/* Truncate the table */
TRUNCATE TABLE CORELIBRARY_STAGING.dbo.MessengerLog
IF @Error = 0
BEGIN
Msg 117, Level 15, State 1, Procedure utl_daily_CL_MessengerLog, Line 53
The object name 'NAS2-RPS.CORELIBRARY_ARCHIVE.dbo.MessengerLog' contains more than the maximum number of prefixes. The maximum is 2.
I've tried creating a procedure on the remote server and then passing in the same command as a string, then using spexecute_sql on the remote server to run what I need but get the same error because I'm trying to execute a stored-procedure against the linked server (Yes, the linked server was created with RPC)
Example EXEC [NAS2-RPS].F1Settings.dbo.utl_SetIdentityInsert N'SET IDENTITY_INSERT CORELIBRARY_ARCHIVE.dbo.MessengerLog ON'
Same error. Now I am about 99.9% certain I've never had issues executing stored-procs on a linked server...
What am I missing?
DDL for the table referenced above
CREATE TABLE [dbo].[MessengerLog] (
[SequenceNumber] [int] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL,
[MessengerPrimaryKey] [varchar](255) NULL,
[MessengerTableName] [varchar](255) NULL,
[MessengerOperationType] [varchar](1) NULL,
CONSTRAINT [csPk_MessengerLog] PRIMARY KEY CLUSTERED ([SequenceNumber] ASC))
ON [PRIMARY]
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 27, 2012 at 11:01 am
See if this gets you there:
EXEC('SET IDENTITY_INSERT CORELIBRARY_ARCHIVE.dbo.MessengerLog ON') AT [NAS2-RPS];
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 27, 2012 at 11:21 am
One more comment: you might care to change this to be a "pull" rather than a push. As in - define the stored proc on the [NAS-rp2] server and have it pull the data from the main server. Inserts performed on a remote server over a linked server tend to spool down to cursor-like performance: running a select over that same linked server does seem to stream, so the insert would finish a LOT faster.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 27, 2012 at 1:10 pm
@opc.three - tried that. Again I can execute it but the code won't compile. Executes successfully, gives the above error when hitting the parse or compile buttons.
@Matt. Yes I have considered this however it would mean rewriting a lot of code...that I don't want to have to do unless there's absolutely no other way.
I commented out the IDENTITY_INSERT code completely and still get the same error when running the code below...so in fact, I don't believe it's directly related to the IDENTITY_INSERT as much as anything attempting to hit the linked server.
INSERT INTO [NAS2-RPS].CORELIBRARY_ARCHIVE.dbo.MessengerLog (SequenceNumber, MessengerPrimaryKey, MessengerTableName, MessengerOperationType)
SELECT SequenceNumber, MessengerPrimaryKey, MessengerTableName, MessengerOperationType
FROM CORELIBRARY_STAGING.dbo.MessengerLog
Ironically, when I go to another server and find code when I know da#% well I excecute code via a linked server (exec [NAS2-DBR].F1Settings.dbo.utl_CheckProgressTables 3) , it works A-O-K :: making me think there's something seriously wonky going on with this server or linked server
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 27, 2012 at 1:14 pm
MyDoggieJessie (9/27/2012)
@opc.three - tried that. Again I can execute it but the code won't compile. Executes successfully, gives the above error when hitting the parse or compile buttons.
Right. Because IDENTITY_INSERT is only valid for a given session which is why it makes no sense to try and do it using 4-part naming. You are 'there' now 🙂
Matt is alluding to the correct solution. Rewrite the process on archive-system to pull data from the data systems.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 27, 2012 at 1:58 pm
So why is it possible that I can compile a procedure on Server A with a 4-part naming with no issues
But on Server B, I get that error?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 27, 2012 at 2:12 pm
I was keying on the first part of your post having to do with IDENTITY_INSERT and I think we have beaten that horse silly.
Looking at the error message:
The object name 'NAS2-RPS.CORELIBRARY_ARCHIVE.dbo.MessengerLog' contains more than the maximum number of prefixes. The maximum is 2.
Just a guess but hyphens are not part of the allowable of characters for SQL object names so names that include them need to be treated as literals. Is it missing square-brackets in the code by chance?
Should it be [NAS2-RPS].CORELIBRARY_ARCHIVE.dbo.MessengerLog?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 27, 2012 at 2:22 pm
Yes it is supposed to be []
INSERT INTO [NAS2-RPS].CORELIBRARY_ARCHIVE.dbo.MessengerLog (SequenceNumber, MessengerPrimaryKey, MessengerTableName, MessengerOperationType)
SELECT SequenceNumber, MessengerPrimaryKey, MessengerTableName, MessengerOperationType
FROM CORELIBRARY_STAGING.dbo.MessengerLog
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 27, 2012 at 3:10 pm
this may fix your prefix issue:
EXEC('use CORELIBRARY_ARCHIVE;SET IDENTITY_INSERT dbo.MessengerLog ON') AT [NAS2-RPS];
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 27, 2012 at 3:42 pm
Matt Miller (#4) (9/27/2012)
this may fix your prefix issue:
EXEC('use CORELIBRARY_ARCHIVE;SET IDENTITY_INSERT dbo.MessengerLog ON') AT [NAS2-RPS];
That may execute but when you go to do the insert using 4-part naming it will fail because it's a new session and the IDENTITY_INSERT is OFF at the outset of the new session.
In my testing that's how it worked but I may be missing something you're pointing out.
This succeeded:
EXEC('Use test;set identity_insert dbo.sd ON;INSERT INTO test.dbo.sd (id, name) VALUES (1,''Hi'');') AT [MyPC\STANDARD2008R2];
1 row affected.
But this one fails because it's actually two sessions:
EXEC('Use Test;set identity_insert dbo.sd ON;') AT [MyPC\STANDARD2008R2];
INSERT INTO [MyPC\STANDARD2008R2].test.dbo.sd (id, name) VALUES (1,'Hi');
OLE DB provider "SQLNCLI11" for linked server "MyPC\STANDARD2008R2" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7344, Level 16, State 1, Line 2
The OLE DB provider "SQLNCLI11" for linked server "MyPC\STANDARD2008R2" could not INSERT INTO table "[MyPC\STANDARD2008R2].[test].[dbo].[sd]" because of column "id". The user did not have permission to write to the column.
My table:
CREATE TABLE test.dbo.sd (id INT IDENTITY(1,1), name VARCHAR(100));
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 27, 2012 at 3:49 pm
opc.three (9/27/2012)
Matt Miller (#4) (9/27/2012)
this may fix your prefix issue:
EXEC('use CORELIBRARY_ARCHIVE;SET IDENTITY_INSERT dbo.MessengerLog ON') AT [NAS2-RPS];That may execute but when you go to do the insert using 4-part naming it will fail because it's a new session and the IDENTITY_INSERT is OFF at the outset of the new session.
In my testing that's how it worked but I may be missing something you're pointing out.
This succeeded:
EXEC('Use test;set identity_insert dbo.sd ON;INSERT INTO test.dbo.sd (id, name) VALUES (1,''Hi'');') AT [MyPC\STANDARD2008R2];1 row affected.
But this one fails because it's actually two sessions:
EXEC('Use Test;set identity_insert dbo.sd ON;') AT [MyPC\STANDARD2008R2];INSERT INTO [MyPC\STANDARD2008R2].test.dbo.sd (id, name) VALUES (1,'Hi');
OLE DB provider "SQLNCLI11" for linked server "MyPC\STANDARD2008R2" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7344, Level 16, State 1, Line 2
The OLE DB provider "SQLNCLI11" for linked server "MyPC\STANDARD2008R2" could not INSERT INTO table "[MyPC\STANDARD2008R2].[test].[dbo].[sd]" because of column "id". The user did not have permission to write to the column.
My table:
CREATE TABLE test.dbo.sd (id INT IDENTITY(1,1), name VARCHAR(100));
Agreed and by the time you're doing this you might as well be running OPENQUERY (since you're already setting yourself up as a pull).
Only other thing that comes up might be that the linked server security might not be exposing the right stuff (so the target DB might not be visible from the linked server creds).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply