Does SQL server CDC (Change Data Capture) have some limitation with UDDT or Table-valued parameters

Question

Does SQL server CDC (Change Data Capture) have some limitation with UDDT or Table-valued parameters

Banti Gurbani 25

I have configured CDC on my SQL server DB and after enabling it, my application code started breaking in some scenarios. After troubleshooting, it is found that existing stored procedure with Table-Valued parameter is failing. I want to know

if there is anything is any limitation with Table-valued parameter or UDDT
Anything to be considered/handled seperately in the code/stored procedure to fix this issue

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-06-17T08:41:01.1933333+00:00

I have not heard about any issues with CDC and table-valued parameters, but I have not used CDC a whole lot.

Can you give more information about the "breaking"? What error message do you get? Can you share the code that is failing?
Banti Gurbani 25 Reputation points

2023-06-19T03:09:23.61+00:00
Thank you Erland. Follwoing two errors are recieved.

Error:1

SP Name : uspXyz

Description:

The adapter failed to transmit message going to send port "AbcSendPort" with URL "mssql://...". It will be retransmitted after the retry interval specified for this Send Port. Details:"System.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated.

The data for table-valued parameter "@Parameter1" doesn't conform to the table type of the parameter. SQL Server error is: 8152, state: 30

The statement has been terminated.

Error: 2

SP Name: usp123

Description:

A message sent to adapter "345" on send port "456" with URI "mssql://..." is suspended.

Error details: System.Data.SqlClient.SqlException (0x80131904): Operand type clash: uniqueidentifier is incompatible with int

The data for table-valued parameter "@Parameter2" doesn't conform to the table type of the parameter. SQL Server error is: 200, state: 7

based on troubleshooting, we have follwoing findings,

There is a Stored procedure getting called from MS BizTalk that accepts tabled value parameter. After enabling CDC, sequence of table columns sent as input parameter value is changing and hence SP is resulting in above error. we are not sure why sequnce of column is changing even though there is no change in the code and only CDC is enabled.
Anonymous

2023-06-23T06:57:58.08+00:00

Hi @Banti Gurbani

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Anonymous

2023-06-30T08:27:29.9433333+00:00

Hi @Banti Gurbani

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

1 answer

Your answer

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-06-17T08:41:01.1933333+00:00

I have not heard about any issues with CDC and table-valued parameters, but I have not used CDC a whole lot.

Can you give more information about the "breaking"? What error message do you get? Can you share the code that is failing?
Banti Gurbani 25 Reputation points

2023-06-19T03:09:23.61+00:00

Thank you Erland. Follwoing two errors are recieved.

Error:1

SP Name : uspXyz

Description:

The adapter failed to transmit message going to send port "AbcSendPort" with URL "mssql://...". It will be retransmitted after the retry interval specified for this Send Port. Details:"System.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated.

The data for table-valued parameter "@Parameter1" doesn't conform to the table type of the parameter. SQL Server error is: 8152, state: 30

The statement has been terminated.

Error: 2

SP Name: usp123

Description:

A message sent to adapter "345" on send port "456" with URI "mssql://..." is suspended.

Error details: System.Data.SqlClient.SqlException (0x80131904): Operand type clash: uniqueidentifier is incompatible with int

The data for table-valued parameter "@Parameter2" doesn't conform to the table type of the parameter. SQL Server error is: 200, state: 7

based on troubleshooting, we have follwoing findings,

There is a Stored procedure getting called from MS BizTalk that accepts tabled value parameter. After enabling CDC, sequence of table columns sent as input parameter value is changing and hence SP is resulting in above error. we are not sure why sequnce of column is changing even though there is no change in the code and only CDC is enabled.
Anonymous

2023-06-23T06:57:58.08+00:00

Hi @Banti Gurbani

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Anonymous

2023-06-30T08:27:29.9433333+00:00

Hi @Banti Gurbani

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Answer 1

Thanks for the information. I don't have a solution right now, but I have some ideas for troubleshooting.

If you are on SQL 2016 or SQL 2017, make sure that you have the more recent Service and Cumulative Update (please share the output of SELECT @@version) and then run

DBCC TRACEON(460, -1)

This will change the error message String or binary would be truncated so that is relates which table and column the error relates to, and you also see the truncated part of the data.

The other recommendation is that you set up a extended-event session which captures the event error_reported. Then you create a view over this event session that gives you information on where the error occurred. You can also run a stored procedure that gives you the full call stack. You find everything you need to know about this in this article on my web site: https://www.sommarskog.se/Short%20Stories/trace-exceptions.html

Share via

Does SQL server CDC (Change Data Capture) have some limitation with UDDT or Table-valued parameters

1 answer

Your answer