Try_Convert and Try_Cast

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!!

Muhammad Imran “SQL SERVER 2012 -Conversion Function -TRY_CONVERT”

Muhammad Imran “SQL SERVER 2012 -Conversion Function -TRY_CAST”

 

casterror

With the next query I was able to find all the data that would fail in a cast.

try_castFail

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s