Change collation all db tables and db

If I need to copy a db to another server but I need to chnage the new db
collation and the collation of all the columns, is there a better way than:
1. Create new DB with desired collation
2. Create table scripts without collation option or constraints; run them
3. Use BCP or DTS to move the data
4. Script contraints, stored procs, views....; run them
I ran DTS object move but that always seems to copy the column collation.
Ideally I would like to somehow force a backup or restore to use a given
collation.
--
-Peterhttp://groups.google.co.uk/groups?q=bart+duncan+collate+database+jacco&hl=en&lr=&group=microsoft.public.sqlserver.*&selm=%23%24c1OFoaEHA.1652%40TK2MSFTNGP09.phx.gbl&rnum=2
--
Jacco Schalkwijk
SQL Server MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:62681A24-84DC-4E41-A1B0-FD85D8F93AB5@.microsoft.com...
> If I need to copy a db to another server but I need to chnage the new db
> collation and the collation of all the columns, is there a better way
> than:
> 1. Create new DB with desired collation
> 2. Create table scripts without collation option or constraints; run them
> 3. Use BCP or DTS to move the data
> 4. Script contraints, stored procs, views....; run them
> I ran DTS object move but that always seems to copy the column collation.
> Ideally I would like to somehow force a backup or restore to use a given
> collation.
> --
> -Peter|||Thanks Jacco. I guess there is no easy way to change collation.
-Peter
"Jacco Schalkwijk" wrote:
> http://groups.google.co.uk/groups?q=bart+duncan+collate+database+jacco&hl=en&lr=&group=microsoft.public.sqlserver.*&selm=%23%24c1OFoaEHA.1652%40TK2MSFTNGP09.phx.gbl&rnum=2
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Peter L." <PeterL@.discussions.microsoft.com> wrote in message
> news:62681A24-84DC-4E41-A1B0-FD85D8F93AB5@.microsoft.com...
> > If I need to copy a db to another server but I need to chnage the new db
> > collation and the collation of all the columns, is there a better way
> > than:
> >
> > 1. Create new DB with desired collation
> > 2. Create table scripts without collation option or constraints; run them
> > 3. Use BCP or DTS to move the data
> > 4. Script contraints, stored procs, views....; run them
> >
> > I ran DTS object move but that always seems to copy the column collation.
> >
> > Ideally I would like to somehow force a backup or restore to use a given
> > collation.
> >
> > --
> > -Peter
>
>