InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 07:23:34 GMT20http://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspxHangs in New Zealand too ;-)Thu, 11 Aug 2011 23:27:18 GMTdarryn pocockhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]SQLkiwi (8/4/2011)[/b][hr][quote][b]SanDroid (8/4/2011)[/b][hr]I just clicked it the link again on two systems and got the Connect page for this issue. Could it be the https part that is not working for you?[/quote]This is weird. I can view the page so long as I am not logged in to Connect. As soon as I log in, I get the error. I hate Connect.[/quote]That is not caused by connect. Had to have the Firewall/Proxey server fixed to resolve that issue at my work. Send me a private if you want more information. :cool:Thu, 04 Aug 2011 12:41:22 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]SanDroid (8/4/2011)[/b][hr]I just clicked it the link again on two systems and got the Connect page for this issue. Could it be the https part that is not working for you?[/quote]This is weird. I can view the page so long as I am not logged in to Connect. As soon as I log in, I get the error. I hate Connect.Thu, 04 Aug 2011 12:38:38 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]SQLkiwi (8/4/2011)[/b][hr][quote][b]SanDroid (8/4/2011)[/b][hr]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. :-P [url]https://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=125502#details[/url]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.[/quote]I get a Page Not Found clicking on that...?[/quote]I just clicked it the link again on two systems and got the Connect page for this issue. Could it be the https part that is not working for you?Thu, 04 Aug 2011 12:31:47 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]SanDroid (8/4/2011)[/b][hr]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. :-P [url]https://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=125502#details[/url]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.[/quote]I get a Page Not Found clicking on that...?Thu, 04 Aug 2011 12:28:39 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspxSeveral 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. :-P [url]https://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=125502#details[/url]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.Thu, 04 Aug 2011 11:38:08 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspxExcellant 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.:cool:Thu, 04 Aug 2011 11:33:16 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspxReally a good question.Wed, 03 Aug 2011 04:30:09 GMTDanny Oceanhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspxExcellent Question.for testing i executed in my machine. with Australian settings it got hanged.Tue, 02 Aug 2011 23:47:25 GMTmalleswarareddy_mhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspxIf 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 :-DI 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 :). Tue, 02 Aug 2011 15:54:51 GMTshannonjkhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspxNice question. Have always used SQL_Latin1_General_CP1_CI_AS as we do not have any servers outside the US. Interesting stuff though!Tue, 02 Aug 2011 12:36:22 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]SQLkiwi (8/2/2011)[/b][hr]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.[/quote]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):[url=http://msdn.microsoft.com/en-us/library/ms143508%28v=SQL.90%29.aspx]http://msdn.microsoft.com/en-us/library/ms143508%28v=SQL.90%29.aspx[/url].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.RichTue, 02 Aug 2011 08:03:40 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspxThanks MichaelTue, 02 Aug 2011 06:53:52 GMTDhruvesh Shahhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspxNo, I typed NULL because I started life as a C programmer and some habits die *really* hard...Tue, 02 Aug 2011 06:31:00 GMTpaul.knibbshttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]paul.knibbs (8/2/2011)[/b][hr]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! :-)[/quote]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 [i]in general[/i] don't define CHAR(0). The other small point is that CHAR(0) isn't NULL; perhaps you meant to type NUL?Tue, 02 Aug 2011 06:25:07 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspxWell, 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! :-)Tue, 02 Aug 2011 06:20:54 GMTpaul.knibbshttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspxI'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:[code="sql"]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))[/code]In those examples, only the first one goes into an endless loop.Tue, 02 Aug 2011 06:00:07 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]Thomas Abraham (8/1/2011)[/b][hr]Just curious - what situations require replacement of CHAR(0)? How does it end up in your data? Thanks.[/quote]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. ;-)ronTue, 02 Aug 2011 05:49:27 GMTronmoseshttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]Tom.Thomson (8/1/2011)[/b][hr][quote][b]Nakul Vachhrajani (8/1/2011)[/b][hr][quote][b]David in .AU (7/31/2011)[/b][hr]This article might be interesting: note the reply from microsoft at the bottom[url=https://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=125502]Replace of char(0) does not work in DB with Windows collation[/url]I know it isn't exactly the same issue, but it runs along the same lines...[/quote]Hello!Thank-you very much for this link. It explains the issue very well.[/quote]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.[/quote]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 :-)JosephTue, 02 Aug 2011 03:46:53 GMTJoseph-411461http://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]Dhruvesh Shah (8/2/2011)[/b][hr][quote][b]bitbucket-25253 (8/1/2011)[/b][hr][quote][b]Dhruvesh Shah (8/1/2011)[/b][hr]My server got hang. but from memory i have setup my instance as US. Can some one pls advise how can i find out what's my default country setting is?[/quote]Beyond finding your default contry setting, the following will display the serer / database collation settings.[code="sql"]SELECT SERVERPROPERTY(N'Collation')SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation')[/code][/quote]The result is "Latin1_General_CI_AS" what does that mean?[/quote]Dhruvesh,that your server collation and / or the collation of the AdventureWorks database is set to [b]English (Canada)[/b] (or [i]German (any German speaking country)[/i], and actually [i]English (any English speaking nation except US)[/i], etc.).In one of my previous posts I already mentioned this [url=http://msdn.microsoft.com/en-us/library/ms188046.aspx][b]document from Microsoft[/b][/url] that lists all the different collations for SQL Server 2K, 2K5 and 2K8.Regards,MichaelTue, 02 Aug 2011 03:05:24 GMTmichael.kaufmannhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]bitbucket-25253 (8/1/2011)[/b][hr][quote][b]Dhruvesh Shah (8/1/2011)[/b][hr]My server got hang. but from memory i have setup my instance as US. Can some one pls advise how can i find out what's my default country setting is?[/quote]Beyond finding your default contry setting, the following will display the serer / database collation settings.[code="sql"]SELECT SERVERPROPERTY(N'Collation')SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation')[/code][/quote]The result is "Latin1_General_CI_AS" what does that mean?Tue, 02 Aug 2011 02:47:59 GMTDhruvesh Shahhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspxNice question, but a bit hard for people who are not from the US or Canada...Tue, 02 Aug 2011 00:05:39 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]Richard Warr (8/1/2011)[/b][hr]My immediate thought was:US : 'Hello World'Canada: 'Hello World, eh?'However, that wasn;t one of the options and I got there with a lucky guess :-)[/quote]:laugh:Depending on where in Canada, it could also be "Bonjour monde".Mon, 01 Aug 2011 15:33:52 GMTFargoUThttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]Nakul Vachhrajani (8/1/2011)[/b][hr][quote][b]David in .AU (7/31/2011)[/b][hr]This article might be interesting: note the reply from microsoft at the bottom[url=https://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=125502]Replace of char(0) does not work in DB with Windows collation[/url]I know it isn't exactly the same issue, but it runs along the same lines...[/quote]Hello!Thank-you very much for this link. It explains the issue very well.[/quote]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.Mon, 01 Aug 2011 14:04:19 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]Richard Warr (8/1/2011)[/b]US : 'Hello World'Canada: 'Hello World, eh?'[/quote]Richard's comment is terrifically funny!But, on a serious note, has this problem been fixed in SQL Server 2008? I get same results ("Hello World") from: -- ----------------------------------------ALTER DATABASE databasename COLLATE Latin1_General_CI_AS --CanadaDECLARE @var varchar(max); SET @var = 'Hello World'; SELECT @var + CHAR(0) + @var -- ----------------------------------------and-- ----------------------------------------ALTER DATABASE databasename COLLATE SQL_Latin1_General_CP1_CI_AS --US DECLARE @var varchar(max); SET @var = 'Hello World'; SELECT @var + CHAR(0) + @var -- ----------------------------------------Mon, 01 Aug 2011 12:48:40 GMTRose Budhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]Richard Warr (8/1/2011)[/b][hr]My immediate thought was:US : 'Hello World'Canada: 'Hello World, eh?'quote]Good thing I wasn't drinking anything when I read your post. It would have been a case of "and then the milk shot out my nose!" Thanks for the laugh.Now, can someone PLEASE tell me why CHAR(0) is an issue? I've not come across it as something to worry about before. Is is related to front end text delimiters?Mon, 01 Aug 2011 10:43:26 GMTThomas Abrahamhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspxMy immediate thought was:US : 'Hello World'Canada: 'Hello World, eh?'However, that wasn;t one of the options and I got there with a lucky guess :-)Mon, 01 Aug 2011 10:38:28 GMTRichard Warrhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspxVery interesting question. I am curious why you would need to look for a char(0) though. Is it some type of transmission error? Or is it something coming put in the file deliberately?Mon, 01 Aug 2011 10:30:57 GMTKenneth.Fisherhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspxThanks for the question. Definately weird behavior. I got it right by a little guessing and some experience with coallations.Mon, 01 Aug 2011 10:27:26 GMTTrey Stakerhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspxVery interesting question.Mon, 01 Aug 2011 08:46:57 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspxVery interesting question and discussion but too hard for a Monday.Mon, 01 Aug 2011 07:35:28 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]cengland0 (8/1/2011)[/b][hr][quote][b]Eugene Elutin (8/1/2011)[/b][hr]Can someone guess without executing what the following SQL will return on Canada like server setup (I have the UK one) :-DDECLARE @var varchar(max); SET @var = 'Hello World'; SELECT @var + CHAR(0) + @var [/quote]My guess is that it works and returns:Hello WorldHello WorldI suspect the problem is only when using a replace command. The reason is that it gets stuck in an infinite loop while trying to find nothing. With your select, it's not looping so it should return fine.[/quote]Check the output to Text vs output to Grid :-)and try:DECLARE @var varchar(max); SET @var = 'Hello World'; select @var = @var + CHAR(0) + @var print 'normal text'print @varselect @varSome one can tell me why the print @var is printed in different font :w00t:Refer to one of my comments in a signature... Mon, 01 Aug 2011 07:31:13 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspxThis one just scares me.It would seem that REPLACE is still an old C function call, and it sees the CHAR(0) and treats it as the string terminator that C did? Or something else horrible like that?Seems old programming languages are sneaking back up on us.Mon, 01 Aug 2011 07:01:37 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]Dhruvesh Shah (8/1/2011)[/b][hr]My server got hang. but from memory i have setup my instance as US. Can some one pls advise how can i find out what's my default country setting is?[/quote]Beyond finding your default contry setting, the following will display the serer / database collation settings.[code="sql"]SELECT SERVERPROPERTY(N'Collation')SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation')[/code]Mon, 01 Aug 2011 06:34:11 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]paul.knibbs (8/1/2011)[/b][hr]It's still realistically a bug, though--even if CHAR(0) is treated as an empty string in a Windows collation, REPLACE() given an empty string as its second parameter should do nothing, not hang the connection![/quote]Yes, I agree it's a bug. Sorry if that wasn't clear before.Mon, 01 Aug 2011 06:25:37 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]cengland0 (8/1/2011)[/b][hr]My guess is that it works and returns:Hello WorldHello World[/quote]It returns 'Hello World ' in a SELECT statement, but 'Hello World Hello World' when using PRINT.CHAR(0) is often used as a variable-length string terminator.SELECT @var + CHAR(0) + @var, DATALENGTH(@var + CHAR(0) + @var)...returns 'Hello World ', 23TSQL is funny.Mon, 01 Aug 2011 06:23:40 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]Britt Cluff (8/1/2011)[/b][hr]Good question, but not sure how you could get it right without guessing.[/quote]Believe it or not, for this one, Google. I didn't know the answer (I don't usually have to deal with Canadian collation). It took < 10 minutes to find a sqlteam article that led to a Microsoft Connect article that discussed the problem, and then a quick search to find out what the default collation setting for Canada was. YMMV, of course.Mon, 01 Aug 2011 06:14:52 GMTKiarahttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]Eugene Elutin (8/1/2011)[/b][hr]Can someone guess without executing what the following SQL will return on Canada like server setup (I have the UK one) :-DDECLARE @var varchar(max); SET @var = 'Hello World'; SELECT @var + CHAR(0) + @var [/quote]My guess is that it works and returns:Hello WorldHello WorldI suspect the problem is only when using a replace command. The reason is that it gets stuck in an infinite loop while trying to find nothing. With your select, it's not looping so it should return fine.Mon, 01 Aug 2011 06:12:45 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspxJust curious - what situations require replacement of CHAR(0)? How does it end up in your data? Thanks.Mon, 01 Aug 2011 06:12:44 GMTThomas Abrahamhttp://www.sqlservercentral.com/Forums/Topic1151474-1694-1.aspx[quote][b]Dhruvesh Shah (8/1/2011)[/b][hr]My server got hang. but from memory i have setup my instance as US. Can some one pls advise how can i find out what's my default country setting is?[/quote]The actual problem is to do with the collation settings on the database--the only reason countries came in to it is because the default collation on a Canadian install of SQL server is different than a US install. Chances are the default collation on your server has been changed since it was installed.Mon, 01 Aug 2011 06:08:23 GMTpaul.knibbs