0

I'm working at a client and their CDC is failing due to an Implicit Conversion. The problem is that I can't find the exact statement that is doing the conversion.

I've tried using Pinal Dave's script (https://blog.sqlauthority.com/2017/01/29/find-queries-implict-conversion-sql-server-interview-question-week-107/) but none of the conversions from that list match the time and types that are in the CDC error.

Does anyone have more information on how I can resolve this?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Rodney Ellis
  • 727
  • 2
  • 13
  • 28
  • Please add relevant code to your question and an error message, if any. – Reed Mar 21 '19 at 04:50
  • I can't add too much as I'm bound by confidentiality agreements. I can say this however: SELECT * FROM sys.dm_cdc_errors Produces these two rows -- Implicit conversion from data type varchar to binary is not allowed .... -- Failed to insert rows into Change Data Capture change tables. Refer to previous errors in the current session to identify. Problem is I can't find the query which is attempting the implicit conversion – Rodney Ellis Mar 21 '19 at 05:08
  • Is it possible to start an extended events session for that would to capture the statement? – Ben Thul Mar 21 '19 at 10:17
  • @RodneyEllis Do you obtain records for this captured instance `sys.sp_cdc_get_ddl_history [your_captured_instance_name]` or querying `[cdc].[ddl_history]` ? – SNR Mar 21 '19 at 11:13
  • @snr - am getting the errors from : SELECT * FROM sys.dm_cdc_errors; – Rodney Ellis Mar 22 '19 at 00:41
  • @BenThul - Am trying to set that up now, but am unsure of the events and fields to track. are you able to point me in the right direction? – Rodney Ellis Mar 22 '19 at 00:43
  • 1
    I've used the session (or variations on it) listed here to pretty good success: https://www.brentozar.com/archive/2013/08/what-queries-are-failing-in-my-sql-server/ – Ben Thul Mar 22 '19 at 00:59
  • cheers @BenThul that's a great article by Kendra. I was able to get this from the xEvent: [ sql_text - sys.sp_MScdc_capture_job] I was hoping for more though. Am still at a loss :'( – Rodney Ellis Mar 22 '19 at 03:47
  • Ugh... unfortunate. Also captured is the tsql stack. Are you able to use those handles to find anything? – Ben Thul Mar 22 '19 at 03:57
  • nothing that I can make sense of @benthul ... 4 lines of this: – Rodney Ellis Mar 22 '19 at 04:28
  • 1
    Sure. Those handles are (iirc) plan handles and you should be able to query sys.dm_exec_cached_plans for them. Alternatively (and perhaps in addition to), you can turn on event correlation on in your session and add something like sql_statement as an event. A word of caution though - make sure to add some sort of filter on the whole session so it doesn't collect *lots* of data. Filtering on something like the server_principal (i.e. the login) should work. – Ben Thul Mar 22 '19 at 19:12
  • Firstly thanks @benthul for your awesome help on this. really appreciated. I've been trying to get the cached plan, but according to this link, inserts aren't cached (the original poster got into a little spat with Johnathan Kehayias) - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cb597539-ea44-4bf3-863d-3a93f2c6d34c/event-session-does-not-pick-up-insert-statement?forum=sqldatabaseengine I'll try your second suggestion there of turning on event correlation. – Rodney Ellis Mar 27 '19 at 00:58

0 Answers0