Saturday, August 08, 2015

SQL Server Collation Settings, Quelquechose de Vraiment Grav

Saw this post about a Windows 10 error message a couple weeks ago on Facebook and laughed my head off.

Microsoft has gone ahead and adopted the Sad Mac philosophy of error handling.
From a consumer's perspective, I guess this yodel is a tad better than Catastrophic Failure and reassures you that something might happen next.

From a developers perspective, you really should have included the bugcheck code, stack trace, or at least some other kind of error identifying the method call you are finally giving up on.

If not, one fix for fellow Canadians installing Windows 10 will be to ensure you meld with our neighbours in the south by selecting English (US) as the regional locale.  You selected French (Canada), didn't you?

Zut alors.  Didn't SQL have this issue a few years ago?  And Analysis Services?

Sorry MS, as a Windows 10 Insider I did not catch this bug.  Actually, I didn't really catch any bugs.  My Windows 10 install over the last year was a pleasant surprise, and I expect my main desktop to be upgraded soon.  Perhaps my only suggestion would be that if English (Canada) is selected, then error messages should be bilingual.  Seems like a waste of the second line of Something happened.

Instead of upgrading him, a family member who I provide tech support for asked me to just hide the "Upgrade to Windows 10" icon for him instead.  From a support perspective I though that was wise for now....  I used the scheduled tasks and registry edit methods.

A few times in the last year I've encountered various collation issues related to Windows Regional Settings and SQL Server installs, and the defaults MS uses for products like Sharepoint and Reporting Services.

To switch over collation of a single database, you can try this at your own risk.

1. ALTER the database to change the collation (after killing all processes that are using the database and dropping all user defined functions with varchar max that bind to the collation)

2. ALTER the columns (this script may need to be modified to include schema id's when dealing with multiple schemas.  This script was tested on SQL 2008 R2.

       + c.NAME + ' ' + t.NAME + '('
       + Cast(c.max_length AS NVARCHAR)
       + ') COLLATE ' + ' Latin1_General_CI_AS ' + CASE WHEN c.is_nullable = 1
       + ';'
--select,,, c.collation_name, c.is_nullable,, c.max_length
FROM   sys.objects o
       INNER JOIN sys.columns c
               ON c.object_id = o.object_id
       INNER JOIN sys.types t
               ON t.user_type_id = c.user_type_id
WHERE  c.collation_name IS NOT NULL
       AND o.type = 'u' 

No comments: