title: Case-Sensitive Linked-Server merge script - Set IDENTITY_INSERT <table> ON not working
body: <p>I am doing a experimental script to do a SQL Comparison (<code>COLLATED</code> as case-sensitive) and I am having issues with the <code>SET IDENTITY_INSERT &lt;Table&gt; ON</code></p>
  
  <p>I have switched on this option and disabled foreign key checks, but it still seems to be complaining about the latter.</p>
  
  <p>Here are the steps I followed:</p>
  
  <p>1 - I created a linked server</p>
  
  <pre><code>EXEC sp_addlinkedserver @Server=N'xxx.xxx.xxx.xxx', @srvproduct=N'SQL Server'
  </code></pre>
  
  <p>2 - I added the login credentials</p>
  
  <pre><code>EXEC master.dbo.sp_addlinkedsrvlogin 
  @rmtsrvname = N'xxx.xxx.xxxx.xxx', 
  @locallogin = NULL , 
  @useself = N'False',
  @rmtuser = N'xxxxxxxxxxx',
  @rmtpassword = N'xxxxxxxxxxx'
  </code></pre>
  
  <p>3 - In the same batch, I set the <code>identity_insert</code>, disabled foreign key checks and ran the following merge script. Note, the deferred query returns an <code>XML</code> field which is disallowed over distributed servers, so I casted to <code>NVARCHAR(MAX)</code></p>
  
  <pre><code>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
  </code></pre>
  
  <p>The error message I am getting reads as follows:</p>
  
  <blockquote>
    <p>Msg 8101, Level 16, State 1, Line 4<br>
    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.</p>
  </blockquote>
  
  <p>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.</p>