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


change collation on a datase


change collation on a datase

Author
Message
henrik staun poulsen
henrik staun poulsen
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2060 Visits: 1222
Dear SQL Server Central,

So far it has been a nightmare, if you got your database collation wrong.

One of my colleagues has written a script that will do most of the hard work for you, and report where it fails.

Would it be possible for someone to test (and/or review) this script for us?

Best regards,

Henrik Staun Poulsen
www.sql.udstyr.dk

--*******************************************************
SET NOCOUNT ON
/*
Description: Code to change collation on a database.
Pre_condition:
Post_condition:
Input:
Output:
Written by: Karsten Laursen, Vestas Wind Systems A/S, Denmark
Change History: $Date: 6-07-07 9:12 $ , $Author: KLAUR $, $Revision: 1 $

*/

DECLARE @NewCollation sysname,
@CurrentCollation sysname,
@DatabaseName sysname,
@SQLString nvarchar(max),
@TableName sysname,
@ColumnName sysname,
@Nullable varchar(3),
@DataType nvarchar(128),
@CharacterLength int,
@DashString varchar(100),
@ErrorMessage nvarchar(max),
@ErrorMessageLength int,
@ErrorCount int,
@PrintString varchar(100),
@i int

SET @NewCollation = 'Latin1_General_CI_AS'
--SET @NewCollation = 'Danish_Norwegian_CI_AS'
SET @CurrentCollation = CAST( DATABASEPROPERTYEX ( db_name(), 'Collation' ) AS sysname)
SET @DatabaseName = db_name()
SET @SQLString = ''
SET @DashString = '---------------------------------------------------------------------'
SET @ErrorMessage = ''

PRINT 'Current database: ' + @DatabaseName
PRINT 'Current collation: ' + @CurrentCollation
PRINT 'New collation: ' + @NewCollation
PRINT ''

IF EXISTS (SELECT 1 FROM master.dbo.sysprocesses
WHERE dbid = db_id()
AND spid <> @@spid
)
BEGIN

PRINT 'Database ' + @DatabaseName + ' could not be locked exclusively'

RETURN

END


IF @NewCollation NOT IN (SELECT name FROM master.dbo.fn_helpcollations())
BEGIN

PRINT @NewCollation + ' is not a valid collation'

RETURN

END

IF @NewCollation <> @CurrentCollation
BEGIN

SELECT @SQLString = 'ALTER DATABASE ' + @DatabaseName + ' COLLATE ' + @NewCollation

BEGIN TRY

EXEC (@SQLString)

PRINT 'Database collation has been changed'

END TRY

BEGIN CATCH

PRINT 'Database collation could not be changed due to the follwing errors:'
PRINT @DashString
EXEC (@SQLString)

RETURN

END CATCH

PRINT ''

END

DECLARE @Columns TABLE
(
TableName sysname,
ColumnName sysname,
Nullable varchar(3),
DataType nvarchar(128),
CharacterLength int,
Changed bit,
Error bit,
ErrorMessage nvarchar(2048)
)

INSERT @Columns
(
TableName,
ColumnName,
DataType,
CharacterLength,
Nullable,
Changed
)
SELECT T.TABLE_NAME,
C.COLUMN_NAME,
C.DATA_TYPE,
C.CHARACTER_MAXIMUM_LENGTH,
C.IS_NULLABLE,
0
FROM INFORMATION_SCHEMA.COLUMNS C
JOIN INFORMATION_SCHEMA.TABLES T
ON C.TABLE_NAME = T.TABLE_NAME
WHERE C.DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext', 'sysname')
AND C.COLLATION_NAME <> @NewCollation
AND T.TABLE_TYPE = 'BASE TABLE'

IF EXISTS (SELECT 1 FROM @Columns)
BEGIN

PRINT 'The following columns has been changed:'
PRINT @DashString

END
ELSE BEGIN

PRINT 'No changes needed'

RETURN

END

WHILE EXISTS (SELECT 1 FROM @Columns WHERE Changed = 0 AND Error IS NULL)
BEGIN

SELECT TOP 1 @TableName = TableName,
@ColumnName = ColumnName,
@DataType = DataType,
@CharacterLength = CharacterLength,
@Nullable = Nullable
FROM @Columns
WHERE Changed = 0
AND Error IS NULL
ORDER BY TableName, ColumnName

SELECT @SQLString = 'ALTER TABLE ' + @TableName
+ ' ALTER COLUMN ' + @ColumnName
+ ' ' + @DataType
+ CASE WHEN @CharacterLength = -1 THEN '(max)'
WHEN @DataType IN ('text', 'ntext') THEN ''
ELSE '(' + CAST (@CharacterLength AS nvarchar(10)) + ')' END
+ ' COLLATE ' + @NewCollation
+ ' ' + CASE @Nullable WHEN 'NO' THEN 'NOT NULL' ELSE 'NULL' END

BEGIN TRY

EXEC (@SQLString)

UPDATE @Columns
SET Changed = 1
WHERE TableName = @TableName
AND ColumnName = @ColumnName

PRINT @TableName + ', ' + @ColumnName

END TRY

BEGIN CATCH

UPDATE @Columns
SET Error = 1,
ErrorMessage = ERROR_MESSAGE()
WHERE TableName = @TableName
AND ColumnName = @ColumnName

SELECT @ErrorMessage = @ErrorMessage + @TableName + ', ' + @ColumnName + ': ' + ErrorMessage + char(10)-- + char(13)
FROM @Columns
WHERE TableName = @TableName
AND ColumnName = @ColumnName

END CATCH

END

IF @ErrorMessage <> ''
BEGIN

SELECT @ErrorCount = COUNT(*)
FROM @Columns
WHERE Error = 1

SELECT @PrintString = 'The following errors occured (' + CONVERT(varchar, @ErrorCount) + '):'

PRINT ''
PRINT @PrintString
PRINT @DashString

SELECT @ErrorMessageLength = LEN(@ErrorMessage)
SET @i = 1

WHILE @i <= @ErrorMessageLength
BEGIN

PRINT SUBSTRING(@ErrorMessage, @i, 4000)

SELECT @i = @i + 4000

END

END



free_mascot
free_mascot
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4069 Visits: 2235
Check following link:

http://msdn2.microsoft.com/en-us/library/ms174269.aspx
--USE THIS TO CHANGE DATABASE COLLATION

http://msdn2.microsoft.com/en-us/library/aa197950(SQL.80).aspx
--USE THIS TO CHANGE INSTANCE COLLATION

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
henrik staun poulsen
henrik staun poulsen
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2060 Visits: 1222
hi,

This only change the collation on the database, not the objects in the database.

Best regards,
Henrik Staun Poulsen
www.sql.udstyr.com



Kenneth Wilhelmsson
Kenneth Wilhelmsson
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2599 Visits: 699
If your existing data may contain extended ascii (åäö and such), you'd probably want to store away your data before changing collation.

The 'old and proven' way is something along these lines...

1) Script out the db and all objects in it. (remove all references to COLLATION that is wrong)
2) BCP out all data from all tables. (store in a safe place, or two..)
3) Drop the db.
4) Create db with the desired collation
5) Create all objects from the script(s) (tables, procs, views, triggers, users etc..)
... make sure that the script(s) has no references to the 'old' collation
6) BCP all data back in again.
7) Done, go have a beer. =;o)

/Kenneth



henrik staun poulsen
henrik staun poulsen
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2060 Visits: 1222
Hi Kenneth,

My colleague has written the script in order to invalidate the "old proven" way.
We have tested it on our database, but have not had the currage to go live with it yet.

The problems we have found, have been fixed, or the script has been enhanced with a warning message, on the stuff it cannot handle.

Hence this post, in the hope that somebody has the time to test it, or review it.
It might also help other people without the skill or time to develop such a script.

I also forgot to tell that it is an in-place fix; you do not need an additional copy of your data.
That is one big advantage over your method!

Thank you for posting,

Henrik



noeld
noeld
SSCrazy Eights
SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)

Group: General Forum Members
Points: 9952 Visits: 2048
When you do COLLATE it DOES *NOT* change the data that is *already* there. Keneth is right, BCP-OUT->Rebuild->BCP-IN is the way to go!

Cheers,


* Noel
henrik staun poulsen
henrik staun poulsen
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2060 Visits: 1222
Noel,

Are you saying that "ALTER TABLE xx ALTER COLUMN yy COLLATE zz" does not change the data in the table, that it only changes how the table appears to the world?

So could we get away with a "SELECT a,b,c INTO temp_xx" and then a "DROP TABLE xx", rename table ?

We do not have disk space for an additional copy of the entire database, and only maybe we have space for an extra copy of our biggest table.
I know that USB drives are not that expensive, but they require site visits (which are very expensive).

TIA
Henrik Staun Poulsen



Kenneth Wilhelmsson
Kenneth Wilhelmsson
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2599 Visits: 699
Hmmm... perhaps the old arcane ways are up for revision...
Did a very tiny test in tempdb on a 2005 box, and it indeed looks like
alter table alter column collate actually seems to change the ascii values of data already stored in the column...

/Kenneth



henrik staun poulsen
henrik staun poulsen
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2060 Visits: 1222
Kenneth,

Hurraaaa. :-)
Thank you for testing, and sharing.

Henrik



Kenneth Wilhelmsson
Kenneth Wilhelmsson
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2599 Visits: 699
Heh. Well, you need to do your fair share of testing also.
After all, it's your data =;o)

Especially look out for 'weird' chars (from an ascii point of view) that may not convert as expected.
It all depends on if you do have extended ascii, from which collation to which collation you're going etc.

Here's a small illustration of what may be an issue.. (or it may not)


select 'Ø' collate SQL_Latin1_General_Cp1_CI_AS, ascii('Ø' collate SQL_Latin1_General_Cp1_CI_AS)

select 'Ø' collate SQL_Latin1_General_Cp437_CI_AS, ascii('Ø' collate SQL_Latin1_General_Cp437_CI_AS)


---- -----------
Ø 216

(1 row(s) affected)


---- -----------
O 79

(1 row(s) affected)


/Kenneth



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