Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Change database collation Expand / Collapse
Author
Message
Posted Tuesday, May 14, 2013 1:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:29 AM
Points: 119, Visits: 451
Hi all,

I have a db that i am restoring from a SQL 2005 server to 2012, which is in the wrong collation (It was wrong for the old server as well). From what I understand if you do a ALTER DATABASE this will only change future data and the current data will still be in the old collation?

I found the following KB:

http://support.microsoft.com/default.aspx?scid=kb;en-us;325335

Is this still the only way to update everything to a new collation (the kb says its for 2000 and 2005)? or in 2012 can you get away with just an ALTER DB?

Hope someone can help.
Post #1452407
Posted Tuesday, May 14, 2013 6:30 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 10, 2014 2:36 AM
Points: 924, Visits: 714
this is one of the worst problems in SQL Server: you can only change only if there is no dependency on objects

Abhijit - http://abhijitmore.wordpress.com
Post #1452533
Posted Wednesday, May 15, 2013 3:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:29 AM
Points: 119, Visits: 451
Hi Abhijit,

Thanks for the reply.

So if i cant find any dependacies on columns with the different collation i could do a simple ALTER DATABASE?

Am i on the right track trying the following:

SELECT DISTINCT OBJECT_NAME (OBJECT_ID) AS 'Table Name'
,name AS 'Column Name'
, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects
WHERE TYPE = 'U')
AND collation_name IS NOT NULL
ORDER BY OBJECT_NAME (OBJECT_ID)

To find any tables in the database that have a collation set.

and run this against all the tables from that list to find if they have any dependacies?

SELECT * FROM sys.sql_expression_dependencies
WHERE referencing_id = X

If none of the tables have dependancies I can just change it with ALTER and im good to go?

Sorry for the confusion.

S
Post #1452999
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse