change default collation for user database

Hi Folks,
Is there another way to change the default collation for a
user database without using the SQL cmd:
ALTER DATABASE myDatase COLLATE Latin1_General_CI_AS '
With the ALTER DATABASE it is not possible to change the
database collation whenever there are Check Constraint
which are depending on the database collation.
A go lead for me would also be if someone can tell me in
which master table this is recorded'
Cheers Jack.You have to drop the constraints, then change the database collation, and
recreate the constraints. Don't try to change anything in system table
directly, you can make your database completely unusable.
I have an (unsupported) script from Microsoft that you can use to recollate
a database. Drop me a mail if you want it.
--
Jacco Schalkwijk
SQL Server MVP
"Jack" <jbonapart@.dicon.nl> wrote in message
news:051301c3c2f8$46849d80$a301280a@.phx.gbl...
> Hi Folks,
> Is there another way to change the default collation for a
> user database without using the SQL cmd:
> ALTER DATABASE myDatase COLLATE Latin1_General_CI_AS '
> With the ALTER DATABASE it is not possible to change the
> database collation whenever there are Check Constraint
> which are depending on the database collation.
> A go lead for me would also be if someone can tell me in
> which master table this is recorded'
> Cheers Jack.|||Jacco,
What e-mail adres can I reach you ? I have tried
nospamjaccos@.eurostop.co.uk but i receive a Delivery
Status Notification Failure.
You can e-mail me the collate script to jbonapart@.dicon.nl
cheers.
Delivery to the following recipients failed.
>--Original Message--
>You have to drop the constraints, then change the
database collation, and
>recreate the constraints. Don't try to change anything in
system table
>directly, you can make your database completely unusable.
>I have an (unsupported) script from Microsoft that you
can use to recollate
>a database. Drop me a mail if you want it.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Jack" <jbonapart@.dicon.nl> wrote in message
>news:051301c3c2f8$46849d80$a301280a@.phx.gbl...
>> Hi Folks,
>> Is there another way to change the default collation
for a
>> user database without using the SQL cmd:
>> ALTER DATABASE myDatase COLLATE Latin1_General_CI_AS '
>> With the ALTER DATABASE it is not possible to change the
>> database collation whenever there are Check Constraint
>> which are depending on the database collation.
>> A go lead for me would also be if someone can tell me in
>> which master table this is recorded'
>> Cheers Jack.
>
>.
>|||sorry, i had put a wrong sender's E-mail adres.
>--Original Message--
>Jacco,
>What e-mail adres can I reach you ? I have tried
>nospamjaccos@.eurostop.co.uk but i receive a Delivery
>Status Notification Failure.
>You can e-mail me the collate script to jbonapart@.dicon.nl
>cheers.
>Delivery to the following recipients failed.
>
>>--Original Message--
>>You have to drop the constraints, then change the
>database collation, and
>>recreate the constraints. Don't try to change anything
in
>system table
>>directly, you can make your database completely unusable.
>>I have an (unsupported) script from Microsoft that you
>can use to recollate
>>a database. Drop me a mail if you want it.
>>--
>>Jacco Schalkwijk
>>SQL Server MVP
>>
>>"Jack" <jbonapart@.dicon.nl> wrote in message
>>news:051301c3c2f8$46849d80$a301280a@.phx.gbl...
>> Hi Folks,
>> Is there another way to change the default collation
>for a
>> user database without using the SQL cmd:
>> ALTER DATABASE myDatase COLLATE Latin1_General_CI_AS '
>> With the ALTER DATABASE it is not possible to change
the
>> database collation whenever there are Check Constraint
>> which are depending on the database collation.
>> A go lead for me would also be if someone can tell me
in
>> which master table this is recorded'
>> Cheers Jack.
>>
>>.
>.
>sql

Change collations in many tables defined using COLLATE

I have (inherited a) a db with lots of tables looking like the definition at the end of the message.

I need to change all these tables, so that all the textual fields follow the default DB collation.

Any help appreciated!
THANX...

CREATE TABLE [Club] (
[id] [int] NOT NULL ,
[name] [varchar] (100) COLLATE Greek_CI_AS NOT NULL ,
[address] [varchar] (255) COLLATE Greek_CI_AS NULL ,
[zoomAreaId] [int] NULL ,
[phone] [varchar] (255) COLLATE Greek_CI_AS NULL ,
[contact] [varchar] (50) COLLATE Greek_CI_AS NULL ,
[clubCategoryId] [int] NULL ,
[unused] [varchar] (2) COLLATE Greek_CI_AS NULL ,
[monday] [int] NULL ,
[tuesday] [int] NULL ,
[wednesday] [int] NULL ,
[thursday] [int] NULL ,
[friday] [int] NULL ,
[saturday] [int] NULL ,
[sunday] [int] NULL ,
[comments] [text] COLLATE Greek_CI_AS NULL ,
[URL] [varchar] (255) COLLATE Greek_CI_AS NULL ,
[image] [varchar] (50) COLLATE Greek_CI_AS NULL ,
[priceCategoryId] [int] NULL ,
[ratingId] [int] NULL ,
[entryDate] [datetime] NULL ,
[WAPText] [varchar] (255) COLLATE Greek_CI_AS NULL ,
[SMSText] [varchar] (160) COLLATE Greek_CI_AS NULL ,
[SMSAddress] [varchar] (50) COLLATE Greek_CI_AS NULL ,
[active] [bit] NOT NULL ,
[content_id] [bigint] NULL ,
[disp_as_propos] [bit] NULL ,
[date2disp] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOALTER TABLE...ALTER COLUMN would do the trick, but you have to drop all indexes and all constraints. If you have DBArtisan it would be very easy to do, or you can script both DROP <table_name>.<index_name> and ALTER TABLE <table_name> DROP CONSTRAINT and then re-create them yourself.|||Also, BOL:

You cannot alter the collation of a column that is currently referenced by:

A computed column.
An index.
Distribution statistics, either generated automatically or by the CREATE STATISTICS statement.
A CHECK constraint.
A FOREIGN KEY constraint.

change collation

Hello.
I need to change the COLLATE sequence on TEMPDB database, change it from de
Latin1_General to Modern_Spanish.
I don´t have here the SQLServer CD to make a Rebuildm because I´m on a trip
and the database I have is in Modern_Spanish and the server is
Latin1_General.
What can I do ?
I tried to make an ALTER DATABASE to the database in Modern_Spanish to
change it toLatin1_General but I couldn´t because there are too many
constraints with this collation.
Thanks in advance for any help.
I need to solve it in some way because I´ve a presentation tomorrow.
--
Ricardo Lopez Celani
Unisolutions SA
rlopezNO@.SPAMunisolutions.com.arTempdb is rebuilt at reboot so you could try changing the collation of the model database.
A word of caution - tempdb will then be a different collation from the other system databases and this could cause problems if they have SPs which create temp tables and expect them to be the same collation.
>--Original Message--
>Hello.
>I need to change the COLLATE sequence on TEMPDB database, change it from de
>Latin1_General to Modern_Spanish.
>I don=B4t have here the SQLServer CD to make a Rebuildm because I=B4m on a trip
>and the database I have is in Modern_Spanish and the server is
>Latin1_General.
>What can I do ?
>I tried to make an ALTER DATABASE to the database in Modern_Spanish to
>change it toLatin1_General but I couldn=B4t because there are too many
>constraints with this collation.
>Thanks in advance for any help.
>I need to solve it in some way because I=B4ve a presentation tomorrow.
>--
>Ricardo Lopez Celani
>Unisolutions SA
>rlopezNO@.SPAMunisolutions.com.ar
>
>.
>|||[posted and mailed]
Ricardo Lopez Celani (rlopez@.unisolutions.com.ar) writes:
> I need to change the COLLATE sequence on TEMPDB database, change it from
> de Latin1_General to Modern_Spanish. I don´t have here the SQLServer CD
> to make a Rebuildm because I´m on a trip and the database I have is in
> Modern_Spanish and the server is Latin1_General. What can I do ? I tried
> to make an ALTER DATABASE to the database in Modern_Spanish to change it
> toLatin1_General but I couldn´t because there are too many constraints
> with this collation. Thanks in advance for any help. I need to solve it
> in some way because I´ve a presentation tomorrow.
This question is answered in .tools. Please do not post the same question
to several groups in individual postings. If you feel compelled to
target several groups, please cross-post.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp