Changing database collation

I need to copy a database from one server to another. In the process of
copying the database I would like to change the collation from
Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
I have tried using the SSIS Export Wizard but it had difficulty in
transfering views and would not copy any stored procedures. I then tried
Restoring a backup of the source database onto the destingation database. I
tried using the "alter database statement" to change the collation but this
failed due to dependencies on the database collation.
Is there a good method for copying a database and changing the collation?Loren,
I have never personally done this so perhaps there is room for some
conjecture...
I would start with a logical method.
1. In SSIS I would use the Transfer database wizard which gives me source
and destination.
2. The next step of the SSIS package would be an execute SQL task and I
would Execute the Alter database command with the collate option.
Collate
http://technet.microsoft.com/en-us/...y/ms184391.aspx
Alter Database
http://msdn2.microsoft.com/en-us/library/aa275464(SQL.80).aspx
Hope that helps...
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>I need to copy a database from one server to another. In the process of
>copying the database I would like to change the collation from
>Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
> I have tried using the SSIS Export Wizard but it had difficulty in
> transfering views and would not copy any stored procedures. I then tried
> Restoring a backup of the source database onto the destingation database.
> I tried using the "alter database statement" to change the collation but
> this failed due to dependencies on the database collation.
> Is there a good method for copying a database and changing the collation?
>|||Hi Loren
There is no method to change the collation to an existing SQL Server
database. Using ALTER DATABASE, you can only chage the default collation,
used to create new object, but existing objects maintain the previous
collation.
http://technet.microsoft.com/en-us/...y/ms175835.aspx
Try to create a new database, using the desired collation, and create the
schema using the desired collation again. Then, you can populate tables from
your source database using SSIS.
Regards,
GuilleSQL
http://www.guillesql.es
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>I need to copy a database from one server to another. In the process of
>copying the database I would like to change the collation from
>Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
> I have tried using the SSIS Export Wizard but it had difficulty in
> transfering views and would not copy any stored procedures. I then tried
> Restoring a backup of the source database onto the destingation database.
> I tried using the "alter database statement" to change the collation but
> this failed due to dependencies on the database collation.
> Is there a good method for copying a database and changing the collation?
>|||I see what you are saying...
"You can change the collation of any new objects that are created in a user
database by using the COLLATE clause of the ALTER DATABASE statement. This
statement does not change the collation of the columns in any existing
user-defined tables. These can be changed by using the COLLATE clause of
ALTER TABLE. "
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
<GuilleSQL> wrote in message news:eohI5$mTIHA.4696@.TK2MSFTNGP05.phx.gbl...
> Hi Loren
> There is no method to change the collation to an existing SQL Server
> database. Using ALTER DATABASE, you can only chage the default collation,
> used to create new object, but existing objects maintain the previous
> collation.
> http://technet.microsoft.com/en-us/...y/ms175835.aspx
> Try to create a new database, using the desired collation, and create the
> schema using the desired collation again. Then, you can populate tables
> from your source database using SSIS.
> Regards,
> GuilleSQL
> http://www.guillesql.es
>
> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>