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...
>
|||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...
>
>