Changing all existing tables collations in one step

If you're wanting to change the whole collation of your database then
(in SQL 2000) the best way is to script out the db, recreate it with
the new collation as default, then DTS your data across. I think that
if you changed it table by table then every new table you create will
then have to be changed as well.
Hopefully there's something in SQL 2005 that allows a complete db
collation change, as I expect most people have run into the issue of a
database being restored from a backup from a server that used a
different collation (I often found for some reason the collation got
set to LATIN1_GENERAL_CPL_CI_AS on peoples default install of SQL for
no apparent reason).Thank you.
"Will" wrote:

> If you're wanting to change the whole collation of your database then
> (in SQL 2000) the best way is to script out the db, recreate it with
> the new collation as default, then DTS your data across. I think that
> if you changed it table by table then every new table you create will
> then have to be changed as well.
>
> Hopefully there's something in SQL 2005 that allows a complete db
> collation change, as I expect most people have run into the issue of a
> database being restored from a backup from a server that used a
> different collation (I often found for some reason the collation got
> set to LATIN1_GENERAL_CPL_CI_AS on peoples default install of SQL for
> no apparent reason).
>

Changing all existing tables collations in one step

I can change table collation first table column by column
then second table and so on (Alter table table_name
alter column column_name COLLATE < collation_name> ).
How can I change all existing tables collations in one step ?
Where can I find some program in Transact-SQL or some procedure to solve
this problem ?Am Tue, 11 Apr 2006 20:31:01 -0700 schrieb Alur:

> I can change table collation first table column by column
> then second table and so on (Alter table table_name
> alter column column_name COLLATE < collation_name> ).
> How can I change all existing tables collations in one step ?
> Where can I find some program in Transact-SQL or some procedure to solve
> this problem ?
You can change the collation only column by column, not in one step.
So you can read the info you need:
--
select so.name as TableName, sc.name as ColName, sc.collation from
syscolumns sc left join sysobjects so on so.id = sc.id
where IsNull(sc.name,'') > '' and so.xtype = 'U'
and sc.collation is not NULL
--
create a cursor, loop through this query and do the ALTER TABLE where the
collation is not like you need.
bye, Helmut|||Thank you.
"helmut woess" wrote:

> Am Tue, 11 Apr 2006 20:31:01 -0700 schrieb Alur:
>
> You can change the collation only column by column, not in one step.
> So you can read the info you need:
> --
> select so.name as TableName, sc.name as ColName, sc.collation from
> syscolumns sc left join sysobjects so on so.id = sc.id
> where IsNull(sc.name,'') > '' and so.xtype = 'U'
> and sc.collation is not NULL
> --
> create a cursor, loop through this query and do the ALTER TABLE where the
> collation is not like you need.
> bye, Helmut
>

CHANGE THE PROVIDERS TO USE SQL SERVER INSTEAD OF SQL EXPRESS

In step one of this Blog ,what's this mean

Step 1: Create or obtain a blank SQL database instance

and how do i go about doing it

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

That means to get a SQL database which does not have the schema (tables, stored procedures, functions, etc.) for membership, so that you can useaspnet_regsql.exe to create the required schema.|||

I'm a newbie so can you explain what u mean by getting a schema

Can u explain for example where do i start to make the config in VWD or using one of the SQL DATABASE, and if so which one. My provider is running SQL 2000

If you can break it down like the first step to do . My objective is to run a asp .net page

But i cannot figure out how to get in to run on the server But it works on my local machine. but when i upload it... i get the error message 40 about remote connection ,and i was told because i have to config VWD2005 to run on SQL 2000

Thanks for your help

|||

Hi bigmike40, let's discuss it in this post:

http://forums.asp.net/thread/1362712.aspx

|||

Ok