Unable to update sys.columns - any other approach?

Question

I just found out, that since I created my DB on a different server that had a different collation, then immigrated to a new server with new collation, now I keep on having trouble, and I decided to change all the values of the old collation.

So I tried to execute this query:

sp_configure "Allow Updates", 1
GO
RECONFIGURE WITH OVERRIDE
GO

UPDATE sys.columns SET collation_name = 
    'SQL_Latin1_General_CP1_CI_AS' WHERE collation_name = 'Hebrew_CI_AS'
GO

sp_configure "Allow Updates", 0
GO
RECONFIGURE
GO

But here is the output of the query:

Configuration option 'allow updates' changed from 0 to 1. 
    Run the RECONFIGURE statement to install.

Msg 259, Level 16, State 1, Line 2
Ad hoc updates to system catalogs are not allowed.

Configuration option 'allow updates' changed from 1 to 0. 
    Run the RECONFIGURE statement to install.

UPDATE
According to the answers bellow, I am just looking for an automated way to perform the action.

SYS tables are read-only -- you need to get a list, and update the collation against the actual table/column — OMG Ponies, Oct 25 '10 at 20:10
@OMG Ponies, OK, so bottom line, is there an automated way to do it, as I am talking about a large amount of columns in various tables. I think I'll create a console application that generates the SQL script according to the values from sys.columns and sys.tables, if you have any other shorter ways, please lemme know. — Shimmy Weitzhandler, Oct 25 '10 at 22:11
Can you describe - which problems? Note that you probably have dependencies on columns with collation in question, like constraints, indexes, etc You will not be able to change collation without dropping them first. I doubt that you should fo it at all in this way as well as you correctly understood why and what to do. For example, why are you doing this on target (production?) database? Why have had this collation in first place? Why don't you change collation on your development server? How are you going to maintain it? — Gennady Vanin Геннадий Ванин, Oct 25 '10 at 23:19
vg8, look at my answer, there was indeed errors, but i had to edit 10 columns manually with the designer, all the other thousands were automated! still something! — Shimmy Weitzhandler, Oct 25 '10 at 23:27
I posted subquestion http://stackoverflow.com/questions/4020870/what-issues-to-anticipate-having-different-collations-between-development-and-pro — Gennady Vanin Геннадий Ванин, Oct 26 '10 at 05:18
One more http://stackoverflow.com/questions/4034114/how-to-script-non-default-collation-and-skip-explicit-scripting-for-default-colla — Gennady Vanin Геннадий Ванин, Oct 27 '10 at 14:15

score 4 · Answer 1 · answered Oct 25 '10 at 20:13

4

You need to issue ALTER TABLE commands in order to change the collations of the specific columns.

e.g.

ALTER TABLE YourTable 
ALTER COLUMN ColA VARCHAR(10) COLLATE Latin1_General_CI_AS NOT NULL

Check out this MSDN reference

answered Oct 25 '10 at 20:13

AdaTheDev

142,592
28
206
200

score 2 · Answer 2 · answered Oct 25 '10 at 20:12

2

You can use alter table to change the collation for each column.

The only way to change a database collation is to drop and recreate the database.

answered Oct 25 '10 at 20:12

Andomar

232,371
49
380
404

Shimmy Weitzhandler · Accepted Answer · 2010-10-25T23:31:52.057

This query is generates a script that will swap all Hebrew_CI_AS collated columns to Latin1_General_CI_AS collation:

DECLARE @Script varchar(MAX)
SET @Script = CAST((
SELECT 'ALTER TABLE [' + Tables.Name + '] ALTER COLUMN [' + 
  [Columns].Name + '] ' + Types.Name + '(' + 
  CAST([Columns].max_length AS varchar) + ') COLLATE Latin1_General_CI_AS ' + 
  CASE WHEN [Columns].is_nullable = 0 THEN 'NOT ' ELSE '' END + 'NULL '
FROM sys.tables Tables INNER JOIN sys.all_columns [Columns]
  ON [Tables].[object_id] = [Columns].[object_id]
  INNER JOIN sys.types Types ON [Columns].system_type_id = Types.system_type_id
WHERE [Columns].collation_name = 'Hebrew_CI_AS'
FOR XML PATH('')
) AS varchar(MAX))

PRINT @Script
EXEC(@Script)

NOTE: For columns that are indexed/constrained you'll need to edit manually, but that's also something since when executing the above query the error-result (for a constraint etc.) contains the table & column name, you must admit it's still better than do everything manually.

May I mark this post as answer? because the questioner will not gain reputation on marking your own answer — Gennady Vanin Геннадий Ванин, Oct 26 '10 at 04:47

Unable to update sys.columns - any other approach?

3 Answers3