Hi Martin,

To check the collation order just right-click on the server in the Enterprise manager and choose Properties on the 1st property page you should have a label called Collation Order. Compare the collation on the 2 servers. You should also right-click on the db and choose properties to check that you haven't choosen another collation than server default.

I don't think you can change the collation on an existing db, but I'm not shure. You can't change it on a server.

You should be able to use detach/attach method to transfer the db. You can have different collation on the db than the server, at least in SQL2000.

If that won't work you should be able to use the DTS Export/Import Wizard to transfer tables and data between to servers and db:s. The wizard might put the collation from the source db on every varchar field in the destination db. You can have different collation order set on table fileds than the one set in the db.

>>Hi all!
>>
>>I installed a new SQL Server 2000 and restored the tables from the old SQL 2000 Server to the new one. Upon running the the following...
>>
>>insert DYNAMICS..RecoveryMaster (name, password)
>> select name, password from master..sysxlogins
>> where name in (select USERID from DYNAMICS..SY01400)
>> and name not in ('sa', 'DYNSA')
>>
>>I get the error: Cannot resolve collation conflict for equal to operation.
>>
>>I know there is nothing wrong with the script, cause it executes perfectly on the old server. I am thinking that there is something wrong with the new SQL Server installation. How do I determine the problem and ofcourse... how do I fix it?
>>
>>Thanx! a lot in advance 8 )
>>
>>Martin
>
>>Hi Martin,
>
>>Do you have the same collation order on the name fields in table sysxlogins >and sy01400? You cannot join 2 fields with different collation order.
>
>>Try create a copy of sy01400 with the right collation and run the query >again.
>>Magnus Nordin
>>VFP MCP
>
>
>Magnus,
>
>Good morning! Thanx for the reply. I had the same idea that the two servers might have different collation order. Is there a way I can verify this? Like is there an SQL syntax that will return the collation order? And is there a way for me to change it? I have like 5k+ tables on each database and i'd hate to recreate each table manually 8 )
>
>Would it be possible for me to just detach them... change the collation order (how to do this is the question)... and the attach them back again?
>
>Thanx! in advance 8 )
>
>Martin