May 26, 2005 at 3:51 pm
Hi
I have detached and copied my databases to a new server,and. When I now try to use these on the new server I get this error: Cannot resolve collation conflict for union operation.
old server run on w2k, sql 2k with sp3a, while new server run on w2k3 server, sql 2k with sp4
Afer doing some research, I found that this is caused by the fact that the new server had a collation setting "Danish/Norwegian", while the old one has "SQL_Latin1_General_CP1_CI_AS". After some more researching and reading I found that I needed to reinstall sql on the new one with latin general. But I have to say that it seems strange if it really is so that that is the only way to get around this.
Anyway after instaling it ones again,the sql now has "Latin1_General_CI_AS". But I still get the same error. Running:
select SERVERPROPERTY ('collation')select convert(sysname,DatabasePropertyEx('staging','Collation'))
returns:
Latin1_General_CI_AS
SQL_Latin1_General_CP1_CI_AS
I assume, sadly, that these are not compatible, or???? If so, I then assume that I need to reinstal the sql once again????
Any one got work arounds? Are my assumptios above correct? I appriciate all input. Thanks in advance.
Regards
Dan
May 26, 2005 at 5:43 pm
I think your problem may be that the database you detatched from the old server has the old collation carried along with it when you attach it. In older releases, every database on the server had to have the server's collation, but in sql server 2000 you could build objects with different collations than the server default. So you could detach a database from a Norwegian server, where it ahd a Norwegian collation, and attach it to a server with the default collation, and the process would work just fine until you ran certain types of sql. The only way I know to get around this is to either specify the collation in all your SQL (not a very effective method) or to copy all the data out, rebuild the database structures without specifying a collation and then copy the data back in.
I hope someone else has a simpler solution for you.
May 30, 2005 at 5:12 pm
There is not a simple solution that I know of. The only way that I know of to get rid of the old collation is to bcp all of your data (from every table) into flat files (I like to use the native option for this) and then reimport the data into a new database created on the new server with the new collation. When the data in bcped out (into a flat file) it has not collation, so when you bcp it back in you get the new collation at that time.
June 1, 2005 at 8:02 am
See
http://support.microsoft.com/default.aspx?scid=kb;en-us;325335
I've chanegd a lot of my DB collations this way to standardise them
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply