February 12, 2008 at 11:37 pm
Hi,
I have restored database from 2000 to 2005.As that have different collation name from tempdb of 2005.SO it gives problem for query.
Can we change collation of database??
As i am going to change it from command,it gives error.
code is
ALTER DATABASE database_name
{
|
|
| MODIFY NAME = new_database_name
| COLLATE collation_name
}
[;]
Is there any other way to chage collation during restore database?
February 18, 2008 at 6:20 am
[font="Courier New"]alter database yourdb collate new_collation[/font] will change database collation, which will be used for new objects created in this database. Yet if you want to change collation of existing columns, you have to change their collation for each of them separately. look at my blog post, maybe it will help you.
...and your only reply is slàinte mhath
February 19, 2008 at 2:35 am
Maybe I mis-understand this.
You can run the alter command to change the collation of existing columns, but any data already in the column will keep the OLD collation.
This could give some problems if tempdb - for example - uses a different collation.
My answer would be to dump the data out, alter the collations and then re-load.
If I get time, I'll try to write a script to prove/disprove ...
February 19, 2008 at 3:06 am
Thanks for your reply..
Yes i am going to re-install SQL SERVER 2005,where reinstalling it ask for collation setting,that you can set.
And i am going to set according to my database.
Hope this will work
February 19, 2008 at 3:19 am
avinash repe (2/19/2008)
Thanks for your reply..Yes i am going to re-install SQL SERVER 2005,where reinstalling it ask for collation setting,that you can set.
And i am going to set according to my database.
Hope this will work
Ian is right, if you want to keep the data, the best is to script your database, and create it using the right collations. There are many ways of doing it, you could use DTS/SSIS http://support.microsoft.com/kb/325335 or third party tools.
Regards,
Andras
February 20, 2008 at 3:44 am
Maybe it's too much work to change your queries but one option is to create your temp tables first...
--if you do like this...
Select name into #tmp from Person
--you can change to this
Create table #tmp(
name nvarchar(25) COLLATE DATABASE_DEFAULT
)
insert into #tmp(name)
Select name from Person
...it's more code but you will not have any problem with tempdb collation
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply