Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Odd SQL conversion error "August 2012" Expand / Collapse
Author
Message
Posted Friday, September 7, 2012 9:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 10, 2012 3:53 PM
Points: 41, Visits: 115
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.
Post #1356069
Posted Friday, September 7, 2012 9:38 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:50 PM
Points: 23,003, Visits: 31,495
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?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1356072
Posted Friday, September 7, 2012 10:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 10, 2012 3:53 PM
Points: 41, Visits: 115
Description_reference contains 2 rows with the value August 2012

........ ???
Post #1356093
Posted Friday, September 7, 2012 10:25 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:50 PM
Points: 23,003, Visits: 31,495
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1356098
Posted Friday, September 7, 2012 10:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 10, 2012 3:53 PM
Points: 41, Visits: 115
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?



Post #1356101
Posted Friday, September 7, 2012 10:36 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:50 PM
Points: 23,003, Visits: 31,495
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1356107
Posted Friday, September 7, 2012 10:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 10, 2012 3:53 PM
Points: 41, Visits: 115
Yes - I understand conversion and casting

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

Post #1356108
Posted Friday, September 7, 2012 10:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:50 PM
Points: 23,003, Visits: 31,495
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1356112
Posted Friday, September 7, 2012 10:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 10, 2012 3:53 PM
Points: 41, Visits: 115
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
Post #1356120
Posted Friday, September 7, 2012 10:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 13,069, Visits: 11,908
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1356121
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse