Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Niagara Falls Expand / Collapse
Author
Message
Posted Monday, August 1, 2011 12:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:05 AM
Points: 2,842, Visits: 3,875
I liked the QOTD and especially the Niagara Falls idea.

Best Regards,
Chris Büttner
Post #1151632
Posted Monday, August 1, 2011 1:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:44 AM
Points: 11,194, Visits: 11,168
David in .AU (7/31/2011)
This article might be interesting: note the reply from microsoft at the bottom

Replace 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...

That explains it nicely - and Peter Larsson (SwePeso) added pretty much the exact same example as this QotD in the comments.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1151635
Posted Monday, August 1, 2011 3:19 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
You got me on this one. I had no idea it would hang. Great question.
Post #1151668
Posted Monday, August 1, 2011 3:42 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:52 AM
Points: 1,639, Visits: 5,628
SQLkiwi (8/1/2011)
That explains it nicely - and Peter Larsson (SwePeso) added pretty much the exact same example as this QotD in the comments.


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!
Post #1151679
Posted Monday, August 1, 2011 5:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
paul.knibbs (8/1/2011)
SQLkiwi (8/1/2011)
That explains it nicely - and Peter Larsson (SwePeso) added pretty much the exact same example as this QotD in the comments.


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!


I'd also second that this is a bug, because the following works nicely:

select replace(cast('Hello World' collate SQL_Latin1_General_CP1_CI_AS as varchar(50)), char(0), '')
select replace(cast('Hello World' collate Latin1_General_100_CI_AS as varchar(50)), char(0), '')
select replace(cast('Hello World' collate Latin1_General_CI_AS as varchar(50)), char(0), '')

I'm well aware that the question was about
varchar(max)
(that's why I got it wrong)... but given only 11 characters tried it anyway.

BTW, collation Latin1_General_100_ is English (Canada) (and many others) in SQL Server 2008, while Latin1_General_ is English (Canada) for SQL Server 2000 and 2005; you may want to check out this document.

-Michael
Post #1151719
Posted Monday, August 1, 2011 5:32 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 17, 2013 11:53 AM
Points: 623, Visits: 237
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?
Post #1151720
Posted Monday, August 1, 2011 5:41 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:45 PM
Points: 1,589, Visits: 253
Good question, but not sure how you could get it right without guessing.

http://brittcluff.blogspot.com/
Post #1151735
Posted Monday, August 1, 2011 5:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
Britt Cluff (8/1/2011)
Good question, but not sure how you could get it right without guessing.

By trying it.
Post #1151737
Posted Monday, August 1, 2011 5:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 2,856, Visits: 5,124
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



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1151739
Posted Monday, August 1, 2011 5:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
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
Post #1151744
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse