2

I have set up sp_BlitzFirst to run as a job (for the PowerBI Dashboard). Occasionally, when it runs, it gets the following error:

Msg 8152, Sev 16, State 13, Line 2980 : String or binary data would be truncated. [SQLSTATE 22001]

This is actually happening in the sp_BlitzCache procedure at this line:

INSERT #stored_proc_info ( SPID, SqlHandle, QueryHash, variable_name, variable_datatype, compile_time_value, proc_name )
SELECT vi.SPID, vi.SqlHandle, vi.QueryHash, vi.variable_name, vi.variable_datatype, vi.compile_time_value, vi.proc_name
FROM #variable_info AS vi
OPTION ( RECOMPILE );

How can I resolve this? Thanks.

Darrell

Darrell
  • 125
  • 3
  • 10

1 Answers1

0

You'll get that error when one of the columns in the select won't fit into the destination table.

Tracking it down can take some time, but it's just like kindergaten - try and fit the shapes in the holes.

If you compare #Variable_info to #stored_proc_info, you will probably find that one of the columns in #variable_info is larger than its corresponding column in #stored_proc_info.

I can't find #variable_info in my version of the sp_blitzcache, so I can't tell which columns don't match.

Often, I track it down by running a query like this:

SELECT len(vi.SPID) as spid, len(vi.SqlHandle) as handle, len(vi.QueryHash as hash), len(vi.variable_name) as variable_name, 
len(vi.variable_datatype) as data_type, len(vi.compile_time_value) as compile_time, len(vi.proc_name) as proc_name
FROM #variable_info AS vi;

and looking for values that are too big.

James
  • 2,668
  • 5
  • 28
  • 51