April 16, 2013 at 11:36 pm
I have been using a query that joins several raw tables (nvarchar datatypes), has several conversions, nested queries, CASE WHEN statements. In other words it is messy, but I didn't create it nor do I have time to recreate it properly. It has been generating a very strange conversion error (the cause not the error itself is strange) where there is some kind of datetime conversion error when converting a string datatype. I have debugged every single field from every table in the query that is either converted explicitly or implicitly (these have all been updated to explicit conversions), with no problems present.
The error is very strange because if I change the select fields (none of which are converting to a datetime implicitly or otherwise) to * the error message goes away, in fact if I simply add ,* after the last field in the select list it also goes away. However I can't use this because don't want every single field from every table in the query.
Further, if I change the Select statement to SELECT TOP XXX where XXX is greater than the number of rows returned the error still does not occur. But if I use SELECT TOP 100 PERCENT the error occurs again.
This made me think that it might be an error in the query execution cache. Which I cleared and tried again with the same errors still occurring.
The database is set up to use Korean_Wansung_CI_AS on SQL Server Enterprise Edition v10.50.1600.1
This problem is not critical as I can set it to use SELECT TOP XXX such that XXX is far larger than will ever be actually required. It was just very strange behavior that I had never seen before.
Unfortunately, I can't provide the code or data both are quite confidential, however I'm quite sure that this is not directly related to the code itself, as the code is working using the two scenarios mentioned.
Has anyone seen this behavior and/or know of the cause?
April 17, 2013 at 8:08 am
We all certainly understand sensitive data. Many of us work with it all day long. I can't however imagine that a query is sensitive. The problem here is that you have asked for help but won't provide the details. Short of knowing what the table looks like and the query that retrieves the data we have no chance of helping. We can't see your screen and we don't know the data. Anything anyone offered would be a complete guess.
_______________________________________________________________
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/
April 17, 2013 at 6:41 pm
Thanks Sean,
I completely understand and if no-one can help I don't blame them, this question is more of a curiosity for me as I can work around the issue and that is sufficient. Unfortunately my company considers code such as this as its IP for a product that they've developed. I understand that without the code it is almost impossible to debug, so perhaps it would have been better to try to understand why SELECT TOP XXX would be different from a SELECT without a "top" number of rows when XXX is greater then the number of rows that query produces. Or even why adding ",*" to the end of the fields being selected would change the behavior of the query to prevent the conversion error?
April 17, 2013 at 9:29 pm
I have a question, have you tried recreating the problem using a set of tables that resemble the tables used in the query (data types and column order) with a similar query joining the on columns of the same data types?
April 19, 2013 at 3:05 am
Well without knowing anything about the query, data or even the error message anything you get will be very general.
You will need to break down the query and understand in detail how it works and what part is failing.
I would start by identifying what record(s) and fields(s) is(are) causing the error.
If your query is using subqueries try running those in isolation - this could throw some light on why adding * works - are you trying to use a field hidden within a subquery but not returned from it?
Same applies for any unions or stored proc calls.
The TOP XXX would indicate that there is probably a few records causing problems so narrow that down - add additional clauses to help with this. If there is no sorting its probably recent as well.
April 19, 2013 at 3:52 am
The bazaar thing is that I've tried looking at the sub-queries, I've looked at every field referenced in every table, including looking at each and every implicit conversion. I would try to replicate the issue for you but suspect that it is some incredibly strange sequence of events that could not be replicated if I tried.
I suspect that the issue is somewhere SQL server is behaving in a way that it shouldn't, there is a conversion that uses a case when statement to exclude the values that would cause an error, if SQL server tried to convert this inside the case when then I would see this conversion error.
The TOP XXX rows shouldn't be relevant because the result set in the data used is under 1000 rows, if I set it at 1000 or more I get every single row without the error occurring.
Whilst I don't claim to be flawless in anyway, I'm almost certain that the issue stems from SQL Server processing something in the wrong order. When I say wrong order please don't assume me ignorant to the order that calculations are normally performed. I believe it must be either evaluating the JOIN criteria of one of the nested queries before evaluating the nested query properly, or evaluating the conversion of a field and applying the case when inside the conversion afterward.
For Example there is code that does this:
CONVERT(DATE, CASE WHEN [date_field_as_text] = '00000000' THEN NULL ELSE [date_field_as_text] END,112) as [DATE]
this occurs inside a nested query and is used to join to another table where the other table is correctly stored as a date. The join criteria is a combination of this date field and another string field where the string fields are certainly not date data types implicitly or otherwise. Further the date fields are correctly converted to dates
E.g.
ON a.ID = b.ID
And a.Date = b.Date
I can provide more information a bit later.
Sorry for not being more helpful.
April 19, 2013 at 4:01 am
Does using
TOP 100 PERCENT
work?
April 19, 2013 at 6:16 am
I am not going to blame the problem on a bug in SQL Server. I am more likely to say there is a logic/data error somewhere in the code.
My suggestion at this point is rewrite the query doing it in steps making sure each step works as you move to the next until you have rewritten the query. Doing this you may find what is causing the error.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply