Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Odd SQL conversion error "August 2012"


Odd SQL conversion error "August 2012"

Author
Message
Andrew-495157
Andrew-495157
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24217 Visits: 37978
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?

Cool
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)
Andrew-495157
Andrew-495157
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 115
Description_reference contains 2 rows with the value August 2012

........ ???
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24217 Visits: 37978
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.

Cool
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)
Andrew-495157
Andrew-495157
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
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?
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24217 Visits: 37978
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.

Cool
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)
Andrew-495157
Andrew-495157
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24217 Visits: 37978
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.

Cool
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)
Andrew-495157
Andrew-495157
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16616 Visits: 17024
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search