虽然IDENTITY_INSERT设置为ON，但无法为标识列插入显式值 [英] Cannot insert explicit value for identity column although IDENTITY_INSERT is set to ON

问题描述

您好，

我使用CopyData活动将整个表从mySQL数据库复制到Azure SQL Server数据库。现在，在四个表中的两个表中，每次都会收到以下错误，并且不会占用任何行：

I use a CopyData activity to copy entire tables from a mySQL DB to an Azure SQL Server DB. Now in two of four tables I receive the following error every time, and no rows are taken over:

{
    "errorCode": "2200",
    "message": "ErrorCode=FailedDbOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Cannot insert explicit value for identity column in table 'artikel' when IDENTITY_INSERT is set to OFF.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Cannot insert explicit value for identity column in table 'artikel' when IDENTITY_INSERT is set to OFF.,Source=.Net SqlClient Data Provider,SqlErrorNumber=544,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=544,State=1,Message=Cannot insert explicit value for identity column in table 'artikel' when IDENTITY_INSERT is set to OFF.,},],'",
    "failureType": "UserError",
    "target": "CopyData"
}

我已在预拷贝脚本中的TRUNCATE之后添加了RESEED和SET IDENTITY_INSERT：

I already added a RESEED and SET IDENTITY_INSERT ON after the TRUNCATE in the precopy script:

TRUNCATE TABLE [@{item().TABLE_SCHEMA}].[@{item().TABLE_NAME}];
DBCC CHECKIDENT ('[@{item().TABLE_SCHEMA}].[@{item().TABLE_NAME}]', RESEED, 1);
SET IDENTITY_INSERT [@{item().TABLE_SCHEMA}].[@{item().TABLE_NAME}] ON;

但错误仍然存​​在。我还能做些什么？

However the error still remains. What can I do further?

推荐答案

你可以尝试做
RESEED并在TRUNCATE操作后设置IDENTITY_INSERT ON存储过程活动。

请试一试，告诉我们是否有帮助。另外，我们可以继续深入研究。

这篇关于虽然IDENTITY_INSERT设置为ON，但无法为标识列插入显式值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！