CODEWITHSUNDEEP
sqlsql-servert-sql
Allan F
Allan F

Reputation: 2288

Debugging SQL Server SQL

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.

Upvotes: 1

Views: 105

Answers (1)

gotqn
gotqn

Reputation: 43636

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;

Upvotes: 1

Related Questions