Collation problem - urgent help

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_General_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_General_CP1_CS_AS" in the equal to operation

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

Tnanks in advance.Nasir (nmajeed@.prosrm.com) writes:

Quote:

Originally Posted by

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_General_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_General_CP1_CS_AS" in the equal to operation
>
WHat sthe best way to resolve these problems...would 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_General_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@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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.. .

Quote:

Originally Posted by

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_General_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_General_CP1_CS_AS" in the equal to operation
>
>
>
WHat sthe best way to resolve these problems...would bcp in/out fix it?
>
>
>
Tnanks in advance.
>
>

|||Nasir (nmajeed@.prosrm.com) writes:

Quote:

Originally Posted by

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(schema_name(schema_id)) + '.' +
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.

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

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@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx