none
Conversion failed when converting the varchar value '0000B8' to data type INT.

    Question

  • I have a query which I am pasting into the Dataset query in an SSRS report in BIDS.  It runs fine in SSMS.

    However each time I run it in the DataSet query window, I get the following error.  Conversion failed when converting the varchar value '0000B8' to data type INT. (MS SQL Server, Error 245)  However, the only '0000B8' is in a char(6) field which is being joined to another char(6) field in another table.  While it is true that most of the values in those fields are strictly numeric, at no point was this field in either table set to be an int, nor was there any intent to do so because 1. all six places are required even when it starts off with four leading zeros like this one, and 2.  There are enough values in that field that cannot be represented as an int to make that impossible.  There are no varchar fields or INT fields in the query.  In addition, the only row with 0000B8 is excluded from the query by the where clause.

    So my question is why the dataset query engine trying to convert these values to Int when there is neither a need or a desire to do this?


    Edward R. Joell MCSD MCDBA

    Tuesday, August 20, 2013 9:03 PM

Answers

  • I took the query and pasted it into a stored procedure and had the SSRS Dataset call the stored procedure.  Now when I run the query from the dataset query window the data is returned without a problem. 

    This is a workaround not an answer.  However, I am still going to close this question to reduce the number of open questions.  However I will still continue to monitor this question in case someone figures out how this is possible.


    Edward R. Joell MCSD MCDBA

    • Marked as answer by joeller Thursday, August 22, 2013 7:59 PM
    Thursday, August 22, 2013 7:59 PM

All replies

  • Is this error from SQL Server, thus have you tested to run the query stand-alone int SSMS?

    Wednesday, August 21, 2013 6:55 AM
  • As stated above, it runs fine in SSMS. Error only occurs when trying to run query in SSRS dataset query window.


    Edward R. Joell MCSD MCDBA

    Wednesday, August 21, 2013 3:56 PM
  • I took the query and pasted it into a stored procedure and had the SSRS Dataset call the stored procedure.  Now when I run the query from the dataset query window the data is returned without a problem. 

    This is a workaround not an answer.  However, I am still going to close this question to reduce the number of open questions.  However I will still continue to monitor this question in case someone figures out how this is possible.


    Edward R. Joell MCSD MCDBA

    • Marked as answer by joeller Thursday, August 22, 2013 7:59 PM
    Thursday, August 22, 2013 7:59 PM