August 8, 2019 at 4:04 pm
Hi,
I'm getting an infuriating issue where I have my default collation of the server set to SQL_Latin1_General_CP1_CI_AS.
For some unknown reason some tables in different databases have a different collation AND some columns within those tables have a different collation again.
It's driving me mad. Any idea how I can stop this from happening?
I have software that's not working as it should and when I run the SQL trace utility and check the SQL it's giving collation conflict errors.
I've tried running the following script to update the collation of all tables/columns to be the same but it doesn't appear to work for some reason. It doesn't give an error it runs and say's it's altering the columns but when I go back to the columns and check the collation it hasn't changed them.
DECLARE @collate SYSNAME
SELECT @collate = 'SQL_Latin1_General_CP1_CI_AS'
SELECT
'[' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] -> ' + c.name
, 'ALTER TABLE [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']
ALTER COLUMN [' + c.name + '] ' +
UPPER(t.name) +
CASE WHEN t.name NOT IN ('ntext', 'text')
THEN '(' +
CASE
WHEN t.name IN ('nchar', 'nvarchar') AND c.max_length != -1
THEN CAST(c.max_length / 2 AS VARCHAR(10))
WHEN t.name IN ('char', 'varchar') AND c.max_length != -1
THEN CAST(c.max_length AS VARCHAR(10))
WHEN t.name IN ('nchar', 'nvarchar', 'char', 'varchar') AND c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length AS VARCHAR(10))
END + ')'
ELSE ''
END + ' COLLATE ' + @collate +
CASE WHEN c.is_nullable = 1
THEN ' NULL'
ELSE ' NOT NULL'
END
FROM sys.columns c
JOIN sys.objects o ON c.[object_id] = o.[object_id]
JOIN sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE t.name IN ('char', 'varchar', 'text', 'nvarchar', 'ntext', 'nchar')
AND c.collation_name != @collate
AND o.[type] = 'U'
Thank you
Paul
August 8, 2019 at 4:53 pm
No disrespect intended, but the commands shown only generate the commands to change the collations. Did you run the generated commands?
August 9, 2019 at 12:32 pm
Hi Crow,
I see, I didn't realise that. I got the script off the net, I thought it would update the collation for me.
Thanks for the insight. I'll copy the results an run them in SQL.
Thank you
Paul.
August 9, 2019 at 1:28 pm
Paul
It won't work unless you happen to have no indexes on the columns that you'll be altering. Constraints and indexes on such columns all have to be dropped before you change the collation, and added back afterwards.
John
August 9, 2019 at 2:13 pm
Yes, thanks John. I've done that now.
Anyone have any idea how or why some columns ended up different collation to the others and to the default collation of the server and how I can prevent that happening in the future?
Thank you
Paul.
August 16, 2019 at 1:48 pm
If your database originally resided on a different server (with a different collation), columns created would have used that default. Columns created after the migration would default to the collation of the new server.
August 16, 2019 at 2:27 pm
If your database originally resided on a different server (with a different collation), columns created would have used that default. Columns created after the migration would default to the collation of the new server.
Ha, just reading this made my head hurt.
Collation gets set on columns in a table. Every column could have a different collation. The default collation (most of the time) is SQL_Latin1_General_CP1_CI_AS. The 'CI' stands for 'case insensitive' which means the db treats 'FOO', 'Foo', and 'foo' the same when compared and/or sorted. This is why I type all SQL in lower case only. I've saved myself from millions of useless shift key strokes! Anyway, imo the #1 reason to change collation is when case sensitivity matters. Case matters with things like screen names, credit card names, etc...
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply