System.Data.SqlClient.SqlException: An explicit value for the identity column in table '#CascadeCollect' can only be specified when a column list is used and IDENTITY_INSERT is ON.

(0) Share

Report

Posted on by Community Member

Hello,

When I was trying to merge contacts in CRM, I got this error in the log file:

System.Data.SqlClient.SqlException: An explicit value for the identity column in table '#CascadeCollect' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Someone please help because it's been an issue for awhile and I couldn't find any solution for this.

Thanks.

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Sort by

Suggested answer

Radu Chiribelea 6,667 on at

Like (1)

Report
Copy link

Link copied!

Hello,

Is this CRM Online or OnPrem?

Usually when contacts are merged, deleted, etc. depending on how the relationships is set up between the Contacts and their child entity records, the changes that happen to the parent contact entity are cascaded to the child records (hence this is why it is calling #CascadeCollect)

What build is your CRM System (binaries and Database?)

I suggest you collect a set of CRM Platform Traces & SQL Profiler if it is CRM OnPremise. This should give you some more information on why it is failing and what could case the issue. If it is CRM Online, then best to reach out to Microsoft Support.

Hope this helps,

Radu

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Radu,

We use CRM OnPrem and it's database system.

Here is more detail for the problem:

>Crm Exception: Message: Generic SQL error., ErrorCode: -2147204784, InnerException: System.Data.SqlClient.SqlException (0x80131904): An explicit value for the identity column in table '#CascadeCollect' can only be specified when a column list is used and IDENTITY_INSERT is ON.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Crm.CrmDbConnection.InternalExecuteWithRetry[TResult](Func`1 ExecuteMethod, IDbCommand command)
at Microsoft.Crm.CrmDbConnection.InternalExecuteNonQuery(IDbCommand command, Boolean capturePerfTrace)
at Microsoft.Crm.BusinessEntities.CrmCascadeDBCollection.<>c__DisplayClass1.<Collect>b__0(IDbCommand cmd)
at Microsoft.Crm.BusinessEntities.CrmCascadeDBHandler.Collect(String cascadingSql, Int32 entityOtc, ExecutionContext context, CollectDelegate CollectDelegate)
at Microsoft.Crm.BusinessEntities.CascadeEngine.ModifyAccessDB(BusinessEntityMoniker moniker, PrincipalAccess grantee, ExecutionContext context)
at Microsoft.Crm.BusinessEntities.BusinessProcessObject.Merge(BusinessEntityMoniker masterMoniker, Guid subordinateId, IBusinessEntity entity, Boolean bPerformParentingChecks, ExecutionContext context)
Error Number:8101,State:1,Class:16

Can you tell me where to start fixing this problem because I can't make any change on relationship behavior for contacts entity?

Was this reply helpful? Yes No
Radu Chiribelea 6,667 on at

Like (1)

Report
Copy link

Link copied!

Hello,

Do you also have the SQL Statement that is causing the exception? I suspect a CRM Bug, but i would need to see the actual SQL Statement before being able to suggest how to move further. If you don't know how to identify it, you can share the traces and i can have a look

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hello Radu,

I don't have SQL Statement. I merged the contacts through the MS CRM and it threw the SQL exception in the log file that I turned on the tracing to identify the root of the problem. How would you like me to share the tracing file with you ?

Thank you.

Was this reply helpful? Yes No
Radu Chiribelea 6,667 on at

Like (1)

Report
Copy link

Link copied!

Hi Tanasit - you can ping me on IM and we can take it from there

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Radu,

I'm sorry I'm new to this community. I don't know how to ping you on IM. Here is my email: trnrsi_po@hotmail.com

I will send you log file when I get hold of you via email.

Thank you for helping me out !!

Was this reply helpful? Yes No