Changing SQL Server Collation

Hi
Has anyone successfully changed the Collation type for a single user
database without having to use Rebuildm.exe and/or the ALTER DATABASE
statement as I`ve tried the latter and if there are CK`s you cannot.
Thanks in advance.
Sad guy
Using ALTER DATABASE only changes the default collation for that database.
To fully change the collation you'd have to alter every column, every
character-based user-defined type, and possibly other objects (I can't
remember offhand). The easiest method I've come up with for changing the
collation is:
A) Script the entire database using Enterprise Manager
B) Edit the script and change all of the collation designators to whatever
you want
C) Create a new database using the script
D) Use DTS to transfer the data out of the old database and into the new
database
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"ANON" <ANON@.discussions.microsoft.com> wrote in message
news:EFB1A3D2-6E98-4C81-82D1-2FF10EAC7BBF@.microsoft.com...
> Hi
> Has anyone successfully changed the Collation type for a single user
> database without having to use Rebuildm.exe and/or the ALTER DATABASE
> statement as I`ve tried the latter and if there are CK`s you cannot.
> Thanks in advance.
> Sad guy
|||Hi
Once a tables are created in a DB, ALTER DATABASE does not change those
column's collations.
What are you trying to do?
Regards
Mike
"ANON" wrote:

> Hi
> Has anyone successfully changed the Collation type for a single user
> database without having to use Rebuildm.exe and/or the ALTER DATABASE
> statement as I`ve tried the latter and if there are CK`s you cannot.
> Thanks in advance.
> Sad guy

Changing SQL Server Collation

Hi
Has anyone successfully changed the Collation type for a single user
database without having to use Rebuildm.exe and/or the ALTER DATABASE
statement as I`ve tried the latter and if there are CK`s you cannot.
Thanks in advance.
Sad guyUsing ALTER DATABASE only changes the default collation for that database.
To fully change the collation you'd have to alter every column, every
character-based user-defined type, and possibly other objects (I can't
remember offhand). The easiest method I've come up with for changing the
collation is:
A) Script the entire database using Enterprise Manager
B) Edit the script and change all of the collation designators to whatever
you want
C) Create a new database using the script
D) Use DTS to transfer the data out of the old database and into the new
database
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"ANON" <ANON@.discussions.microsoft.com> wrote in message
news:EFB1A3D2-6E98-4C81-82D1-2FF10EAC7BBF@.microsoft.com...
> Hi
> Has anyone successfully changed the Collation type for a single user
> database without having to use Rebuildm.exe and/or the ALTER DATABASE
> statement as I`ve tried the latter and if there are CK`s you cannot.
> Thanks in advance.
> Sad guy|||Hi
Once a tables are created in a DB, ALTER DATABASE does not change those
column's collations.
What are you trying to do?
Regards
Mike
"ANON" wrote:

> Hi
> Has anyone successfully changed the Collation type for a single user
> database without having to use Rebuildm.exe and/or the ALTER DATABASE
> statement as I`ve tried the latter and if there are CK`s you cannot.
> Thanks in advance.
> Sad guy

Changing SQL Server Collation

Hi
Has anyone successfully changed the Collation type for a single user
database without having to use Rebuildm.exe and/or the ALTER DATABASE
statement as I`ve tried the latter and if there are CK`s you cannot.
Thanks in advance.
Sad guyUsing ALTER DATABASE only changes the default collation for that database.
To fully change the collation you'd have to alter every column, every
character-based user-defined type, and possibly other objects (I can't
remember offhand). The easiest method I've come up with for changing the
collation is:
A) Script the entire database using Enterprise Manager
B) Edit the script and change all of the collation designators to whatever
you want
C) Create a new database using the script
D) Use DTS to transfer the data out of the old database and into the new
database
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"ANON" <ANON@.discussions.microsoft.com> wrote in message
news:EFB1A3D2-6E98-4C81-82D1-2FF10EAC7BBF@.microsoft.com...
> Hi
> Has anyone successfully changed the Collation type for a single user
> database without having to use Rebuildm.exe and/or the ALTER DATABASE
> statement as I`ve tried the latter and if there are CK`s you cannot.
> Thanks in advance.
> Sad guy|||Hi
Once a tables are created in a DB, ALTER DATABASE does not change those
column's collations.
What are you trying to do?
Regards
Mike
"ANON" wrote:
> Hi
> Has anyone successfully changed the Collation type for a single user
> database without having to use Rebuildm.exe and/or the ALTER DATABASE
> statement as I`ve tried the latter and if there are CK`s you cannot.
> Thanks in advance.
> Sad guy

