SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Error changing name collation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

skriver
Starting Member

11 Posts

Posted - 07/29/2013 :  07:49:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 07/29/2013 :  08:12:45  Show Profile  Reply with Quote
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.

Go to Top of Page

skriver
Starting Member

11 Posts

Posted - 07/29/2013 :  10:12:58  Show Profile  Reply with Quote
Getting errors when trying to run that script but will take a look thanks.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/29/2013 :  10:19:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
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
Go to Top of Page

skriver
Starting Member

11 Posts

Posted - 07/29/2013 :  10:26:44  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 07/29/2013 :  10:40:02  Show Profile  Reply with Quote
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.
Go to Top of Page

skriver
Starting Member

11 Posts

Posted - 07/29/2013 :  11:28:25  Show Profile  Reply with Quote
Have never done this before but will try to Google to see how its done. Thanks for the pointers!
Go to Top of Page

skriver
Starting Member

11 Posts

Posted - 07/29/2013 :  12:35:38  Show Profile  Reply with Quote
Got it working! Awesome advice people, thanks!!!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 07/29/2013 :  12:45:24  Show Profile  Reply with Quote
You are welcome - glad you got it figured out.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000