May 14, 2013 at 1:02 am
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.
May 14, 2013 at 6:30 am
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
May 15, 2013 at 3:49 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply