Restoring database with different COLLATION

I make backup from SQL Server 2000 and distribute this backup to our
customers.
They install MSDE 2000, and I can't garantee what COLLATION they will use.
How I can modify the COLLATION of the database after restoring it?
Thanks in advance.
hi Jorge,
"Jorge Brizuela" <jorge_brizuela@.spymac.com> ha scritto nel messaggio
news:OckM6w$pEHA.2948@.TK2MSFTNGP11.phx.gbl
> I make backup from SQL Server 2000 and distribute this backup to our
> customers.
> They install MSDE 2000, and I can't garantee what COLLATION they will
> use.
> How I can modify the COLLATION of the database after restoring it?
> Thanks in advance.
as SQL Server 2000 supports different collations and sort orders for each
database, and even different collations at column level granularity, you
should not require altering that setting for your distributed database...
you can however use the
ALTER DATABASE db_name
COLLATE new_collation...
ALTER TABLE tbname
ALTER COLUMN colname type
COLLATE new_collation
so you have to alter the database setting, and then modify all varchar(n),
char(n) and text datatype columns accordingly...
that's to say
SET NOCOUNT ON
SELECT DATABASEPROPERTYEX( 'master' , 'Collation') AS [master an sys
databases collation]
GO
CREATE DATABASE TEST
GO
USE TEST
GO
CREATE TABLE dbo.TestTB (
ID INT NOT NULL ,
name VARCHAR(10) NOT NULL
)
INSERT INTO dbo.TestTB VALUES ( 1 , 'Andrea')
CREATE TABLE dbo.TestTB2 (
ID INT NOT NULL ,
name VARCHAR(10) NOT NULL
)
GO
SELECT DATABASEPROPERTYEX( 'TEST' , 'Collation') AS [TEST database collation
original]
GO
ALTER DATABASE TEST
COLLATE Latin1_General_CS_AI_KS_WS
GO
SELECT DATABASEPROPERTYEX( 'TEST' , 'Collation') AS [TEST database collation
after changing]
GO
PRINT 'alter each varchar(n), char(n) to the defined collation by HAND'
ALTER TABLE dbo.TestTB2
ALTER COLUMN [name] VARCHAR(10)
COLLATE Latin1_General_CS_AI_KS_WS
ALTER TABLE dbo.TestTB
ALTER COLUMN [name] VARCHAR(10)
COLLATE Latin1_General_CS_AI_KS_WS
GO
PRINT ''
PRINT '--'
PRINT 'alter each varchar(n), char(n) to the defined collation using a
cursor'
PRINT 'an ALTER TABLE ALTER COLUMN script will be written (not executed)'
DECLARE @.sql VARCHAR(2000)
DECLARE @.table_schema VARCHAR(255),
@.table_name VARCHAR(255),
@.column_name VARCHAR(255),
@.is_nullable VARCHAR(255),
@.data_type VARCHAR(255),
@.character_maximum_length VARCHAR(255)
DECLARE myCur CURSOR FOR
SELECT TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
IS_NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'TEST'
AND DATA_TYPE IN ('varchar', 'char', 'nvarchar', 'nchar')
AND TABLE_NAME NOT LIKE 'sys%'
OPEN myCur
FETCH NEXT FROM myCur INTO @.table_schema,
@.table_name,
@.column_name,
@.is_nullable,
@.data_type,
@.character_maximum_length
WHILE @.@.FETCH_STATUS <> -1
BEGIN
SET @.sql = 'ALTER TABLE ' + @.table_schema + '.' + @.table_name
+ ' ALTER COLUMN ' + @.column_name + ' '
+ @.data_type + '(' + @.character_maximum_length + ') '
+ 'COLLATE Latin1_General_CS_AI_KS_WS '
+ CASE @.is_nullable WHEN 'No' THEN 'NOT NULL' ELSE 'NULL' END
PRINT @.sql
FETCH NEXT FROM myCur INTO @.table_schema,
@.table_name,
@.column_name,
@.is_nullable,
@.data_type,
@.character_maximum_length
END
CLOSE myCur
DEALLOCATE myCur
GO
PRINT 'drop all'
USE master
GO
DROP DATABASE TEST
as you can see... you can do it by hand, changing each column setting or
doing something handy like letting SQL Server writing the actual
Transact-SQL ALTER TABLE ALTER COLUMN ... statements you have to execute to
perform the desired result...
the actual statements will be
ALTER TABLE dbo.TestTB ALTER COLUMN name varchar(10) COLLATE
Latin1_General_CS_AI_KS_WS NULL
ALTER TABLE dbo.TestTB2 ALTER COLUMN name varchar(10) COLLATE
Latin1_General_CS_AI_KS_WS NULL
I do not love cursors, but this is one of the cases I could not find another
way to have the job done...
by the way, the problem you describe (it's no more a problem in SQL Server
2000) is one of the reasons I do not deploy my databases using restore
and/or sp_attach_db features... you are not "inheriting" all users database
settings like general sort order, model database specifi settings and
objects the end user placed in that template database...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi again, Jorge,
actually it can be done without a cursor, but yesterday night I was too
tired... =;-D
I appologize...
SET NOCOUNT ON
SELECT DATABASEPROPERTYEX( 'master' , 'Collation') AS [master and sys
databases collation]
GO
CREATE DATABASE TEST
GO
USE TEST
GO
CREATE TABLE dbo.TestTB (
ID INT NOT NULL ,
name VARCHAR(10) COLLATE Latin1_General_CS_AI_KS_WS NOT NULL
)
INSERT INTO dbo.TestTB VALUES ( 1 , 'Andrea')
CREATE TABLE dbo.TestTB2 (
ID INT NOT NULL ,
name VARCHAR(10) COLLATE Latin1_General_CS_AI_KS_WS NOT NULL
)
GO
SELECT DATABASEPROPERTYEX( 'TEST' , 'Collation') AS [TEST database collation
original]
DECLARE @.sysCollation VARCHAR(128)
SELECT @.sysCollation = CONVERT(VARCHAR(128), DATABASEPROPERTYEX( 'TEST' ,
'Collation') )
SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + CHAR(10) + CHAR(9)
+ ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE + '(' +
CONVERT(VARCHAR(5) , CHARACTER_MAXIMUM_LENGTH ) + ') '
+ 'COLLATE ' + @.sysCollation + ' '
+ CASE IS_NULLABLE WHEN 'No' THEN 'NOT NULL' ELSE 'NULL' END +
CHAR(10) + 'GO'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'TEST'
AND DATA_TYPE IN ('varchar', 'char', 'nvarchar', 'nchar')
AND TABLE_NAME NOT LIKE 'sys%'
ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME
GO
PRINT 'cleanup'
USE master
GO
DROP DATABASE TEST
the resultin ALTER TABLE statements,
ALTER TABLE dbo.TestTB
ALTER COLUMN name varchar(10) COLLATE Latin1_General_CI_AS NOT NULL
GO
ALTER TABLE dbo.TestTB2
ALTER COLUMN name varchar(10) COLLATE Latin1_General_CI_AS NOT NULL
GO
in this case, can be then clipped and executed to set the desired collation
setting...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you very very much!!!
You help me a lot!!!!
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> escribi en el mensaje
news:2s7q7pF1h8u71U1@.uni-berlin.de...
> hi again, Jorge,
> actually it can be done without a cursor, but yesterday night I was too
> tired... =;-D
> I appologize...
> SET NOCOUNT ON
> SELECT DATABASEPROPERTYEX( 'master' , 'Collation') AS [master and sys
> databases collation]
> GO
> CREATE DATABASE TEST
> GO
> USE TEST
> GO
> CREATE TABLE dbo.TestTB (
> ID INT NOT NULL ,
> name VARCHAR(10) COLLATE Latin1_General_CS_AI_KS_WS NOT NULL
> )
> INSERT INTO dbo.TestTB VALUES ( 1 , 'Andrea')
> CREATE TABLE dbo.TestTB2 (
> ID INT NOT NULL ,
> name VARCHAR(10) COLLATE Latin1_General_CS_AI_KS_WS NOT NULL
> )
> GO
> SELECT DATABASEPROPERTYEX( 'TEST' , 'Collation') AS [TEST database
> collation
> original]
> DECLARE @.sysCollation VARCHAR(128)
> SELECT @.sysCollation = CONVERT(VARCHAR(128), DATABASEPROPERTYEX( 'TEST' ,
> 'Collation') )
> SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + CHAR(10) +
> CHAR(9)
> + ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE + '(' +
> CONVERT(VARCHAR(5) , CHARACTER_MAXIMUM_LENGTH ) + ') '
> + 'COLLATE ' + @.sysCollation + ' '
> + CASE IS_NULLABLE WHEN 'No' THEN 'NOT NULL' ELSE 'NULL' END +
> CHAR(10) + 'GO'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_CATALOG = 'TEST'
> AND DATA_TYPE IN ('varchar', 'char', 'nvarchar', 'nchar')
> AND TABLE_NAME NOT LIKE 'sys%'
> ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME
> GO
> PRINT 'cleanup'
> USE master
> GO
> DROP DATABASE TEST
> the resultin ALTER TABLE statements,
> ALTER TABLE dbo.TestTB
> ALTER COLUMN name varchar(10) COLLATE Latin1_General_CI_AS NOT NULL
> GO
> ALTER TABLE dbo.TestTB2
> ALTER COLUMN name varchar(10) COLLATE Latin1_General_CI_AS NOT NULL
> GO
> in this case, can be then clipped and executed to set the desired
> collation
> setting...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>