Today I had a job fail, that has been running without errors for months. The error was “Error converting data type varchar to bigint. [SQLSTATE 42000]”.
I went into the procedure that failed and ran it in pieces. I found the error was coming from a merge (update/insert) statement. The data for this one column came in as char(18), but the column to insert or update was a bigint.
I needed to find which rows were causing the issues. I couldn’t use Try/Catch, because it would fail but not show me the data. Then I found TRY_CAST!!
With the next query I was able to find all the data that would fail in a cast.