August 2, 2011 at 3:46 am
Tom.Thomson (8/1/2011)
Nakul Vachhrajani (8/1/2011)
David in .AU (7/31/2011)
This article might be interesting: note the reply from microsoft at the bottomReplace of char(0) does not work in DB with Windows collation
I know it isn't exactly the same issue, but it runs along the same lines...
Hello!
Thank-you very much for this link. It explains the issue very well.
It doesn't appear to me to explain it at all. "This doesn't work" is one thing, "Operating on the result of this causes a hang" is quite a different thing. Particularly since we observe a difference between Nvarchar(max) and varchar(max), and even with varchar(N) it doesn't hang unless N was max.
The explanation is ProgrammerSpeak. Whenever a programmer says "it doesn't work", he actually means "I never expected anyone would use this feature that way. I've absolutely no idea what will come out."
You will be surprised how often this situation occurs in real world. It is by far not limited to microsoft programmers 🙂
Joseph
August 2, 2011 at 5:49 am
Thomas Abraham (8/1/2011)
Just curious - what situations require replacement of CHAR(0)? How does it end up in your data? Thanks.
In my particular case, it was found in some Access data we were importing from our legacy application. The previous developers who worked on the Access app weren't very picky about what sort of things ended up in their database, and our new SQL-based app was processing this data in a manner that was sensitive to certain special characters. Char(0) caused a problem when we encountered it, so I added a REPLACE() to the cleanup section of the script.
It worked fine until one of those darn Canadians got their hands on it. 😉
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
August 2, 2011 at 6:00 am
I'm not sure we should be singling out Canadians here. If anything, the US situation is the exception, since SQL Server uses a SQL collation by default for the US, for backward-compatibility reasons. The rest of us, by and large, get a Windows collation on a default install.
I should add though that not all Windows collations suffer from this edge case:
SELECT REPLACE(CONVERT(VARCHAR(MAX), 'Hello') COLLATE Latin1_General_CI_AI, CHAR(0), SPACE(0))
SELECT REPLACE(CONVERT(VARCHAR(MAX), 'Hello') COLLATE Latin1_General_Bin, CHAR(0), SPACE(0))
SELECT REPLACE(CONVERT(VARCHAR(MAX), 'Hello') COLLATE SQL_Latin1_General_CP1_CI_AI, CHAR(0), SPACE(0))
In those examples, only the first one goes into an endless loop.
August 2, 2011 at 6:20 am
Well, yes, but the Windows collation that works is a binary collation--you'd expect that to be able to take NULLs in its stride, since it wouldn't be much use if it didn't! 🙂
August 2, 2011 at 6:25 am
paul.knibbs (8/2/2011)
Well, yes, but the Windows collation that works is a binary collation--you'd expect that to be able to take NULLs in its stride, since it wouldn't be much use if it didn't! 🙂
It's a fair point. I haven't tested all the Windows collations, so who knows which ones work and which don't. I'm not sure I'm convinced it's so obvious that BIN (and presumably BIN2) don't suffer from this issue though. After all the explanation hinged on the fact that Windows collations in general don't define CHAR(0). The other small point is that CHAR(0) isn't NULL; perhaps you meant to type NUL?
August 2, 2011 at 6:31 am
No, I typed NULL because I started life as a C programmer and some habits die *really* hard...
August 2, 2011 at 6:53 am
Thanks Michael
August 2, 2011 at 8:03 am
SQLkiwi (8/2/2011)
I'm not sure we should be singling out Canadians here. If anything, the US situation is the exception, since SQL Server uses a SQL collation by default for the US, for backward-compatibility reasons. The rest of us, by and large, get a Windows collation on a default install.
Thanks for that point, Paul. I hadn't realized this, being the comfortably provincial, geo-focused American that I am :ermm:
Someone posted a link here for the default collations, but that was for 2008. Here's the list for 2005 (which the QotD was about):
http://msdn.microsoft.com/en-us/library/ms143508%28v=SQL.90%29.aspx.
And, indeed, most collations listed are Windows -- not SQL -- collations.
Kudos to the o.p. for tracking down a hung Access process to this problem. I'd be curious what your trouble-shooting steps were? Did you see the identical process succeed in U.S. locations and decide there was something about Canada? Me, I would have looked for something specific to that Canadian server and missed the forest for the trees, that the problem was global to the SQL installation/collation.
Thanks for the question. The specific problem will almost certainly never be an issue for me, but the lesson in keeping an open mind during troubleshooting is valuable.
Rich
August 2, 2011 at 12:36 pm
Nice question. Have always used SQL_Latin1_General_CP1_CI_AS as we do not have any servers outside the US. Interesting stuff though!
August 2, 2011 at 3:54 pm
If you read in sql server books online
"SQL Server collations apply non-Unicode sorting rules to non-Unicode data, and Unicode sorting rules to Unicode data, by using a corresponding Windows collation for the Unicode data. This difference can cause inconsistent results for comparisons of the same characters. Therefore, if you have a mix of Unicode and non-Unicode columns in your database, they should all be defined by using Windows collations so that the same sorting rules are used across Unicode and non-Unicode data."
I do not know 100% as I do not work with collations often, but I think the difference between unicode and non-unicode in the scripts would make the difference because you are specifying non-unicode with varchar(max). Just my 2 cents though, that may not be correct 😀
I have noticed a few 'bugs' with using varchar(max) and nvarchar(max) causing errors, whereas using nvarchar(8000) removes the error. I have had this happen when using dynamic sql composition using exec sp_executesql @sql, @parmdef, @parm1 = @_parm1 etc. Using nvarchar(max) seemed to have had issues, but using nvarchar(4000) solved the problem which makes no sense to me still but there you go :).
Link to my blog http://notyelf.com/
August 2, 2011 at 11:47 pm
Excellent Question.
for testing i executed in my machine. with Australian settings it got hanged.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
August 3, 2011 at 4:30 am
Really a good question.
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
August 4, 2011 at 11:33 am
Excellant question.
There are a few buggy issues like this caused by Collation.
To reduce these types of issues we use the exact same collation on all of them.
😎
August 4, 2011 at 11:38 am
Several of you seem to agree that this is a bug that they can reproduce.
However the count of user that can re-create and users that think this is a bug on the connect site is LOW. 😛
https://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=125502#details
Here is the link again in case you missed it. Without your feedback, and more of it, M$ will most likely not fix these things.
August 4, 2011 at 12:28 pm
SanDroid (8/4/2011)
Several of you seem to agree that this is a bug that they can reproduce.However the count of user that can re-create and users that think this is a bug on the connect site is LOW. 😛
https://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=125502#details
Here is the link again in case you missed it. Without your feedback, and more of it, M$ will most likely not fix these things.
I get a Page Not Found clicking on that...?
Viewing 15 posts - 46 through 60 (of 63 total)
You must be logged in to reply to this topic. Login to reply