0

I am doing a experimental script to do a SQL Comparison (COLLATED as case-sensitive) and I am having issues with the SET IDENTITY_INSERT <Table> ON

I have switched on this option and disabled foreign key checks, but it still seems to be complaining about the latter.

Here are the steps I followed:

1 - I created a linked server

EXEC sp_addlinkedserver @Server=N'xxx.xxx.xxx.xxx', @srvproduct=N'SQL Server'

2 - I added the login credentials

EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname = N'xxx.xxx.xxxx.xxx', 
@locallogin = NULL , 
@useself = N'False',
@rmtuser = N'xxxxxxxxxxx',
@rmtpassword = N'xxxxxxxxxxx'

3 - In the same batch, I set the identity_insert, disabled foreign key checks and ran the following merge script. Note, the deferred query returns an XML field which is disallowed over distributed servers, so I casted to NVARCHAR(MAX)

SET IDENTITY_INSERT [DATABASE1].[dbo].[TABLE1] ON
ALTER TABLE [DATABASE1].[dbo].[TABLE1] NOCHECK CONSTRAINT ALL

MERGE [DATABASE1].[dbo].[TABLE1]
USING OPENQUERY([xxx.xxx.xxx.xxx], 'SELECT S.ID, S.EventId, S.SnapshotTypeID, CAST(S.Content AS NVARCHAR(MAX)) AS Content FROM [DATABASE1].[dbo].[TABLE1] AS S') AS S
ON (CAST([DATABASE1].[dbo].[TABLE1].Content AS NVARCHAR(MAX)) = S.Content)
WHEN NOT MATCHED BY TARGET
    THEN INSERT VALUES (S.ID, S.EventId, S.SnapshotTypeID, CAST(S.Content AS XML))
WHEN MATCHED 
    THEN UPDATE SET [DATABASE1].[dbo].[TABLE1].EventId = S.EventId,
                    [DATABASE1].[dbo].[TABLE1].SnapshotTypeID = S.SnapshotTypeID,
                    [DATABASE1].[dbo].[TABLE1].Content = S.Content
COLLATE Latin1_General_CS_AS;
GO

The error message I am getting reads as follows:

Msg 8101, Level 16, State 1, Line 4
An explicit value for the identity column in table 'Database1.dbo.Table' can only be specified when a column list is used and IDENTITY_INSERT is ON.

How can I fix this? As I mentioned, this script is only an experiment for one of the systems I am writing. I am probably reinventing the wheel somewhere, but its all about learning in this exercise.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Eon
  • 3,833
  • 10
  • 46
  • 75
  • This script is a comparison script which reads data from a table on a remote server, and inserts / updates it into a table which schema is identical on my local. The problems here are three-fold 1- When you used a linked server (the remote server), any and all XML fields needs to be converted first, else you get an error. So OpenQuery's result set acts as my source. 2- Conversion slows down the query a lot. 3- The merge statement doesn't run at all because it is complaining about the `SET IDENTITY_INSERT ON` - Which is what I am trying **to solve in this question**. – Eon Jan 19 '15 at 12:48
  • There are tools out there to do the job, but as I mentioned a few times, this script is supposed to be experimental – Eon Jan 19 '15 at 12:49
  • 2
    when a column list is used - you have no column list – paparazzo Jan 19 '15 at 12:49
  • I see what you meant there Blam. Let me add a column list - did not think about that – Eon Jan 19 '15 at 12:52
  • @Blam You solved it. Add your answer. – Eon Jan 19 '15 at 12:56
  • possible duplicate of [Set IDENTITY\_INSERT ON is not working](http://stackoverflow.com/questions/12006273/set-identity-insert-on-is-not-working) – Tim Abell Jun 11 '15 at 09:04

1 Answers1

1

An explicit value for the identity column in table 'Database1.dbo.Table' can only be specified when a column list is used and IDENTITY_INSERT is ON.

You have no column list

paparazzo
  • 44,497
  • 23
  • 105
  • 176
  • I know about this. I feel like a brick for forgetting that during scripting. I should read my errors more > – Eon Jan 19 '15 at 13:00