September 7, 2012 at 9:34 am
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.
September 7, 2012 at 9:38 am
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?
September 7, 2012 at 10:20 am
Description_reference contains 2 rows with the value August 2012
........ ???
September 7, 2012 at 10:25 am
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.
September 7, 2012 at 10:29 am
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?
September 7, 2012 at 10:36 am
Andrew-495157 (9/7/2012)
I had thought that SQL coerced the @OrderID int value to a varchar to do the comparisonYou 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.
September 7, 2012 at 10:39 am
Yes - I understand conversion and casting
I wonder if you would re-read my previous post? To confirm what is in it.
September 7, 2012 at 10:45 am
Andrew-495157 (9/7/2012)
Yes - I understand conversion and castingI 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.
September 7, 2012 at 10:52 am
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
September 7, 2012 at 10:53 am
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/
September 7, 2012 at 10:54 am
Lynn Pettis (9/7/2012)
Andrew-495157 (9/7/2012)
Yes - I understand conversion and castingI 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).
September 7, 2012 at 10:59 am
Andrew-495157 (9/7/2012)
OKWhen 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/
September 7, 2012 at 11:05 am
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
September 7, 2012 at 11:12 am
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/
September 7, 2012 at 11:19 am
@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