August 1, 2011 at 6:34 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?
Beyond finding your default contry setting, the following will display the serer / database collation settings.
SELECT SERVERPROPERTY(N'Collation')
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation')
August 1, 2011 at 7:01 am
This 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.
August 1, 2011 at 7:31 am
cengland0 (8/1/2011)
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.
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 @var
select @var
Some one can tell me why the print @var is printed in different font :w00t:
Refer to one of my comments in a signature...
August 1, 2011 at 7:35 am
Very interesting question and discussion but too hard for a Monday.
August 1, 2011 at 8:46 am
Very interesting question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 1, 2011 at 10:27 am
Thanks for the question. Definately weird behavior. I got it right by a little guessing and some experience with coallations.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
August 1, 2011 at 10:30 am
Very 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?
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
August 1, 2011 at 10:38 am
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 🙂
August 1, 2011 at 10:43 am
Richard Warr (8/1/2011)
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?
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
August 1, 2011 at 12:48 pm
Richard Warr (8/1/2011)
US : 'Hello World'
Canada: 'Hello World, eh?'
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 --Canada
DECLARE @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
-- ----------------------------------------
August 1, 2011 at 2:04 pm
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.
Tom
August 1, 2011 at 3:33 pm
Richard Warr (8/1/2011)
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 🙂
:laugh:
Depending on where in Canada, it could also be "Bonjour monde".
August 2, 2011 at 12:05 am
Nice question, but a bit hard for people who are not from the US or Canada...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 2, 2011 at 2:48 am
bitbucket-25253 (8/1/2011)
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?Beyond finding your default contry setting, the following will display the serer / database collation settings.
SELECT SERVERPROPERTY(N'Collation')
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation')
The result is "Latin1_General_CI_AS" what does that mean?
August 2, 2011 at 3:05 am
Dhruvesh Shah (8/2/2011)
bitbucket-25253 (8/1/2011)
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?Beyond finding your default contry setting, the following will display the serer / database collation settings.
SELECT SERVERPROPERTY(N'Collation')
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation')
The result is "Latin1_General_CI_AS" what does that mean?
Dhruvesh,
that your server collation and / or the collation of the AdventureWorks database is set to English (Canada) (or German (any German speaking country), and actually English (any English speaking nation except US), etc.).
In one of my previous posts I already mentioned this document from Microsoft that lists all the different collations for SQL Server 2K, 2K5 and 2K8.
Regards,
Michael
Viewing 15 posts - 31 through 45 (of 63 total)
You must be logged in to reply to this topic. Login to reply