Change the collation on the user DB

Changed the master db collation by using the rebuildm utility from
SQL_LATIN1_GENERAL_SP1_CI_AS to Latin1-General, Binary. How do I change the
user dbs now? Issue is critical. Please help. GeorgeALTER DATABASE. But that doesn't change collation for the existing tables. F
or that you use ALTER
TABLE ... ALTER COLUMN. Yes, a lot of work, and there are some other things
you need to do (like
dropping indexes), see Books Online. You can search the archives to see if a
nyone has posted a
script to semi-automate this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"George" <George@.discussions.microsoft.com> wrote in message
news:849B0CB4-1487-441A-B91B-41DA1C87C37A@.microsoft.com...
> Changed the master db collation by using the rebuildm utility from
> SQL_LATIN1_GENERAL_SP1_CI_AS to Latin1-General, Binary. How do I change th
e
> user dbs now? Issue is critical. Please help. George|||Tibor,
is it possible to dump the database to a flat file, delete the existing db,
create a new db with the correct collation and then import data back in? I
tried backup, delete resotore but it changed back to what the database was
when it was backed up. Any help on this is greatly appreciated. George
"Tibor Karaszi" wrote:

> ALTER DATABASE. But that doesn't change collation for the existing tables.
For that you use ALTER
> TABLE ... ALTER COLUMN. Yes, a lot of work, and there are some other thing
s you need to do (like
> dropping indexes), see Books Online. You can search the archives to see if
anyone has posted a
> script to semi-automate this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:849B0CB4-1487-441A-B91B-41DA1C87C37A@.microsoft.com...
>|||> is it possible to dump the database to a flat file
There is no such direct feature in SQL Server. But you can use some of the t
ransfer tools (DTS etc),
and depending on which method you chose, you script your objects first and t
hen re-create them
before the import.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"George" <George@.discussions.microsoft.com> wrote in message
news:F6F90F12-1FC5-450B-8724-DD3D5DCA5C71@.microsoft.com...[vbcol=seagreen]
> Tibor,
> is it possible to dump the database to a flat file, delete the existing db
,
> create a new db with the correct collation and then import data back in? I
> tried backup, delete resotore but it changed back to what the database was
> when it was backed up. Any help on this is greatly appreciated. George
> "Tibor Karaszi" wrote:
>|||Had to call Microsoft. If you change only one db (user db) then follow these
instructions:
Transfer a database from one collation in SQL Server 2000 to a different
collation in SQL Server 2000
To transfer a database from one collation in SQL Server 2000 to a different
collation in SQL Server 2000, follow these steps:
1. Back up the source database.
2. Note if any columns use the COLLATE clause.
3. Create a new database on the destination server with the appropriate
collation.
4. If no columns use the COLLATE clause, use DTS to transfer the data (Right
click on the database, Export data) to the destination server. To do so,
enable the Use Collation option (choose third option when choosind type of
transfer which is all data) for code page translation and to transfer the
data to the new collation on the destination database. If any columns use th
e
COLLATE clause, follow these steps: a. Generate scripts for all the objects
(not including the indexes, the triggers, the primary keys, the foreign keys
,
the default settings, and the constraints). Additionally, make sure that you
enable the Only script 7.0 compatible features option to remove the COLLATE
clause from the script.
Note When you use the Only script 7.0 compatible features option, you can
change the collation. However, any new SQL Server 2000 options (including
user-defined functions, extended properties, the INSTEAD OF trigger, and
indexes on views) will not be considered when the scripts are generated.
b. Run the scripts from step a on the destination database to create the
objects with the destination database collation.
c. Use DTS to transfer only the data from the source database.
d. After the data is successfully transferred, generate scripts for all the
constraints, foreign keys, primary keys, and indexes from the source
database.
e. Run the scripts from step d on the destination database.
George
"Tibor Karaszi" wrote:

> There is no such direct feature in SQL Server. But you can use some of the
transfer tools (DTS etc),
> and depending on which method you chose, you script your objects first and
then re-create them
> before the import.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:F6F90F12-1FC5-450B-8724-DD3D5DCA5C71@.microsoft.com...
>