Hi db masters,
We are trying to consolidate 2 servers and move databases with sql login
users from one sqlsrv1 to another sqlsrv2. We are using database copy wizard
from management studio. No error messages when moving but the moved users are
disabled and we get "cannot resolve collation conflict" when trying to access
them. The sqlsrv1 runs sql 2000 with server collation Danish_Norwegian_CI_AS
and the sqlsrv2 runs sql 2005 with server collation
SQL_Latin1_General_CP1_CI_AS. A lot of the databases and sql logins on
sqlsrv1 are application generated and cannot be changed. We have full control
over the databases on sqlsrv2 and can change the server collation if needed
and recreate and import user data. On the rest of our sql servers we have
standardized on server collation Latin1_General_CI_AS. Should we change the
server collation on sqlsrv2 to our standard Latin1_General_CI_AS or match the
sqlsrv1 Danish_Norwegian_CI_AS collation? Or any other quick fix?
--
Best regards,
Vidar Hegre, Norway

"VidarHe" <VidarHe@discussions.microsoft.com> wrote in message
news:58DA1A86-7B1A-4972-9951-FD8EA66A2AA3@microsoft.com...
> Hi db masters,
> We are trying to consolidate 2 servers and move databases with sql login
> users from one sqlsrv1 to another sqlsrv2. We are using database copy
> wizard
> from management studio. No error messages when moving but the moved users
> are
> disabled and we get "cannot resolve collation conflict" when trying to
> access
> them. The sqlsrv1 runs sql 2000 with server collation
> Danish_Norwegian_CI_AS
> and the sqlsrv2 runs sql 2005 with server collation
> SQL_Latin1_General_CP1_CI_AS. A lot of the databases and sql logins on
> sqlsrv1 are application generated and cannot be changed. We have full
> control
> over the databases on sqlsrv2 and can change the server collation if
> needed
> and recreate and import user data. On the rest of our sql servers we have
> standardized on server collation Latin1_General_CI_AS. Should we change
> the
> server collation on sqlsrv2 to our standard Latin1_General_CI_AS or match
> the
> sqlsrv1 Danish_Norwegian_CI_AS collation? Or any other quick fix?
> --
> Best regards,
> Vidar Hegre, Norway

Hi

The simplest solution seems to be changing sqlsrv2 collation to be
Danish_Norwegian_CI_AS (a re-install may be the easiest way to do this)
and then you can copy the databases (backup/restore) and map the orphaned
users to logins. I assume that you don't have any other databases on
server2, if you did then these may have collation conflict issues as well if
the instance collation was changed. If there is any interaction between
server2 and the other servers, you may have to force the collations. If you
decide to standardise all the servers then you will need to change the
database collation and the collation of all columns (char, varchar etc) that
are not Latin1_General_CI_AS to be Latin1_General_CI_AS.

John

Thanks for the clarification. We will reinstall sqlsrv2 with sql 2005 and use
the Danish_Norwegian as server collation.
Br, Vidar