Hello there
On one of my servers diffrent collation is being declered.
I need to change it to SQL_LATIN1_GENERAL_SP1_CI_AS
I used rebuildm program for that. and it failed
what i need to do on the regional settings for allowing this type of
collation?Roy,
what do you mean with rebuildm.exe failed. Did you get any errors or do
you just mean that the collation of your server hasn't changed? By the
way be aware of the fact that rebuildm.exe only changes the collation
of the master, model, msdb and tempdb. Your user databases you have to
convert "manually".
Markus|||How do you change the user databases collation manualy? I have changed the
master db by using rebuildm.exe.
George
"MarkusB" wrote:
> Roy,
> what do you mean with rebuildm.exe failed. Did you get any errors or do
> you just mean that the collation of your server hasn't changed? By the
> way be aware of the fact that rebuildm.exe only changes the collation
> of the master, model, msdb and tempdb. Your user databases you have to
> convert "manually".
> Markus
>sql
Showing posts with label rebuildm. Show all posts
Showing posts with label rebuildm. Show all posts
Tuesday, March 27, 2012
Changing collation on server
Hello there
On one of my servers diffrent collation is being declered.
I need to change it to SQL_LATIN1_GENERAL_SP1_CI_AS
I used rebuildm program for that. and it failed
what i need to do on the regional settings for allowing this type of
collation?Roy,
what do you mean with rebuildm.exe failed. Did you get any errors or do
you just mean that the collation of your server hasn't changed? By the
way be aware of the fact that rebuildm.exe only changes the collation
of the master, model, msdb and tempdb. Your user databases you have to
convert "manually".
Markus|||How do you change the user databases collation manualy? I have changed the
master db by using rebuildm.exe.
George
"MarkusB" wrote:
> Roy,
> what do you mean with rebuildm.exe failed. Did you get any errors or do
> you just mean that the collation of your server hasn't changed? By the
> way be aware of the fact that rebuildm.exe only changes the collation
> of the master, model, msdb and tempdb. Your user databases you have to
> convert "manually".
> Markus
>
On one of my servers diffrent collation is being declered.
I need to change it to SQL_LATIN1_GENERAL_SP1_CI_AS
I used rebuildm program for that. and it failed
what i need to do on the regional settings for allowing this type of
collation?Roy,
what do you mean with rebuildm.exe failed. Did you get any errors or do
you just mean that the collation of your server hasn't changed? By the
way be aware of the fact that rebuildm.exe only changes the collation
of the master, model, msdb and tempdb. Your user databases you have to
convert "manually".
Markus|||How do you change the user databases collation manualy? I have changed the
master db by using rebuildm.exe.
George
"MarkusB" wrote:
> Roy,
> what do you mean with rebuildm.exe failed. Did you get any errors or do
> you just mean that the collation of your server hasn't changed? By the
> way be aware of the fact that rebuildm.exe only changes the collation
> of the master, model, msdb and tempdb. Your user databases you have to
> convert "manually".
> Markus
>
Changing collation on server
Hello there
On one of my servers diffrent collation is being declered.
I need to change it to SQL_LATIN1_GENERAL_SP1_CI_AS
I used rebuildm program for that. and it failed
what i need to do on the regional settings for allowing this type of
collation?
Roy,
what do you mean with rebuildm.exe failed. Did you get any errors or do
you just mean that the collation of your server hasn't changed? By the
way be aware of the fact that rebuildm.exe only changes the collation
of the master, model, msdb and tempdb. Your user databases you have to
convert "manually".
Markus
On one of my servers diffrent collation is being declered.
I need to change it to SQL_LATIN1_GENERAL_SP1_CI_AS
I used rebuildm program for that. and it failed
what i need to do on the regional settings for allowing this type of
collation?
Roy,
what do you mean with rebuildm.exe failed. Did you get any errors or do
you just mean that the collation of your server hasn't changed? By the
way be aware of the fact that rebuildm.exe only changes the collation
of the master, model, msdb and tempdb. Your user databases you have to
convert "manually".
Markus
Wednesday, March 7, 2012
Change the collation on the user DB
Changed the master db collation by using the rebuildm utility from
SQL_LATIN1_GENERAL_SP1_CI_AS to Latin1-General, Binary. How do I change the
user dbs now? Issue is critical. Please help. GeorgeALTER DATABASE. But that doesn't change collation for the existing tables. For that you use ALTER
TABLE ... ALTER COLUMN. Yes, a lot of work, and there are some other things you need to do (like
dropping indexes), see Books Online. You can search the archives to see if anyone has posted a
script to semi-automate this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"George" <George@.discussions.microsoft.com> wrote in message
news:849B0CB4-1487-441A-B91B-41DA1C87C37A@.microsoft.com...
> Changed the master db collation by using the rebuildm utility from
> SQL_LATIN1_GENERAL_SP1_CI_AS to Latin1-General, Binary. How do I change the
> user dbs now? Issue is critical. Please help. George|||Tibor,
is it possible to dump the database to a flat file, delete the existing db,
create a new db with the correct collation and then import data back in? I
tried backup, delete resotore but it changed back to what the database was
when it was backed up. Any help on this is greatly appreciated. George
"Tibor Karaszi" wrote:
> ALTER DATABASE. But that doesn't change collation for the existing tables. For that you use ALTER
> TABLE ... ALTER COLUMN. Yes, a lot of work, and there are some other things you need to do (like
> dropping indexes), see Books Online. You can search the archives to see if anyone has posted a
> script to semi-automate this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:849B0CB4-1487-441A-B91B-41DA1C87C37A@.microsoft.com...
> > Changed the master db collation by using the rebuildm utility from
> > SQL_LATIN1_GENERAL_SP1_CI_AS to Latin1-General, Binary. How do I change the
> > user dbs now? Issue is critical. Please help. George
>|||> is it possible to dump the database to a flat file
There is no such direct feature in SQL Server. But you can use some of the transfer tools (DTS etc),
and depending on which method you chose, you script your objects first and then re-create them
before the import.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"George" <George@.discussions.microsoft.com> wrote in message
news:F6F90F12-1FC5-450B-8724-DD3D5DCA5C71@.microsoft.com...
> Tibor,
> is it possible to dump the database to a flat file, delete the existing db,
> create a new db with the correct collation and then import data back in? I
> tried backup, delete resotore but it changed back to what the database was
> when it was backed up. Any help on this is greatly appreciated. George
> "Tibor Karaszi" wrote:
>> ALTER DATABASE. But that doesn't change collation for the existing tables. For that you use ALTER
>> TABLE ... ALTER COLUMN. Yes, a lot of work, and there are some other things you need to do (like
>> dropping indexes), see Books Online. You can search the archives to see if anyone has posted a
>> script to semi-automate this.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "George" <George@.discussions.microsoft.com> wrote in message
>> news:849B0CB4-1487-441A-B91B-41DA1C87C37A@.microsoft.com...
>> > Changed the master db collation by using the rebuildm utility from
>> > SQL_LATIN1_GENERAL_SP1_CI_AS to Latin1-General, Binary. How do I change the
>> > user dbs now? Issue is critical. Please help. George
>>|||Had to call Microsoft. If you change only one db (user db) then follow these
instructions:
Transfer a database from one collation in SQL Server 2000 to a different
collation in SQL Server 2000
To transfer a database from one collation in SQL Server 2000 to a different
collation in SQL Server 2000, follow these steps:
1. Back up the source database.
2. Note if any columns use the COLLATE clause.
3. Create a new database on the destination server with the appropriate
collation.
4. If no columns use the COLLATE clause, use DTS to transfer the data (Right
click on the database, Export data) to the destination server. To do so,
enable the Use Collation option (choose third option when choosind type of
transfer which is all data) for code page translation and to transfer the
data to the new collation on the destination database. If any columns use the
COLLATE clause, follow these steps: a. Generate scripts for all the objects
(not including the indexes, the triggers, the primary keys, the foreign keys,
the default settings, and the constraints). Additionally, make sure that you
enable the Only script 7.0 compatible features option to remove the COLLATE
clause from the script.
Note When you use the Only script 7.0 compatible features option, you can
change the collation. However, any new SQL Server 2000 options (including
user-defined functions, extended properties, the INSTEAD OF trigger, and
indexes on views) will not be considered when the scripts are generated.
b. Run the scripts from step a on the destination database to create the
objects with the destination database collation.
c. Use DTS to transfer only the data from the source database.
d. After the data is successfully transferred, generate scripts for all the
constraints, foreign keys, primary keys, and indexes from the source
database.
e. Run the scripts from step d on the destination database.
George
"Tibor Karaszi" wrote:
> > is it possible to dump the database to a flat file
> There is no such direct feature in SQL Server. But you can use some of the transfer tools (DTS etc),
> and depending on which method you chose, you script your objects first and then re-create them
> before the import.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:F6F90F12-1FC5-450B-8724-DD3D5DCA5C71@.microsoft.com...
> > Tibor,
> >
> > is it possible to dump the database to a flat file, delete the existing db,
> > create a new db with the correct collation and then import data back in? I
> > tried backup, delete resotore but it changed back to what the database was
> > when it was backed up. Any help on this is greatly appreciated. George
> >
> > "Tibor Karaszi" wrote:
> >
> >> ALTER DATABASE. But that doesn't change collation for the existing tables. For that you use ALTER
> >> TABLE ... ALTER COLUMN. Yes, a lot of work, and there are some other things you need to do (like
> >> dropping indexes), see Books Online. You can search the archives to see if anyone has posted a
> >> script to semi-automate this.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "George" <George@.discussions.microsoft.com> wrote in message
> >> news:849B0CB4-1487-441A-B91B-41DA1C87C37A@.microsoft.com...
> >> > Changed the master db collation by using the rebuildm utility from
> >> > SQL_LATIN1_GENERAL_SP1_CI_AS to Latin1-General, Binary. How do I change the
> >> > user dbs now? Issue is critical. Please help. George
> >>
> >>
>
SQL_LATIN1_GENERAL_SP1_CI_AS to Latin1-General, Binary. How do I change the
user dbs now? Issue is critical. Please help. GeorgeALTER DATABASE. But that doesn't change collation for the existing tables. For that you use ALTER
TABLE ... ALTER COLUMN. Yes, a lot of work, and there are some other things you need to do (like
dropping indexes), see Books Online. You can search the archives to see if anyone has posted a
script to semi-automate this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"George" <George@.discussions.microsoft.com> wrote in message
news:849B0CB4-1487-441A-B91B-41DA1C87C37A@.microsoft.com...
> Changed the master db collation by using the rebuildm utility from
> SQL_LATIN1_GENERAL_SP1_CI_AS to Latin1-General, Binary. How do I change the
> user dbs now? Issue is critical. Please help. George|||Tibor,
is it possible to dump the database to a flat file, delete the existing db,
create a new db with the correct collation and then import data back in? I
tried backup, delete resotore but it changed back to what the database was
when it was backed up. Any help on this is greatly appreciated. George
"Tibor Karaszi" wrote:
> ALTER DATABASE. But that doesn't change collation for the existing tables. For that you use ALTER
> TABLE ... ALTER COLUMN. Yes, a lot of work, and there are some other things you need to do (like
> dropping indexes), see Books Online. You can search the archives to see if anyone has posted a
> script to semi-automate this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:849B0CB4-1487-441A-B91B-41DA1C87C37A@.microsoft.com...
> > Changed the master db collation by using the rebuildm utility from
> > SQL_LATIN1_GENERAL_SP1_CI_AS to Latin1-General, Binary. How do I change the
> > user dbs now? Issue is critical. Please help. George
>|||> is it possible to dump the database to a flat file
There is no such direct feature in SQL Server. But you can use some of the transfer tools (DTS etc),
and depending on which method you chose, you script your objects first and then re-create them
before the import.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"George" <George@.discussions.microsoft.com> wrote in message
news:F6F90F12-1FC5-450B-8724-DD3D5DCA5C71@.microsoft.com...
> Tibor,
> is it possible to dump the database to a flat file, delete the existing db,
> create a new db with the correct collation and then import data back in? I
> tried backup, delete resotore but it changed back to what the database was
> when it was backed up. Any help on this is greatly appreciated. George
> "Tibor Karaszi" wrote:
>> ALTER DATABASE. But that doesn't change collation for the existing tables. For that you use ALTER
>> TABLE ... ALTER COLUMN. Yes, a lot of work, and there are some other things you need to do (like
>> dropping indexes), see Books Online. You can search the archives to see if anyone has posted a
>> script to semi-automate this.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "George" <George@.discussions.microsoft.com> wrote in message
>> news:849B0CB4-1487-441A-B91B-41DA1C87C37A@.microsoft.com...
>> > Changed the master db collation by using the rebuildm utility from
>> > SQL_LATIN1_GENERAL_SP1_CI_AS to Latin1-General, Binary. How do I change the
>> > user dbs now? Issue is critical. Please help. George
>>|||Had to call Microsoft. If you change only one db (user db) then follow these
instructions:
Transfer a database from one collation in SQL Server 2000 to a different
collation in SQL Server 2000
To transfer a database from one collation in SQL Server 2000 to a different
collation in SQL Server 2000, follow these steps:
1. Back up the source database.
2. Note if any columns use the COLLATE clause.
3. Create a new database on the destination server with the appropriate
collation.
4. If no columns use the COLLATE clause, use DTS to transfer the data (Right
click on the database, Export data) to the destination server. To do so,
enable the Use Collation option (choose third option when choosind type of
transfer which is all data) for code page translation and to transfer the
data to the new collation on the destination database. If any columns use the
COLLATE clause, follow these steps: a. Generate scripts for all the objects
(not including the indexes, the triggers, the primary keys, the foreign keys,
the default settings, and the constraints). Additionally, make sure that you
enable the Only script 7.0 compatible features option to remove the COLLATE
clause from the script.
Note When you use the Only script 7.0 compatible features option, you can
change the collation. However, any new SQL Server 2000 options (including
user-defined functions, extended properties, the INSTEAD OF trigger, and
indexes on views) will not be considered when the scripts are generated.
b. Run the scripts from step a on the destination database to create the
objects with the destination database collation.
c. Use DTS to transfer only the data from the source database.
d. After the data is successfully transferred, generate scripts for all the
constraints, foreign keys, primary keys, and indexes from the source
database.
e. Run the scripts from step d on the destination database.
George
"Tibor Karaszi" wrote:
> > is it possible to dump the database to a flat file
> There is no such direct feature in SQL Server. But you can use some of the transfer tools (DTS etc),
> and depending on which method you chose, you script your objects first and then re-create them
> before the import.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:F6F90F12-1FC5-450B-8724-DD3D5DCA5C71@.microsoft.com...
> > Tibor,
> >
> > is it possible to dump the database to a flat file, delete the existing db,
> > create a new db with the correct collation and then import data back in? I
> > tried backup, delete resotore but it changed back to what the database was
> > when it was backed up. Any help on this is greatly appreciated. George
> >
> > "Tibor Karaszi" wrote:
> >
> >> ALTER DATABASE. But that doesn't change collation for the existing tables. For that you use ALTER
> >> TABLE ... ALTER COLUMN. Yes, a lot of work, and there are some other things you need to do (like
> >> dropping indexes), see Books Online. You can search the archives to see if anyone has posted a
> >> script to semi-automate this.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "George" <George@.discussions.microsoft.com> wrote in message
> >> news:849B0CB4-1487-441A-B91B-41DA1C87C37A@.microsoft.com...
> >> > Changed the master db collation by using the rebuildm utility from
> >> > SQL_LATIN1_GENERAL_SP1_CI_AS to Latin1-General, Binary. How do I change the
> >> > user dbs now? Issue is critical. Please help. George
> >>
> >>
>
Change the collation on the user DB
Changed the master db collation by using the rebuildm utility from
SQL_LATIN1_GENERAL_SP1_CI_AS to Latin1-General, Binary. How do I change the
user dbs now? Issue is critical. Please help. GeorgeALTER DATABASE. But that doesn't change collation for the existing tables. F
or that you use ALTER
TABLE ... ALTER COLUMN. Yes, a lot of work, and there are some other things
you need to do (like
dropping indexes), see Books Online. You can search the archives to see if a
nyone has posted a
script to semi-automate this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"George" <George@.discussions.microsoft.com> wrote in message
news:849B0CB4-1487-441A-B91B-41DA1C87C37A@.microsoft.com...
> Changed the master db collation by using the rebuildm utility from
> SQL_LATIN1_GENERAL_SP1_CI_AS to Latin1-General, Binary. How do I change th
e
> user dbs now? Issue is critical. Please help. George|||Tibor,
is it possible to dump the database to a flat file, delete the existing db,
create a new db with the correct collation and then import data back in? I
tried backup, delete resotore but it changed back to what the database was
when it was backed up. Any help on this is greatly appreciated. George
"Tibor Karaszi" wrote:
> ALTER DATABASE. But that doesn't change collation for the existing tables.
For that you use ALTER
> TABLE ... ALTER COLUMN. Yes, a lot of work, and there are some other thing
s you need to do (like
> dropping indexes), see Books Online. You can search the archives to see if
anyone has posted a
> script to semi-automate this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:849B0CB4-1487-441A-B91B-41DA1C87C37A@.microsoft.com...
>|||> is it possible to dump the database to a flat file
There is no such direct feature in SQL Server. But you can use some of the t
ransfer tools (DTS etc),
and depending on which method you chose, you script your objects first and t
hen re-create them
before the import.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"George" <George@.discussions.microsoft.com> wrote in message
news:F6F90F12-1FC5-450B-8724-DD3D5DCA5C71@.microsoft.com...[vbcol=seagreen]
> Tibor,
> is it possible to dump the database to a flat file, delete the existing db
,
> create a new db with the correct collation and then import data back in? I
> tried backup, delete resotore but it changed back to what the database was
> when it was backed up. Any help on this is greatly appreciated. George
> "Tibor Karaszi" wrote:
>|||Had to call Microsoft. If you change only one db (user db) then follow these
instructions:
Transfer a database from one collation in SQL Server 2000 to a different
collation in SQL Server 2000
To transfer a database from one collation in SQL Server 2000 to a different
collation in SQL Server 2000, follow these steps:
1. Back up the source database.
2. Note if any columns use the COLLATE clause.
3. Create a new database on the destination server with the appropriate
collation.
4. If no columns use the COLLATE clause, use DTS to transfer the data (Right
click on the database, Export data) to the destination server. To do so,
enable the Use Collation option (choose third option when choosind type of
transfer which is all data) for code page translation and to transfer the
data to the new collation on the destination database. If any columns use th
e
COLLATE clause, follow these steps: a. Generate scripts for all the objects
(not including the indexes, the triggers, the primary keys, the foreign keys
,
the default settings, and the constraints). Additionally, make sure that you
enable the Only script 7.0 compatible features option to remove the COLLATE
clause from the script.
Note When you use the Only script 7.0 compatible features option, you can
change the collation. However, any new SQL Server 2000 options (including
user-defined functions, extended properties, the INSTEAD OF trigger, and
indexes on views) will not be considered when the scripts are generated.
b. Run the scripts from step a on the destination database to create the
objects with the destination database collation.
c. Use DTS to transfer only the data from the source database.
d. After the data is successfully transferred, generate scripts for all the
constraints, foreign keys, primary keys, and indexes from the source
database.
e. Run the scripts from step d on the destination database.
George
"Tibor Karaszi" wrote:
> There is no such direct feature in SQL Server. But you can use some of the
transfer tools (DTS etc),
> and depending on which method you chose, you script your objects first and
then re-create them
> before the import.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:F6F90F12-1FC5-450B-8724-DD3D5DCA5C71@.microsoft.com...
>
SQL_LATIN1_GENERAL_SP1_CI_AS to Latin1-General, Binary. How do I change the
user dbs now? Issue is critical. Please help. GeorgeALTER DATABASE. But that doesn't change collation for the existing tables. F
or that you use ALTER
TABLE ... ALTER COLUMN. Yes, a lot of work, and there are some other things
you need to do (like
dropping indexes), see Books Online. You can search the archives to see if a
nyone has posted a
script to semi-automate this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"George" <George@.discussions.microsoft.com> wrote in message
news:849B0CB4-1487-441A-B91B-41DA1C87C37A@.microsoft.com...
> Changed the master db collation by using the rebuildm utility from
> SQL_LATIN1_GENERAL_SP1_CI_AS to Latin1-General, Binary. How do I change th
e
> user dbs now? Issue is critical. Please help. George|||Tibor,
is it possible to dump the database to a flat file, delete the existing db,
create a new db with the correct collation and then import data back in? I
tried backup, delete resotore but it changed back to what the database was
when it was backed up. Any help on this is greatly appreciated. George
"Tibor Karaszi" wrote:
> ALTER DATABASE. But that doesn't change collation for the existing tables.
For that you use ALTER
> TABLE ... ALTER COLUMN. Yes, a lot of work, and there are some other thing
s you need to do (like
> dropping indexes), see Books Online. You can search the archives to see if
anyone has posted a
> script to semi-automate this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:849B0CB4-1487-441A-B91B-41DA1C87C37A@.microsoft.com...
>|||> is it possible to dump the database to a flat file
There is no such direct feature in SQL Server. But you can use some of the t
ransfer tools (DTS etc),
and depending on which method you chose, you script your objects first and t
hen re-create them
before the import.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"George" <George@.discussions.microsoft.com> wrote in message
news:F6F90F12-1FC5-450B-8724-DD3D5DCA5C71@.microsoft.com...[vbcol=seagreen]
> Tibor,
> is it possible to dump the database to a flat file, delete the existing db
,
> create a new db with the correct collation and then import data back in? I
> tried backup, delete resotore but it changed back to what the database was
> when it was backed up. Any help on this is greatly appreciated. George
> "Tibor Karaszi" wrote:
>|||Had to call Microsoft. If you change only one db (user db) then follow these
instructions:
Transfer a database from one collation in SQL Server 2000 to a different
collation in SQL Server 2000
To transfer a database from one collation in SQL Server 2000 to a different
collation in SQL Server 2000, follow these steps:
1. Back up the source database.
2. Note if any columns use the COLLATE clause.
3. Create a new database on the destination server with the appropriate
collation.
4. If no columns use the COLLATE clause, use DTS to transfer the data (Right
click on the database, Export data) to the destination server. To do so,
enable the Use Collation option (choose third option when choosind type of
transfer which is all data) for code page translation and to transfer the
data to the new collation on the destination database. If any columns use th
e
COLLATE clause, follow these steps: a. Generate scripts for all the objects
(not including the indexes, the triggers, the primary keys, the foreign keys
,
the default settings, and the constraints). Additionally, make sure that you
enable the Only script 7.0 compatible features option to remove the COLLATE
clause from the script.
Note When you use the Only script 7.0 compatible features option, you can
change the collation. However, any new SQL Server 2000 options (including
user-defined functions, extended properties, the INSTEAD OF trigger, and
indexes on views) will not be considered when the scripts are generated.
b. Run the scripts from step a on the destination database to create the
objects with the destination database collation.
c. Use DTS to transfer only the data from the source database.
d. After the data is successfully transferred, generate scripts for all the
constraints, foreign keys, primary keys, and indexes from the source
database.
e. Run the scripts from step d on the destination database.
George
"Tibor Karaszi" wrote:
> There is no such direct feature in SQL Server. But you can use some of the
transfer tools (DTS etc),
> and depending on which method you chose, you script your objects first and
then re-create them
> before the import.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:F6F90F12-1FC5-450B-8724-DD3D5DCA5C71@.microsoft.com...
>
Friday, February 24, 2012
change sql server from CS to Case Insensitive
Hi,
I setup a SQL server with case sensitive. How can I change
it into case insensitive?
If I use rebuildm, would it destroy my existing db?
When I ran rebuildm.exe, I got error message "Rebuild
Master failed with error -1:". Any hint?
Thank you for your help.|
| Hi,
| I setup a SQL server with case sensitive. How can I change
| it into case insensitive?
--
I will assume that you are using SQL Server 2000. In this version, sort
order can be different up to column level.
To change the server-wide sort order, you need to perform the following:
1. Use rebuildm to rebuild the master database and specify the desired case
insensitive sort order.
2. Use Alter database to specify the sort order per database.
One concern when moving from case sensitive to case sensitive is you might
encounter duplicate object names. For instance, when you move from case
sensitive to case insensitive sort order a table named "tAbLe1" will be the
same as a table named "TaBlE1".
Hope this helps,
--
Eric Cárdenas
SQL Server support|||> 1. Use rebuildm to rebuild the master database and specify the desired
case
> insensitive sort order.
1.5 Restore the databases.
> 2. Use Alter database to specify the sort order per database.
3. Use ALTER TABLE ... ALTER COLUMN to change collation for all existing
columns. And drop constraints and indexes to be able to do this.
Changing collation is (still) not a trivial task, unfortunately.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Eric Cardenas" <ecard@.anonymous.com> wrote in message
news:AXhdme7vDHA.2772@.cpmsftngxa07.phx.gbl...
> |
> | Hi,
> | I setup a SQL server with case sensitive. How can I change
> | it into case insensitive?
> --
> I will assume that you are using SQL Server 2000. In this version, sort
> order can be different up to column level.
> To change the server-wide sort order, you need to perform the following:
> 1. Use rebuildm to rebuild the master database and specify the desired
case
> insensitive sort order.
> 2. Use Alter database to specify the sort order per database.
> One concern when moving from case sensitive to case sensitive is you might
> encounter duplicate object names. For instance, when you move from case
> sensitive to case insensitive sort order a table named "tAbLe1" will be
the
> same as a table named "TaBlE1".
> Hope this helps,
> --
> Eric Cárdenas
> SQL Server support
>
I setup a SQL server with case sensitive. How can I change
it into case insensitive?
If I use rebuildm, would it destroy my existing db?
When I ran rebuildm.exe, I got error message "Rebuild
Master failed with error -1:". Any hint?
Thank you for your help.|
| Hi,
| I setup a SQL server with case sensitive. How can I change
| it into case insensitive?
--
I will assume that you are using SQL Server 2000. In this version, sort
order can be different up to column level.
To change the server-wide sort order, you need to perform the following:
1. Use rebuildm to rebuild the master database and specify the desired case
insensitive sort order.
2. Use Alter database to specify the sort order per database.
One concern when moving from case sensitive to case sensitive is you might
encounter duplicate object names. For instance, when you move from case
sensitive to case insensitive sort order a table named "tAbLe1" will be the
same as a table named "TaBlE1".
Hope this helps,
--
Eric Cárdenas
SQL Server support|||> 1. Use rebuildm to rebuild the master database and specify the desired
case
> insensitive sort order.
1.5 Restore the databases.
> 2. Use Alter database to specify the sort order per database.
3. Use ALTER TABLE ... ALTER COLUMN to change collation for all existing
columns. And drop constraints and indexes to be able to do this.
Changing collation is (still) not a trivial task, unfortunately.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Eric Cardenas" <ecard@.anonymous.com> wrote in message
news:AXhdme7vDHA.2772@.cpmsftngxa07.phx.gbl...
> |
> | Hi,
> | I setup a SQL server with case sensitive. How can I change
> | it into case insensitive?
> --
> I will assume that you are using SQL Server 2000. In this version, sort
> order can be different up to column level.
> To change the server-wide sort order, you need to perform the following:
> 1. Use rebuildm to rebuild the master database and specify the desired
case
> insensitive sort order.
> 2. Use Alter database to specify the sort order per database.
> One concern when moving from case sensitive to case sensitive is you might
> encounter duplicate object names. For instance, when you move from case
> sensitive to case insensitive sort order a table named "tAbLe1" will be
the
> same as a table named "TaBlE1".
> Hope this helps,
> --
> Eric Cárdenas
> SQL Server support
>
Subscribe to:
Posts (Atom)