August 1, 2011 at 5:32 am
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?
August 1, 2011 at 5:41 am
Good question, but not sure how you could get it right without guessing.
http://brittcluff.blogspot.com/
August 1, 2011 at 5:44 am
Britt Cluff (8/1/2011)
Good question, but not sure how you could get it right without guessing.
By trying it.
August 1, 2011 at 5:52 am
Can someone guess without executing what the following SQL will return on Canada like server setup (I have the UK one) 😀
DECLARE @var varchar(max);
SET @var = 'Hello World';
SELECT @var + CHAR(0) + @var
August 1, 2011 at 5:59 am
Dhruvesh Shah (8/1/2011)
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?
That depends on the account your SQL Server is running in.
Let's assume it's the SYSTEM account:
- Open up the Registry Editor.
- Expand HKEY_USERS\S-1-15-18\Control Panel\International
- If you click on the International key, the regional settings will show for the SYSTEM account.
If you're using a dedicated account with no changes to the default settings, you may go to:
- HKEY_USERS\.DEFAULT\Control Panel\International
If you're using a dedicated account with changes to the default settings, you'll first need to go to:
- HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList
- Click on any of the S-IDs to find out about the account name (usually to be identified via the ProfileImagePath value) and make a note of the S-ID for your SQL Server account.
- Then go to HKEY_USERS\S-1-15-21-xxxxxxxxx\Control Panel\International
This may not be the most elegant way, but this one definitely works and gives you the results you need.
BTW, the key for the ProfileList will always read \Microsoft\Windows NT\, regardless of the OS your server (or workstation) is running.
-Michael
August 1, 2011 at 6:05 am
Eugene Elutin (8/1/2011)
Can someone guess without executing what the following SQL will return on Canada like server setup (I have the UK one) 😀DECLARE @var varchar(max);
SET @var = 'Hello World';
SELECT @var + CHAR(0) + @var
DECLARE @var varchar(max);
SET @var = 'Hello World';
SET @var = @var + CHAR(0) + @var
PRINT @var
:w00t:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 1, 2011 at 6:07 am
Great question - thanks!
-Ki
August 1, 2011 at 6:07 am
I found that someone reported this "bug" (opened on 2/7/2006 and has 12 up votes) to Microsoft already but they do not consider it a bug:
https://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=125502
Their response:
This is due to the fact that 0x0000 is an undefined character in Windows collations. All undefined characters are ignored during comparison, sort, and pattern matching. So searing for 'a' + char(0) is really searching for ‘a’, and searching for char(0) is equivalent to empty string.
The way to handle undefined character is a bit confusing, but this is the way that Windows defined to sort them, and SQL Server conforms with the general Windows API.
In SQL collation, there is no notion of undefined character. Each code point is assigned a weight, that's why we don't see a problem there.
Edit: Oops, sorry. I see David in .AU has already posted this link.
August 1, 2011 at 6:08 am
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.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
August 1, 2011 at 6:08 am
Dhruvesh Shah (8/1/2011)
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?
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.
August 1, 2011 at 6:12 am
Eugene Elutin (8/1/2011)
Can someone guess without executing what the following SQL will return on Canada like server setup (I have the UK one) 😀DECLARE @var varchar(max);
SET @var = 'Hello World';
SELECT @var + CHAR(0) + @var
My guess is that it works and returns:
Hello WorldHello World
I 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.
August 1, 2011 at 6:14 am
Britt Cluff (8/1/2011)
Good question, but not sure how you could get it right without guessing.
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.
-Ki
August 1, 2011 at 6:23 am
cengland0 (8/1/2011)
My guess is that it works and returns:Hello WorldHello World
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 ', 23
TSQL is funny.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 1, 2011 at 6:25 am
paul.knibbs (8/1/2011)
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!
Yes, I agree it's a bug. Sorry if that wasn't clear before.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 16 through 30 (of 63 total)
You must be logged in to reply to this topic. Login to reply