Debugging SQL Server SQL

Question

If I have a complex query with many dozen joins and I get this message, am wondering how to debug (?) i.e. no column name or line number is quoted in the error message:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'X' to data type smallint.

I guess I just keep reducing the query until it works again and then add back bit by bit until error occurs again.

This is an issue with data. You're doing a CAST() or CONVERT() that tries to turn a VARCHAR value to a SMALLINT, and the value in the VARCHAR either isn't numeric or the resulting value after the conversion is too large to fit in a SMALLINT. Debugging means figuring out which columns are undergoing that conversion and then looking at the data to figure out what causes it. You can narrow it down by restricting the number of rows that are being processed into smaller sets until you find the area of the data where it's happening. — Ken White
– Ken White, Commented Jun 13, 2019 at 2:46
Thanks Ken ! Your answer is spot on .. ... I tried changing my CONVERT function calls to TRY_CONVERT and issue went away. I will do a data profile job (excel and formulas and macros) on the data table to determine where the data issue is ! — Allan F
– Allan F, Commented Jun 13, 2019 at 2:59

gotqn · Accepted Answer · 2019-06-13 05:06:41Z

1

Usually, this can be easily debug as you found - using TRY_CONVERT or TRY_CAST.

Something like that:

SELECT *
FROM [dbo].[DataSource]
WHERE TRY_CONVERT(SMALLINT, [MyTextColumn]) IS NULL;

answered Jun 13, 2019 at 5:06

gotqn

44k47 gold badges167 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Debugging SQL Server SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related