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/library/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?
>
|||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/library/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...
>
|||Use a scripting tool like Redgate SQL Compare to generate scripts for your
existing database objects. You could do it using the free 14 day trial then
show your boss how much time(money) it saved and get him or her to buy it.
Create your new database with the collation set as you want it and then
build your tables, stored procedures views etc. from your script. You can
now run SSIS to transfer data from one database to another - although in
this particular case, if it is a one off, not a regular job, T-SQL bulk
insert may be quicker and easier.
Nigel Ainscoe
"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?
>