SQLTeam.com | Weblogs | Forums

Transactional Replication Issue


#1

Iam receiving the below error from distribution agent

Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x0003160400008152004B00000000, Command ID: 1)

Error messages:
Explicit value must be specified for identity column in table 'XYZ' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column. (Source: MSSQLServer, Error number: 545)

I have set IDENTITY_INSERT to OFF but I receive the message as

Cannot insert explicit value for identity column in table 'XYZ' when IDENTITY_INSERT is set to OFF.

Any Suggestions.


#2

Dunno what the issue is with replication (this error is occurring during replication, right?) but in case helpful this is the issue regarding a normal insert where an IDENTITY column is involved:

If you are attempting to insert a value into a column which is an IDENTITY column then you:

a) must set IDENTITY_INSERT to ON for that table before the insert (and turn it off against afterwards)
b) must specify all columns in the INSERT statement - you cannot just write:

INSERT INTO MyTargetTable
SELECT Col1, COl2, ...
FROM MySourceTable

so you must explicitly name all columns:

INSERT INTO MyTargetTable
(
   Col1, Col2, ...
)
SELECT Col1, COl2, ...
FROM MySourceTable