January 17, 2011 at 5:41 am
Hi ,
I need to change the colation of the extsing database . So i ran the following scripts.
[p]
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE test COLLATE SQL_Latin1_General_CP1_CI_AS
ALTER DATABASE test SET MULTI_USER
[/p]
the above statements run successfully.
Now i modify the colation of all the columns.
[p]
declare
@NewCollation varchar(255)
,@Stmt nvarchar(4000)
,@DBName sysname
set @NewCollation = 'SQL_Latin1_General_CP1_CI_AS' -- change this to the collation that you need
set @DBName = 'test'
declare
@CName varchar(255)
,@TName sysname
,@OName sysname
,@Sql varchar(8000)
,@Size int
,@Status tinyint
,@Colorder int
declare curcolumns cursor read_only forward_only local
for select
QUOTENAME(C.Name)
,T.Name
,QUOTENAME(U.Name) + '.' +QUOTENAME(O.Name)
,C.Prec
,C.isnullable
,C.colorder
from syscolumns C
inner join systypes T on C.xtype=T.xtype
inner join sysobjects O on C.ID=O.ID
inner join sysusers u on O.uid = u.uid
where T.Name in ('varchar', 'char', 'text', 'nchar', 'nvarchar', 'ntext')
and O.xtype in ('U')
and C.collation != @NewCollation
and objectProperty(O.ID, 'ismsshipped')=0
order by 3, 1
open curcolumns
SET XACT_ABORT ON
begin tran
fetch curcolumns into @CName, @TName, @OName, @Size, @status, @Colorder
while @@FETCH_STATUS =0
begin
set @sql='ALTER TABLE '+@OName+' ALTER COLUMN '+@CName+' '+@TName+ isnull ('('
+convert(varchar,@Size)+')', '') +' COLLATE '+ @NewCollation
+' '+case when @status=1 then 'NULL' else 'NOT NULL' end
exec(@Sql) -- change this to print if you need only the script, not the action
fetch curcolumns into @CName, @TName, @OName, @Size, @status, @Colorder
end
close curcolumns
deallocate curcolumns
commit tran
[/p]
I got the following errror. What i need to do and how i need to change this?
Msg 5074, Level 16, State 1, Line 1
The object 'PK_CONFIGDESCRIPTION' is dependent on column 'Path'.
Msg 5074, Level 16, State 1, Line 1
The object 'FK_ConfigValue_ConfigDescription' is dependent on column 'Path'.
Msg 5074, Level 16, State 1, Line 1
The object 'FK_ConfigPathUpdateRole_ConfigDescription' is dependent on column 'Path'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Path failed because one or more objects access this column.
January 17, 2011 at 7:42 am
i think to change DB collation you can use the following simple script
USE [master]
GO
ALTER DATABASE [TEST_DB] COLLATE New Collation Name
GO
March 18, 2011 at 3:39 am
All the answers are at 2 other recent topics - covering the fact you need to drop the PK & FK constraints if they are not numeric before you can change the collation of the table.
In order, you need to change the collation
- server level
- each database
- each table in the database
(I have posted a stored proc with the code u supplied)
See http://www.sqlservercentral.com/Forums/Topic1078120-2919-2.aspx#bm1079484
and
(Keep in mind TheSQLGuru's warning, all these steps will change the collation for the server, the databases default, then the table defs and will guarrantee consistency for the future, but it is not clear what happens to the data already existing in each table.)
March 22, 2011 at 5:14 am
First we have to run some scripts to minimize manual actions. The next step is rebuilding the master database to change the server to the correct collation. After that we have to configure all the databases, logins and settings.
Run this statement that creates a script to detach all the user databases. Save the result of this script as detachdbs.sql
SELECT 'EXEC sp_detach_db ''' + name + ''', ''false''' FROM sysdatabases where name not in ('master','tempdb','model','msdb','northwind')
Now we have to make a script to attach all the user databases, therefore run following script and save the result to attachdbs.sql
DECLARE @dbname varchar(50)
DECLARE @dbname_prev varchar(50)
DECLARE @file varchar(150)
SET @dbname_prev = ' '
DECLARE cAttach CURSOR
READ_ONLY
FOR SELECT a.name, b.filename FROM sysdatabases a inner join sysaltfiles b on a.dbid = b.dbid where a.name not in ('master','tempdb','model','msdb','northwind', 'pubs') order by 1
OPEN cAttach
FETCH NEXT FROM cAttach INTO @dbname, @file
WHILE (@@fetch_status = 0)
BEGIN
IF (@dbname_prev <> @dbname)
BEGIN
IF (@dbname_prev <> ' ') PRINT 'FOR ATTACH ';
PRINT '';
PRINT 'CREATE DATABASE [' + @dbname + '] ON';
PRINT '( FILENAME = ''' + rtrim(@file) + ''') ';
END
ELSE PRINT ', ( FILENAME = ''' + rtrim(@file) + ''') ';
SET @dbname_prev = @dbname
FETCH NEXT FROM cAttach INTO @dbname, @file
END
CLOSE cAttach
DEALLOCATE cAttach
PRINT 'FOR ATTACH ';
GO
The next step is making a script to create all SQL Logins.
The master database is the repository of the logins. After rebuilding the master database the changes made after installation are lost. Use this article to script the logins http://support.microsoft.com/kb/918992. Save the result as Logins.sql
EXEC dbo.sp_help_revlogin
Also the server role credentials located in de master database are lost after rebuilding de master database. Run this script and save it as LoginRoles.sql
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''sysadmin''' as script from syslogins
where sysadmin = 1
union
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''securityadmin''' as script from syslogins
where securityadmin = 1
union
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''serveradmin''' as script from syslogins
where serveradmin = 1
union
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''setupadmin''' as script from syslogins
where setupadmin = 1
union
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''processadmin''' as script from syslogins
where processadmin = 1
union
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''diskadmin''' as script from syslogins
where diskadmin = 1
union
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''dbcreator''' as script from syslogins
where dbcreator = 1
union
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''bulkadmin''' as script from syslogins
where bulkadmin = 1
To make sure you don't lost anything you can export and script all the objects in the msdn database. So export SSIS packages, scripts jobs, linked servers, and anything else you can think of.
The preparation is done so we can start with the real stuff. To detach all the user databases you have to make sure everyone is disconnected. You also need to stop the application services that connect to the staging environment.
Run the prepared script detachdbs.sql to detach the databases.
Now that all the user databases are detached we can rebuild the master database to change the server collation. First make sure you bring the SQL Server service offline. Locate the Setup Bootstrap folder, which is usually C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release. You need to run this command, but be sure you use the correct instance name, your domain-account and a new sa password.
“Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME={INSTANCE NAME} /SQLSYSADMINACCOUNTS={DOMAIN}\{WINDOWS ACCOUNT} /SAPWD={SA PASSWORD} /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS”
After a successful installation the collation of the SQL Instance is changed to SQL_Latin1_General_CP1_CI_AS. Please check it before you continue. (Right-click on the instance in the Object Explorer => Properties => 'General' page => Server Collation)
Now we can run the prepared scripts:
•Attach all the user databases with attachdbs.sql
•Create SQL Logins with Logins.sql
•Create server roles for the logins with LoginRoles.sql
You also need to reconfigure your SQL Instance because all configurations are set to default after rebuilding the master database.
The last step is changing the collation of the user databases. The collation of the attached user databases didn't change so you have to run the result of this script to change it.
Note: You need an exclusive lock on the database to do this
Once all the above steps are done, execute the below query:
select 'ALTER DATABASE [' + [name] + '] COLLATE SQL_Latin1_General_CP1_CI_AS' from sys.databases where collation_name <> 'SQL_Latin1_General_CP1_CI_AS'
March 22, 2011 at 6:10 am
vishnubhotla.uday your solution is changing the collation of the master database, and then the default collations of each of the databases...that's only 5% of the solution,and typically you need to change one database, and not every database. you completely ignored all the tables in the database with columns where an existing collation that does not match the new target collation.
I'm assuming we are changing just a single database's collation...that might be a typical issue i might see in my shop.
I'm actually throwing a little time scripting this out, it's very educational, but it's not easy;
the core issue is writing code that is smart enough to do the following:
1. write the DROP CONSTRAINT commands for any CHECK or DEFAULT constraints that feature a column that has collation not the same as the target collation. While you are in there, write the CREATE CONSTRAINT commands to maintain constraint names.
2. do the same for foreign key constraints that feature a column that has collation not the same as the target collation.
3. do the same for unique constraints that feature a column that has collation not the same as the target collation.
4. do the same for primary key constraints that feature a column that has collation not the same as the target collation; this one has seemed a little harder for me to script..i'm still mulling over how to write this one out dynamically.
5. do the same for any indexes that feature a column that has collation not the same as the target collation.
6. finally, write the ALTER TABLE command to change the collation of the data.
If I'm missing something, let me know, but that's what my prototype script is trying to do.
Lowell
March 22, 2011 at 10:26 am
Hi,
Thanks for your reply.
As of now i only know that we can change the collation at the server level.
Is it possible to change the collation only to a perticular DB?
As collation is the same throughout all the DBs in a server.
March 30, 2011 at 3:46 am
You can certainly change the collation of a single database.
USE master;
GO
CREATE DATABASE testdb
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO
ALTER DATABASE testDB
COLLATE French_CI_AI ;
GO
March 30, 2011 at 5:02 am
vishnubhotla.uday (3/22/2011)
Hi,Thanks for your reply.
As of now i only know that we can change the collation at the server level.
Is it possible to change the collation only to a perticular DB?
As collation is the same throughout all the DBs in a server.
You certainly can change the collation of a database and/or any given table within a database (commands supplied by other well-meaning contributors). The server collation is only the default for any database created within that server (but does not apply to databases migrated from other servers by attaching or restoring operations).
The question is WHY would you want to change a DB collation to make it different from the server collation?
Please read carefully the posts about the disastruous effect this change may have on existing tables (and data) within the database after you change its collation at
http://www.sqlservercentral.com/Forums/Topic1078120-2919-2.aspx#bm1079484
and
Also remember that TEMPDB always has the collation of the SERVER (via its model db).
The consequence is that any temp table (#tblName) in the database you have changed will have a different collation, leading to certain run-time failures on some operations.
March 31, 2011 at 7:13 am
I do not believe that changing database collation (even if you DO change all the table/column collations, which you didn't do) is a supported action by Microsoft. I think you must create a new database with proper collations on everything and then migrate the data to the new database.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply