Re: Restoring database with different COLLATION
From: Andrea Montanari (andrea.sqlDMO_at_virgilio.it)
Date: 10/01/04
- Next message: Tom Wing: "Migrating an Access Database to MSDE"
- Previous message: William \(Bill\) Vaughn: "Re: MSDE and SQL 2000"
- In reply to: Jorge Brizuela: "Restoring database with different COLLATION"
- Next in thread: Andrea Montanari: "Re: Restoring database with different COLLATION"
- Reply: Andrea Montanari: "Re: Restoring database with different COLLATION"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 2 Oct 2004 01:22:03 +0200
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.shtm http://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
- Next message: Tom Wing: "Migrating an Access Database to MSDE"
- Previous message: William \(Bill\) Vaughn: "Re: MSDE and SQL 2000"
- In reply to: Jorge Brizuela: "Restoring database with different COLLATION"
- Next in thread: Andrea Montanari: "Re: Restoring database with different COLLATION"
- Reply: Andrea Montanari: "Re: Restoring database with different COLLATION"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
