Collation problem - urgent help

[Nasir]

What is the best way to resolve these collation errors:

My server is installed as - Latin1_General_ CS_AS, but one of the important
50GB databases migrated from another server has -
SQL_Latin1_Gene ral_CP1_CS_AS, and we are getting errors:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General _CS_AS" and
"SQL_Latin1_Gen eral_CP1_CS_AS" in the equal to operation



WHat sthe best way to resolve these problems...woul d bcp in/out fix it?



Tnanks in advance.

[Erland Sommarskog]

Re: Collation problem - urgent help

Nasir (nmajeed@prosrm .com) writes:

What is the best way to resolve these collation errors:
>
My server is installed as - Latin1_General_ CS_AS, but one of the
important 50GB databases migrated from another server has -
SQL_Latin1_Gene ral_CP1_CS_AS, and we are getting errors:
>
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General _CS_AS" and
"SQL_Latin1_Gen eral_CP1_CS_AS" in the equal to operation
>
WHat sthe best way to resolve these problems...woul d bcp in/out fix it?

You would have to rebuild the database from scripts in such case.

The quickest and easiest fix may be to simply install a second instance
with SQL_Latin1_Gene ral_CP1_CS_AS as the server collation.

You can also run:

SELECT 'ALTER TABLE ' + o.name + ' ALTER COLUMN ' + c.name etc

and cut and paste the result into a query window. Unforunately, it is not
that easy, if columns are indexed, or referenced by foreign keys, so
indexes have to be dropped and reapplied, same goes for constraints.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Nasir]

Re: Collation problem - urgent help

Is there any easy way to export out all objects and data, so it can be
exported into any collation of SQL server.

Also, if I create multiple users in a databases, that they can have
independent schemas; can they be backed up separately and restores
separately to the same database.

Where did DTS go. It was nice to load or export different formats of data?

TIA

"Nasir" <nmajeed@prosrm .comwrote in message
news:44e9f86a$0 $1005$39cecf19@ news.twtelecom. net...

What is the best way to resolve these collation errors:
>
My server is installed as - Latin1_General_ CS_AS, but one of the
important 50GB databases migrated from another server has -
SQL_Latin1_Gene ral_CP1_CS_AS, and we are getting errors:
>
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General _CS_AS" and
"SQL_Latin1_Gen eral_CP1_CS_AS" in the equal to operation
>
>
>
WHat sthe best way to resolve these problems...woul d bcp in/out fix it?
>
>
>
Tnanks in advance.
>
>

[Erland Sommarskog]

Re: Collation problem - urgent help

Nasir (nmajeed@prosrm .com) writes:

Is there any easy way to export out all objects and data, so it can be
exported into any collation of SQL server.

SELECT 'bcp yourdb.' + quotename(schem a_name(schema_i d)) + '.' +
quotename(name) + ' out "' + name + '.bcp" ' +
'-T -n -C RAW'
FROM sys.objects
WHERE type = 'U'

Copy and paste into BAT file. To copy back, you need to add the -E option
for tables with identity columns. That is left as an exercise for the
reader. :-)

I believe the -C RAW option should prevent character conversions, but
you have to play around with that.

Also, if I create multiple users in a databases, that they can have
independent schemas; can they be backed up separately and restores
separately to the same database.

Particularly, in SQL 2005, users don't have to have a schema at all.
Or they can own several.

No, you cannot backup a schema on its own. But you can backup on filegroup
level, I believe.

Where did DTS go. It was nice to load or export different formats of data?

DTS was succeeded by SQL Server Integration Services in SQL 2005. I
know nothing about neither.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx