Tuesday, February 28, 2012

Character in CAST argument not valid.

Tough day today, I quickly introduced myself to SQL error codes. I have all the code written and  theoretically should work. Unfortunately the fetch procedure fails with the following error.

Message ID . . . . . . :   SQL0420    Severity . . . . . . . :   30       
Message type . . . . . :   Diagnostic                                        
Date sent  . . . . . . :   02/28/12   Time sent  . . . . . . :   14:37:30
Message . . . . :   Character in CAST argument not valid.                    
Cause . . . . . :   A character in the argument for the CAST function was not
Recovery  . . . :   Change the result data type to one that recognizes the   
  characters in the CAST argument, or change the argument to contain a valid
  representation of a value for the result data type. Try the request again.

I learned that there is a slew of error codes that can be monitored for. Also received a great link from a previous post. I was aware of the error codes and the need to use them but never had to really figure it out before. Working with debug I was able to monitor SQLSTT variable and found several other issues that were easy to fix. I got a good look at how SQL error codes are generated.  

But now I am stuck, I can run the statement in SQL Explorer and interactive SQL green screen and get the desired results. I’ll post the code in the morning, I spent a couple of hours debugging. I am sure it will turn out to be something silly in the morning.

I going to read my Game of Thrones book for a while and get a fresh start in the morning.



  1. Hi, I know this is a long shot seeing as this post was so long ago but I am having this exaclt problem and am looking for a solution. Sounds like I am going through the same pain you went through back in 2012 with embedding SQL! Can you remember what the cause of this problem was and what the solution was?

    Best regards,

    1. Mark, Sorry I missed this. I believe it was a character verses numeric issue. Did you resolve your issue?