Odd SQL conversion error "August 2012"

  • This is a really odd issue!!

    A parameterised SP runs fine on my test database wth a certain set of input values.

    On the customers's production database with the same input values this error occurs: "Conversion failed when converting the nvarchar value 'August 2012' to data type int.'"

    The value "August 2012" is NOT in any data used within the SP.

    Within the SP there is this code:

    SELECT Job_Bag

    FROM Job_Bag

    WHERE Job_Bag.Description_Reference = @OrderID and

    Job_Bag.Cust_Order_No = @OrderReference

    The Job_Bag.Description_Reference column is nVarchar(32)

    The passed in paramter values are:

    @OrderID is Int = 3562

    @OrderReference is varchar(50) = ''

    My understanding is that SQL will convert the @Order int value into the Job_Bag.Description_Reference field. Indeed so far this has always worked in test and production databases.

    Now the odd thing is this. If you change the code to explicitly convert:

    SELECT Job_Bag

    FROM Job_Bag

    WHERE Job_Bag.Description_Reference = CONVERT(nvarchar(30),@OrderID) and

    Job_Bag.Cust_Order_No = @OrderReference

    then the SP runs OK on the production database with the input values that earlier failed

    I really cannot think of an explantion for this.

  • Andrew-495157 (9/7/2012)


    This is a really odd issue!!

    A parameterised SP runs fine on my test database wth a certain set of input values.

    On the customers's production database with the same input values this error occurs: "Conversion failed when converting the nvarchar value 'August 2012' to data type int.'"

    The value "August 2012" is NOT in any data used within the SP.

    Within the SP there is this code:

    SELECT Job_Bag

    FROM Job_Bag

    WHERE Job_Bag.Description_Reference = @OrderID and

    Job_Bag.Cust_Order_No = @OrderReference

    The Job_Bag.Description_Reference column is nVarchar(32)

    The passed in paramter values are:

    @OrderID is Int = 3562

    @OrderReference is varchar(50) = ''

    My understanding is that SQL will convert the @Order int value into the Job_Bag.Description_Reference field. Indeed so far this has always worked in test and production databases.

    Now the odd thing is this. If you change the code to explicitly convert:

    SELECT Job_Bag

    FROM Job_Bag

    WHERE Job_Bag.Description_Reference = CONVERT(nvarchar(30),@OrderID) and

    Job_Bag.Cust_Order_No = @OrderReference

    then the SP runs OK on the production database with the input values that earlier failed

    I really cannot think of an explantion for this.

    What values exist in Job_Bag.Description_Reference in the production database?

  • Description_reference contains 2 rows with the value August 2012

    ........ ???

  • Andrew-495157 (9/7/2012)


    Description_reference contains 2 rows with the value August 2012

    ........ ???

    Now look at the original code:

    WHERE Job_Bag.Description_Reference = @OrderID -- SQL was trying to conver August 2012 to an integer for this comparision.

  • I had thought that SQL coerced the @OrderID int value to a varchar to do the comparison

    You are suggesting that SQL does the coercion the other way - coerces all column value to an Int to compare to @OrderID?

  • Andrew-495157 (9/7/2012)


    I had thought that SQL coerced the @OrderID int value to a varchar to do the comparison

    You are suggesting that SQL does the coercion the other way - coerces all column value to an Int to compare to @OrderID?

    Here is the error message, "Conversion failed when converting the nvarchar value 'August 2012' to data type int.'".

    SQL Server was attempting an implicit conversion from nvarchar to int to accomplish the comparision.

  • Yes - I understand conversion and casting

    I wonder if you would re-read my previous post? To confirm what is in it.

  • Andrew-495157 (9/7/2012)


    Yes - I understand conversion and casting

    I wonder if you would re-read my previous post? To confirm what is in it.

    What is to re-read? The conversion by SQL Server was from nvarchar to int, that's why you got the error.

    There is a hierarchy to the datatypes which SQL Server uses when doing implicit conversions. Unfortunately I am having problems with my google-fu in trying to find the appropriate reference in Books Online to provide that information to you.

  • OK

    When SQL compares column values with a variable value where the values are diferent data types.

    There are 2 posibel methods:

    1 Coerce each column value as it is read row by row from the table and compare to the variable

    2 Coerce the variable value, cahe the coerced value. Compare tow by row the column value with the cached coerced value

    The most efficient method would appear to be 2 as the coercion is only done once.

    ALSO

    My test database has always had a column with the value 'AB' - and yet the SP has never failed before.

    There is clearly something odd going on here. MY suspicion is that it to do with how SQL optimises qurey plans

  • I think is the one Lynn was thinking of.

    http://msdn.microsoft.com/en-us/library/ms190309.aspx

    Implicit conversions, which is what you have when datatypes don't match (like int and varchar) will always attempt to use the higher datatype in precedence. Look at the list and you will notice that int is much than varchar. This is one of the reasons you should not allow implicit conversions. If you want to compare to a varchar value you should make your parameter's datatype match.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Lynn Pettis (9/7/2012)


    Andrew-495157 (9/7/2012)


    Yes - I understand conversion and casting

    I wonder if you would re-read my previous post? To confirm what is in it.

    What is to re-read? The conversion by SQL Server was from nvarchar to int, that's why you got the error.

    There is a hierarchy to the datatypes which SQL Server uses when doing implicit conversions. Unfortunately I am having problems with my google-fu in trying to find the appropriate reference in Books Online to provide that information to you.

    To help Lynn with the explanation, here's the reference to BOL http://msdn.microsoft.com/en-us/library/ms190309.aspx

    Read carefully as it might be confusing (highest precedence is lowest value).

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Andrew-495157 (9/7/2012)


    OK

    When SQL compares column values with a variable value where the values are diferent data types.

    There are 2 posibel methods:

    1 Coerce each column value as it is read row by row from the table and compare to the variable

    2 Coerce the variable value, cahe the coerced value. Compare tow by row the column value with the cached coerced value

    The most efficient method would appear to be 2 as the coercion is only done once.

    ALSO

    My test database has always had a column with the value 'AB' - and yet the SP has never failed before.

    There is clearly something odd going on here. MY suspicion is that it to do with how SQL optimises qurey plans

    It is definitely not anything with plans. It is all about implicit conversions. Your explanation itself should give you a pretty good reason to NOT use implicit conversions. In both of your possible methods you said the dreaded 4 letter word (RBAR) or row by row. Not only do implicit conversions cause strange behavior they will also render your query nonSARGable. The fact that it worked in your test database is a bit odd.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thanks to all.

    BUT - the issue is not conversion per se. I don't need any more info on coercion, casting, conversion etc!

    The issue now is - why the code has previously worked with 'AB' as a column value.

    Since the code DID work then clearly conversion in the SELECT statement has NOT been working in the way you all suggest.

    One thing that has changed is that there are more rows in the table.

    So my best guess is - as I said - that the query optimsization plan has changed

  • Andrew-495157 (9/7/2012)


    Thanks to all.

    BUT - the issue is not conversion per se. I don't need any more info on coercion, casting, conversion etc!

    The issue now is - why the code has previously worked with 'AB' as a column value.

    Since the code DID work then clearly conversion in the SELECT statement has NOT been working in the way you all suggest.

    One thing that has changed is that there are more rows in the table.

    So my best guess is - as I said - that the query optimsization plan has changed

    It is not the execution plan. It is the implicit conversion. Without ddl, sample data and parameters there is no way anybody can do anything to help.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • @SSCertifiable

    There is a consistency within SQL issue here which I don't understand

    Why did the conversion NEVER fail with the value 'AB' is in the column?

    This value has been there since the table was populated - it was part of some test data

    The value 'August 2012' was inserted recently in a new row - and the conversion promptly failed when the SP was next run

    I would appreciate your explantion of this?

    PS Row-by-row for clarity of explanation, not as a mechanism.

Viewing 15 posts - 1 through 15 (of 47 total)

You must be logged in to reply to this topic. Login to reply