Changing server collation

Hello there
I've tried to change collation of my server using rebuildm
It gave me an error: Rebuild master failed with error: -1
The database was created succeffuly, but the collation hasn't changed.
where can be the problem?Roy
In that case I'd resinstall server
You change a collation per database/table/column
SELECT
'ALTER TABLE ' + TABLE_NAME +
' ALTER COLUMN ' + COLUMN_NAME +
' ' + DATA_TYPE +' '+
CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ')
END
+' COLLATE Put here your colation '+
CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
WHEN 'No' THEN 'NOT NULL'
END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar')
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:u$jxwQFQGHA.3256@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I've tried to change collation of my server using rebuildm
> It gave me an error: Rebuild master failed with error: -1
> The database was created succeffuly, but the collation hasn't changed.
> where can be the problem?
>|||Hi Roy
To add to Uri's post, if rebuildm does not work first time I usually resort
to re-installing as the time to run rebuildm twice is probably more than a
full install.
You may want to set the new collation to default rather than a specific one,
this will pick up the database collation (so change it first). Using '[' and
']' around your object names may also be necessary.
You may also want to check out other posts such as http://tinyurl.com/429qa
John
"Roy Goldhammer" wrote:
> Hello there
> I've tried to change collation of my server using rebuildm
> It gave me an error: Rebuild master failed with error: -1
> The database was created succeffuly, but the collation hasn't changed.
> where can be the problem?
>
>|||Whell Uri
This procedure has errors due to collation conflict, which it was suppose to
fix.
I've solved it, but it doesn't work if the column has index, constraint or
primary key.
The only way to solve it, is or by changing it with EM if the amount of
cases is realy small, or use generate databases script, and on the query
anlyser remove all the collations.
Is there better way to do this?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ncWUmFQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Roy
> In that case I'd resinstall server
> You change a collation per database/table/column
> SELECT
> 'ALTER TABLE ' + TABLE_NAME +
> ' ALTER COLUMN ' + COLUMN_NAME +
> ' ' + DATA_TYPE +' '+
> CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
> THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
> ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ')
> END
> +' COLLATE Put here your colation '+
> CASE IS_NULLABLE
> WHEN 'YES' THEN 'NULL'
> WHEN 'No' THEN 'NOT NULL'
> END
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar')
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:u$jxwQFQGHA.3256@.TK2MSFTNGP15.phx.gbl...
>> Hello there
>> I've tried to change collation of my server using rebuildm
>> It gave me an error: Rebuild master failed with error: -1
>> The database was created succeffuly, but the collation hasn't changed.
>> where can be the problem?
>|||Hi Roy
If you profiled what EM was doing you would probably see that it droped
constraints and indexes before issing the same statement. If your source code
is in version control this would not be a big issue. You could use DMO or EM
to script these or the INFORMATION_SCHEMA.KEY_COLUMN_USAGE and sysindexes
tables to generate a script using T-SQL.
John
"Roy Goldhammer" wrote:
> Whell Uri
> This procedure has errors due to collation conflict, which it was suppose to
> fix.
> I've solved it, but it doesn't work if the column has index, constraint or
> primary key.
> The only way to solve it, is or by changing it with EM if the amount of
> cases is realy small, or use generate databases script, and on the query
> anlyser remove all the collations.
> Is there better way to do this?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23ncWUmFQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> > Roy
> > In that case I'd resinstall server
> > You change a collation per database/table/column
> >
> > SELECT
> > 'ALTER TABLE ' + TABLE_NAME +
> > ' ALTER COLUMN ' + COLUMN_NAME +
> > ' ' + DATA_TYPE +' '+
> > CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
> > THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
> > ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ')
> > END
> > +' COLLATE Put here your colation '+
> > CASE IS_NULLABLE
> > WHEN 'YES' THEN 'NULL'
> > WHEN 'No' THEN 'NOT NULL'
> > END
> >
> > FROM INFORMATION_SCHEMA.COLUMNS
> > WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar')
> >
> >
> >
> > "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> > news:u$jxwQFQGHA.3256@.TK2MSFTNGP15.phx.gbl...
> >> Hello there
> >>
> >> I've tried to change collation of my server using rebuildm
> >>
> >> It gave me an error: Rebuild master failed with error: -1
> >>
> >> The database was created succeffuly, but the collation hasn't changed.
> >>
> >> where can be the problem?
> >>
> >
> >
>
>