Error changing name collation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error changing name collation

Author

Topic

skriver
Starting Member

11 Posts

Posted - 2013-07-29 : 07:49:54

I am trying to change the name collation of a SQL database as an update script I am running needs the main collation to be SQL_Latin1_General_CP1_CI_AS

I have tried going into Properties and options then changing the collation type but I get this error

"The Database could not be exclusively locked to perform the operation. ALTER DATABASE failed. The default collation of database 'nutri93' cannot be set to SQL_Latin1_General_CP1_CI_AS."

I have tried SQL scripts that I have found to take the database into a single user mode but still get the same errror.

Usually its this Database nutri93 could not be locked exclusively

Any ideas on how I can change the collation type? Currently the only option seems to be a complete hard install of SQL Management Studio

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-29 : 08:12:45

Set the database to single user mode, change the collation and set it back to multi user mode like shown below. Change the "TempDatabase" to match your database name.

USE master;
GO
ALTER DATABASE TempDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

USE master;
GO
ALTER DATABASE TempDatabase
SQL_Latin1_General_CP1_CI_AS;
GO

use master
GO
ALTER DATABASE TempDatabase
SET MULTI_USER;
GO

As an aside, any columns that have the original collation will remain in that collation and will not be affected by this change. Any future columns created will use the new collation by default.

skriver
Starting Member

11 Posts

Posted - 2013-07-29 : 10:12:58

Getting errors when trying to run that script but will take a look thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-29 : 10:19:00

[code]USE master;
GO
ALTER DATABASE nutri93
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

USE master;
GO
ALTER DATABASE nutri93
COLLATE SQL_Latin1_General_CP1_CI_AS;
GO

use master
GO
ALTER DATABASE nutri93
SET MULTI_USER;
GO[/code]Please remember that existing objects will still have old collation. Only new objects will inherit the new collation.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

skriver
Starting Member

11 Posts

Posted - 2013-07-29 : 10:26:44

I get this error

(8 row(s) affected)
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Msg 5075, Level 16, State 1, Line 1
The object 'Split' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Msg 5075, Level 16, State 1, Line 1
The object 'CHK_Store_HasURI' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'nutri93' cannot be set to SQL_Latin1_General_CP1_CI_AS.

(8 row(s) affected)

Any idea how to remove the dependecies? I have never done this before.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-29 : 10:40:02

quote:
Originally posted by skriver

I get this error

(8 row(s) affected)
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Msg 5075, Level 16, State 1, Line 1
The object 'Split' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Msg 5075, Level 16, State 1, Line 1
The object 'CHK_Store_HasURI' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'nutri93' cannot be set to SQL_Latin1_General_CP1_CI_AS.

(8 row(s) affected)

Any idea how to remove the dependecies? I have never done this before.

You have to fix the two dependencies listed in the error message.

1. Script those objects (including permissions), and save the script.
2. If possible, alter the scripts to remove the schema binding. If not drop the objects.
3. Then alter the collatiotion.
4. Recreate/alter the scripts to include the schema binding.

Please take a backup of your database before you do this. Also, this simply addresses the requirements from the perspective of SQL. You should evaluate what those schema-bound objects do, and what the impact of changing the collation is going to be on those objects.

skriver
Starting Member

11 Posts

Posted - 2013-07-29 : 11:28:25

Have never done this before but will try to Google to see how its done. Thanks for the pointers!

skriver
Starting Member

11 Posts

Posted - 2013-07-29 : 12:35:38

Got it working! Awesome advice people, thanks!!!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-29 : 12:45:24

You are welcome - glad you got it figured out.