June 14, 2012 at 1:25 pm
We have a table of sales tax values which are imported from time to time. When I try a select statement, "Select City From SalesTax where zip = '53708'", I receive the following error.
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'DEL09 ' to a column of data type int.
(The answer to the query is Madison)
The table contains two BigInt Fields. All the other fields are either decimal or character. The first BigInt field is the autoincrementing field, IDNo. The second is a field that stores a date value (the program that this must integrate with stores its dates as long numbers).
What is going on? And what are possible solutions?
My assesment is that one of the two BigInt fields somehow contains character data. Since the query only runs part way, 3 out of many more records are actually returned, is this likely to be unique identifier field, the IDNo field?
Short of truncating the table and doing the latest import over again, what are my options? Can I determine if there are more bad field values?
Thanks,
pat
June 14, 2012 at 1:52 pm
The only columns that query will access are the City and Zip columns, so they're the only columns that can trigger the data type error. While some data types can have illegal values in (numeric, float, nvarchar, nchar), an integer (or bigint) column cannot contain the value DEL09, for a number of reasons.
A bigint column can certainly be represented with the binary value '0100010001000100010011000011000000111001' (which is how DEL09 translates), it would actually mean 293,203,619,897 (if my rough calculations are correct)
Was the query you ran this:
Select City From SalesTax where zip = '53708'
or this:
Select City From SalesTax where zip = 53708
The small distinction is important.
Also, sales tax is a table? Not a view?
Is this a SQL 2000 instance?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2012 at 5:10 pm
GilaMonster,
Thank you for your reply. There is a surprise at the end of my reply so please bear with me.
I tried several queries. I stated with Select * from Salestax where Zip = '53708'. That was the first time I received the error. Thinking that it was related to one of the two BigInt fields because of the wording of the error message, I crafted a query that did not include either of the BigInt fields, Select City from SalesTax where zip = '53708'. This query gave the exact same error. At this point I gave up and posted here.
The SQL version is SQL 2000. I was going to edit my post when I returned to the office this evening but your reply beat me to it.
I have attached the Create table statement to show field types.
I tried running the query in Query Analyzer and in Enterpise manager. The error was the same in both programs (although it was displayed as a Dialog box in EM and as text in the query results pane in QA).
OK. Here is the surprise. Just now while I was verifying which query I used, I reran the query and did not receive the error. I tried all sorts of different queries against this database and could not make the error reappear.
Very strange indeed. Thank you for your help. Please feel free to comment. But it appears that a transient error may have caused me to waste your time and I do aplogize for that.
Pat
June 15, 2012 at 3:16 am
Strange.
Would love to debug further, but that's kinda hard without a broken query.
The reason I asked about the quotes is because the of implicit conversion. If the query were expressed as Select City From SalesTax where zip = 53708, that would actually mean Select City From SalesTax where cast(zip as int) = 53708 and would fail if there's any non-integer values in the zip column, which there may well be.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply