September 8, 2012 at 4:49 pm
Did ask for a description, I ask for the results of a specific query that would return 2 columns and an unknown number of rows (possibly 2).
Copy paste normally works just fine from the result pane.
September 8, 2012 at 5:14 pm
Have we tried explicit conversion and see if it works?
September 8, 2012 at 7:36 pm
vijayinder (9/8/2012)
Have we tried explicit conversion and see if it works?
Yes, if you read the first post it says taht.
September 8, 2012 at 7:36 pm
Lynn Pettis (9/8/2012)
Didn't ask for a description, I ask for the results of a specific query that would return 2 columns and an unknown number of rows (possibly 2).Copy paste normally works just fine from the result pane.
September 8, 2012 at 7:38 pm
Andrew-495157 (9/8/2012)
I don't understand.I described EXACTLY the results.
Not sure how else to post them?
I
And actually, you didn't describe exactly the results of the query I asked you to run. You simply said it returned 'August 2012' and 'AB'. That is not what the query would have returned. Take a look at it again.
September 9, 2012 at 2:46 am
AB70255
August 2012
Query results using copy paste from SQL SMS results pane
September 9, 2012 at 3:01 am
SELECT Job_Bag
FROM Job_Bag
WHERE
Job_Bag.Description_Reference = 1609
AND Job_Bag.Cust_Order_No = '70255'
* A row exists which matches the WHERE cluase conditions
* The row with value 'AB' in column Description_Reference has value '70255' column in Cust_Order_No
* Running this code in SQL server gives error "Conversion failed when converting the nvarchar value 'AB' to data type int."
Just ignoring SQL Server for a moment and thinking purely in RBD terms. In one sense this error should not occur:
* I asked for every row matching the WHERE clause
* The row with 'AB' is irrelevant to my request
The error only occurs because of the specific way SQL Server implements querying.
* For example if during the query SQL converted numeric 1609 to string '1609' then the rows I asked for would have been returned
September 9, 2012 at 11:11 am
Andrew-495157 (9/9/2012)
SELECT Job_BagFROM Job_Bag
WHERE
Job_Bag.Description_Reference = 1609
AND Job_Bag.Cust_Order_No = '70255'
* A row exists which matches the WHERE cluase conditions
* The row with value 'AB' in column Description_Reference has value '70255' column in Cust_Order_No
* Running this code in SQL server gives error "Conversion failed when converting the nvarchar value 'AB' to data type int."
Just ignoring SQL Server for a moment and thinking purely in RBD terms. In one sense this error should not occur:
* I asked for every row matching the WHERE clause
* The row with 'AB' is irrelevant to my request
The error only occurs because of the specific way SQL Server implements querying.
* For example if during the query SQL converted numeric 1609 to string '1609' then the rows I asked for would have been returned
I am confused.
If the table Job_Bag contains a row where the column Cust_Order_Number = '70255' and Description_Reference = 'AB", and your WHERE clause is:
WHERE
Job_Bag.Description_Reference = 1609
AND Job_Bag.Cust_Order_No = '70255'
How is SQL Server NOT supposed to do the comparision of 'AB' = 1609? It HAS TO in order to determine if this row satisifies the condition specified. This means it HAS TO do an implicit conversion of the nvarchar value 'AB' to INT in order to complete the test and fails since 'AB' can not be converted to an integer value.
Going back to your original post, you specified the values being passed in at the time of the failure:
The passed in paramter values are:
@OrderID is Int = 3562
@OrderReference is varchar(50) = ''
Based on the results of the query I had you run, 'August 2012' was returned in Job_Bag.Description_Reference when the value of Job_Bag.Cust_Order_No = '' (the empty string).
Again, when SQL Server found a row where Cust_Order_No = '' (the empty string), it had to complete an implicit conversion of the value 'August 2012' to an integer to compare it the value 3562, and it failed since 'August 2012' could not be converted to an integer value.
September 9, 2012 at 11:17 am
And just so you know, you would have this same issue if the database was Oracle as it uses a similar data type precedence as SQL Server when doing implicit conversions as is (was) used in the code you posted.
September 9, 2012 at 3:45 pm
Lynn wrote "This means it HAS TO do an implicit conversion of the nvarchar value 'AB' to INT in order to complete the test and fails since 'AB' can not be converted to an integer value."
Or it could convert the INT to nvarchar.
Or it could ignore the conversion error since, if there is an error, clearly the WHERE clause condition has not been met and so the row can safely be ignored.
The point is that in pure RDB terms SQL has failed to return the results I wanted.
It fails only because of the way SQL is implemented.
Implementation should make no difference (except in efficiency) to the results returned using RDB theory.
September 9, 2012 at 5:05 pm
Andrew-495157 (9/9/2012)
Lynn wrote "This means it HAS TO do an implicit conversion of the nvarchar value 'AB' to INT in order to complete the test and fails since 'AB' can not be converted to an integer value."Or it could convert the INT to nvarchar.
Or it could ignore the conversion error since, if there is an error, clearly the WHERE clause condition has not been met and so the row can safely be ignored.
The point is that in pure RDB terms SQL has failed to return the results I wanted.
It fails only because of the way SQL is implemented.
Implementation should make no difference (except in efficiency) to the results returned using RDB theory.
You really don't get it, do you. The problem was the code being written to RELY on implicit data conversion to compare the value of a column defined as NVARCHAR to a varable defined as an INTEGER. SQL Server and Oracle would BOTH complete this implicit conversion by attempting to convert the character value to integer.
You are sitting there trying to blame SQL for the failure of the author of the code understanding what would happen when relying on implicit conversion.
September 10, 2012 at 2:24 am
Lynn wrote "You really don't get it, do you"
In the UK this would be considered a provocative and rude statement. I will assume cultural differences apply here.
Having used RBDMS since 1984, and having an MSc in Computing, I am prepared to assert that "actually,yes, I do get it".
Lynn wrote "trying to blame SQL for the failure of the author"
Again provocative - and again I have to assume a cultural difference here.
In the professional world "Blame" is a useless concept. Finding out what has happened, understanding it, and taking steps to prevent re-occurence are useful.
==========================================================================================================
The error occurred in a very old system, which itself had been migrated from a legacy RDBMS. In the original system the validation of data entry meant that the nVarchar column could only possibly have Int (as strings) values. The system had been enhanced and at some point the business model needed to allow non-numeric text to be stored in the column using another data-entry system. There is almost no documentation of the system.
As usual a series of errors going back a long way lead to the defect showing up after years of untroubled use. I don't think anyone now knows the "who did what, when, or why". The important point is that we all learnt things - about the business domain, actual use of the system, SQL etc.
SQL Server is itself, of course, a very old system extended from legacy systems over decades, and does not fully implement the RDB model (as Lynn correctly points out neither does Oracle. Nor in fact does any mainstream commercial RDBMS). SQL is riddled with inconsistencies and quirks, as is SQL server. The history of SQL and the development of RDBMS since 1971 is very interesting - both technically and commercially. It is however a deeply flawed product. It is interesting to see Microsoft gradually re-engineering SQL (essentially towards .Net) to make it a more modern and less flawed product.
======================================================================================================
I don't intend to post any more on this topic myself as I believe we now fully understand the issues and know what steps to take.
Thanks for all who contributed. Live long and prosper
September 10, 2012 at 6:42 am
Andrew-495157 (9/10/2012)
Lynn wrote "You really don't get it, do you"In the UK this would be considered a provocative and rude statement. I will assume cultural differences apply here.
Having used RBDMS since 1984, and having an MSc in Computing, I am prepared to assert that "actually,yes, I do get it".
Lynn wrote "trying to blame SQL for the failure of the author"
Again provocative - and again I have to assume a cultural difference here.
In the professional world "Blame" is a useless concept. Finding out what has happened, understanding it, and taking steps to prevent re-occurence are useful.
==========================================================================================================
The error occurred in a very old system, which itself had been migrated from a legacy RDBMS. In the original system the validation of data entry meant that the nVarchar column could only possibly have Int (as strings) values. The system had been enhanced and at some point the business model needed to allow non-numeric text to be stored in the column using another data-entry system. There is almost no documentation of the system.
As usual a series of errors going back a long way lead to the defect showing up after years of untroubled use. I don't think anyone now knows the "who did what, when, or why". The important point is that we all learnt things - about the business domain, actual use of the system, SQL etc.
SQL Server is itself, of course, a very old system extended from legacy systems over decades, and does not fully implement the RDB model (as Lynn correctly points out neither does Oracle. Nor in fact does any mainstream commercial RDBMS). SQL is riddled with inconsistencies and quirks, as is SQL server. The history of SQL and the development of RDBMS since 1971 is very interesting - both technically and commercially. It is however a deeply flawed product. It is interesting to see Microsoft gradually re-engineering SQL (essentially towards .Net) to make it a more modern and less flawed product.
======================================================================================================
I don't intend to post any more on this topic myself as I believe we now fully understand the issues and know what steps to take.
Thanks for all who contributed. Live long and prosper
Lynn wrote "You really don't get it, do you"
In the UK this would be considered a provocative and rude statement. I will assume cultural differences apply here.
This was not meant to be provocative or rude, but was simply frustration. I probably would have said the same thing in person.
What I have seen, my perception of what you have written, is a lack of understanding of the rules of implicit data conversion that have been around since I started working with MS SQL Server over 15 years ago with SQL Server 6.5. I am sure that these same rules were in place even longer than that. Theory is great, but it doesn't always translate into practical, and the companies that have written RDBM systems had to make design decisions. And luckily they made similar decisions. Could you imagine how difficult it would be to write code for different systems if the rules were different? If one RDBMS implicitly converted integers to strings when another converted strings to integers? Not only that, the rules as implemented actually make logical sense to me because they are simple and straight forward with little decision-making required which speeds up the work that needs to be done by the Optimizer and Storage Engine. Yes, you make get errors such as this, but again, this comes back to reliance on implicit data conversion and understanding how it is handled.
Lynn wrote "trying to blame SQL for the failure of the author"
Again provocative - and again I have to assume a cultural difference here.
Again, I would have said this in person, not be to provocative, but to point out that the error isn't in SQL Server but in the lack of understanding how implicit data conversion works. If you are going to compare two disparate data types, you should not rely on implicit data conversion but should explicitly handle the conversion. In this case, the integer value should be converted to a string value before the comparision is done.
September 10, 2012 at 7:23 am
Andrew-495157 (9/7/2012)
" There are also implicit conversions in .net"Assuming you don't mean VB.net Option Explicit, or the Implicit keyword.
And ingoring the VAR debate.....
Can you give some examples of implicit conversions in .Net?
Sorry I have been away for the weekend.
From a standard ASP.NET webform.
int x = 4;
Session["ImplicitConversion"] = x;
string ConversionTest = "x = " + Session["ImplicitConversion"];
The above will implicitly convert the session value to a string because you are concatenating two strings. The code never calls the ToString method. .NET made the decision that based on how it is being used the string value is what is desired. You can do the same thing with a more complex object. It will use the results of ToString.
If you changed the second line to:
string ConversionTest = Session["ImplicitConversion"];
You would get a compile error "Cannot implicitly convert type 'object' to 'string'. An explicit conversion exists (are you missing a cast?)".
To sum up my take on your frustration it seems that you want sql to throw an error when there is a datatype mismatch instead of trying to implicitly cast to the correct datatype. From your programming background I can understand that. It does certainly cause issues from time to time like you have experienced in this case. It would however cause way more issues because in t-sql we need to do things like specify length for character data and precision for numerics. In the database world a varchar(10) is different than a varchar(11). In other words there has to be some level of implicit conversion due to the flexibility in datatypes. One could argue that simple varchar issues of size can be handled implicitly. Few would argue that point. But at what point should the engine decide to stop implicitly converting and throw an exception? Who makes that rule? This is why implicit conversion happens in sql server and nearly every other RDBMS that I know of.
_______________________________________________________________
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 10, 2012 at 8:08 am
hehe I hadn't looked at today's question of the day. Seems to be in conjunction with this post. Same issue exactly. We should all three get this one correct with no problem. 😉
_______________________________________________________________
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/
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply