Identity insert on linked server fails

IDENTITY_INSERT doesn't work with linked servers AFAIK, unless you execute dynamic SQL that includes the SET IDENTITY_INSERT in the batch or have some code (Stored Proc for instance) on the remote server which does that for you.

The IDENTITY_INSERT is per-session (see MSDN) and when you use the remote server this will probably be in a different session from your statement executed via [LINKEDSERVER].tempdb.sys.sp_sqlexec, which causes it to fail as you see it happening.

You can insert an identity value into a table with an identity column on a linked server with the "SWITCH TO" trick.

If you haven't used the "SWITCH TO" trick to add and remove identity on a column, it's very quick, even on large tables!

Conceptually you simply create a new SCHEMA exactly like the table you are wanting to INSERT to without the identity defined. Then switch the table to that SCHEMA and do your INSERT. Then switch back to the SCHEMA with the identity defined.
The sample below has been tested on a linked server in AZURE.All the caveats of using "SWITCH TO" apply (indexes must be the same, drop and recreate foreign keys, etc)

To test, you can run the full script below on an Linked Azure SQL Server database. You'll need to do a find/replace with [LINKED_SERVER_NAME] and [DATABASE_NAME], replacing with your values. On a non-Azure DB you may need to add "ON PRIMARY" to the table creations.

--Let's setup the example by creating a table with an IDENTITY column on the Linked ServerEXEC('CREATE TABLE [DATABASE_NAME].[dbo].[Example_Table](    [ID] [int] IDENTITY(1,1) NOT NULL,    [Name] [nchar](10) NULL) ') AT [LINKED_SERVER_NAME]--INSERT some data into the tableINSERT INTO [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table] ([Name]) VALUES ('Travis')INSERT INTO [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table] ([Name]) VALUES ('Mike')-- Looks goodSELECT * FROM [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table]GO-- Create a TABLE with an identical schema, without the identity definedEXEC('CREATE TABLE [DATABASE_NAME].[dbo].[Example_Table_temp](    [ID] [int] NOT NULL,    [Name] [nchar](10) NULL) ') AT [LINKED_SERVER_NAME]--Now Use the "SWITCH TO" to move the data to the new tableEXEC(' ALTER TABLE [DATABASE_NAME].[dbo].[Example_Table] SWITCH TO [DATABASE_NAME].[dbo].[Example_Table_temp]  ') AT [LINKED_SERVER_NAME]--Drop the old table (It should now be empty, but you may want to verify that if you are unsure here)EXEC(' DROP TABLE [DATABASE_NAME].[dbo].[Example_Table] ') AT [LINKED_SERVER_NAME] --Rename the new table back to the old table name-- NOTE the lack of database and owner identifiers in the new name-- NOTE the use of double single qoutes (ESCAPED single quotes)EXEC('USE [DATABASE_NAME];  EXEC sp_rename ''[DATABASE_NAME].[dbo].Example_Table_temp'',''Example_Table''   ') AT [LINKED_SERVER_NAME]     -- Now do your IDENTITY INSERTs !!!! INSERT INTO [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table] (ID,[Name]) VALUES (888,'Travis') INSERT INTO [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table] (ID,[Name]) VALUES (999,'Mike') --Verify they got put inSELECT * FROM [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table]     --Now let's switch it back to our SCHEMA with an IDENTITY  EXEC('CREATE TABLE [DATABASE_NAME].[dbo].[Example_Table_temp](    [ID] [int] IDENTITY(1,1) NOT NULL,    [Name] [nchar](10) NULL) ALTER TABLE [DATABASE_NAME].[dbo].[Example_Table] SWITCH TO [DATABASE_NAME].[dbo].[Example_Table_temp] DROP TABLE [DATABASE_NAME].[dbo].[Example_Table]  EXEC sp_rename ''[DATABASE_NAME].[dbo].Example_Table_temp'',''Example_Table''     ') AT [LINKED_SERVER_NAME] --Data is still there SELECT * FROM [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table]  GO -- And note you can no longer INSERT the IDENTITY INSERT INTO [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table] (ID,[Name]) VALUES (45,'Travis')GO

You need to execute dynamic query Example :

exec [LINKSERVERNAME].[DATABASENAME].[SCHEMANAME].sp_executesql N'Your Query'

If any column set identity the you need to set SET IDENTITY_INSERT TargetTable ON and need to specified the column name. Example:

SET IDENTITY_INSERT TargetTable ON;INSERT INTO TargetTable(Col1, Col2, Col3)SELECT Col1, Col2, Col3 FROM SourceTable;SET IDENTITY_INSERT TargetTable OFF;