Thursday, February 17, 2005 8:11 PM
by
RepeatableRead
Fun with Query Analyzer (and goofy ASCII characters)
This one caused me a brief head-scratch the other day. A coworker came and told me he had a corrupted table. Initially that seemed plausible.. it was a huge table/database with a fair amount of activity. But when I was told that the reason for THINKING the table was corrupt was due to the fact that data from one column was spilling into the next, I was skeptical. Corrupt table yes, but data spilling from one row to the next, highly unlikely.
I ran DBCC CHECKTABLE. Everything reported back as spiffy.
Then I saw the results of my coworker's query that caused the need to question the table's integrity. A single char(2) column was spilling into a DATETIME column, but only on some rows. Knowing that the data was being imported from an old AS/400 application (in cobol... with it's funky 'chunky data types' which are just position delimited 'columns'), I grew a bit skeptical of the actual data itself, not how it was stored in the table.
Sure enough SELECT ASCII(badColumn) FROM questionableTable WHERE rowID = evilRowExample showed that the char value in that char(2) column was dead wrong. Newlines and other goodies were occasionaly creeping into this column, and massacring the grid in Query Analyzer.
Here's a sample query below to see it in action. Try it out, it's a hoot! (And try to remember this incase you ever see it in the wild).
SELECT
col1 = 'col1',
col2 = CAST(0x0196 as char(2)),
col3 = 'col3',
datecol = GETDATE()
UNION SELECT
col1 = 'col1',
col2 = 'col2',
col3 = 'col3',
datecol = GETDATE()