If you tried to post a comment on my previous post, you may have noticed an impressive error message. Sorry about that. It was the bug I was trying to fix. And I did, sort of. It shouldn’t get any weirder than this:
I noticed that some of my entries still suffered from the “Empty update, no affected rows”-syndrome, just some. I thought about other offending characters, such as the [ character that caused all the previous pain. But I couldn’t get it pinpointed. As it turned out, this was my problem, and you can reproduce it easily. Hold on, we’ll work to the Area52 bit.
First take any SQL Server database (say MyFunDB) that you can play around with. Then fire up SQL Query Analyzer (isqlw.exe) and run this bit of SQL.
CREATE TABLE WeirdStuff(Body ntext)
INSERT INTO WeirdStuff(Body) VALUES (‘234567890’)
SELECT COUNT(*) FROM WeirdStuff WHERE Body LIKE (‘234567890’)
Run it and it should return zero as the row count. That’s because of the [ character. Replacing the last line with:
SELECT COUNT(*) FROM WeirdStuff WHERE Body LIKE (‘[1]234567890’)
will return 1, because we have escaped the escape character. We’re approaching the freaky things now. Make both the inserted value and the LIKE value longer and longer, by copy-pasting sets of characters (I used 1234567890, so I had an easy count). Once you pass the magic length of 4000 for the LIKE value, it BREAKS! Zero rows after that. 4000 characters, returns 1, 4001 characters returns 0. Go figure. Remove the [ chars from both values and it works again. So, it’s only broken with escape characters and a length above 4000.
That is the reason for ‘my’ bug. Not really my bug, but my application suffers from it anyway. So don’t try for too long to post any comments, until I have this thing worked out. I do not seem to have passed the magic boundary, so comments should work.