SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Niagara Falls


Niagara Falls

Author
Message
paul.knibbs
paul.knibbs
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4168 Visits: 6240
No, I typed NULL because I started life as a C programmer and some habits die *really* hard...
Dhruvesh Shah
Dhruvesh Shah
Right there with Babe
Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)

Group: General Forum Members
Points: 795 Visits: 237
Thanks Michael
Rich Mechaber
Rich Mechaber
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2717 Visits: 3671
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
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6561 Visits: 2396
Nice question. Have always used SQL_Latin1_General_CP1_CI_AS as we do not have any servers outside the US. Interesting stuff though!
shannonjk
shannonjk
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1112 Visits: 840
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 :-D

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

Link to my blog http://notyelf.com/
malleswarareddy_m
malleswarareddy_m
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2725 Visits: 1189
Excellent Question.

for testing i executed in my machine. with Australian settings it got hanged.

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Danny Ocean
Danny Ocean
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2214 Visits: 1549
Really a good question.

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com
SanDroid
SanDroid
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2398 Visits: 1046
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.
Cool
SanDroid
SanDroid
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2398 Visits: 1046
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

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.
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34990 Visits: 11359
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. :-P

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



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search