How to change the collation of all field in all table of a databas

when I join two table, I got the error "Cannot resolve collation conflict for
equal to operation.", I know this is due to the collation is different for
two table, so I want to write a script to change all field in all table of a
database to use the same collation, how to write that script?
Thx!!
kei
ALTER TABLE....ALTER COLUMN......
For more details please refer to the BOL.
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!
|||Meanwhile, you can specify collation in the join statement. Check for
COLLATE.
MC
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!

How to change the collation of all field in all table of a databas

when I join two table, I got the error "Cannot resolve collation conflict for
equal to operation.", I know this is due to the collation is different for
two table, so I want to write a script to change all field in all table of a
database to use the same collation, how to write that script?
Thx!!kei
ALTER TABLE....ALTER COLUMN......
For more details please refer to the BOL.
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!|||Meanwhile, you can specify collation in the join statement. Check for
COLLATE.
MC
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!|||But no method to change the collation of all field in all table of a
database? if sepcify collation in the join statement, then all sql statement
need to use such method, quite trouble...
I know there is alter column..., but I also know this method can't work on
primary, foreign key, index field..., right?
"MC" wrote:
> Meanwhile, you can specify collation in the join statement. Check for
> COLLATE.
> MC
>
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> > when I join two table, I got the error "Cannot resolve collation conflict
> > for
> > equal to operation.", I know this is due to the collation is different for
> > two table, so I want to write a script to change all field in all table of
> > a
> > database to use the same collation, how to write that script?
> > Thx!!
>
>|||kei
Have you checked it?
"kei" <kei@.discussions.microsoft.com> wrote in message
news:76EABC8C-E8BA-4161-A8FA-24E98AD79A47@.microsoft.com...
> But no method to change the collation of all field in all table of a
> database? if sepcify collation in the join statement, then all sql
> statement
> need to use such method, quite trouble...
> I know there is alter column..., but I also know this method can't work on
> primary, foreign key, index field..., right?
> "MC" wrote:
>> Meanwhile, you can specify collation in the join statement. Check for
>> COLLATE.
>> MC
>>
>> "kei" <kei@.discussions.microsoft.com> wrote in message
>> news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
>> > when I join two table, I got the error "Cannot resolve collation
>> > conflict
>> > for
>> > equal to operation.", I know this is due to the collation is different
>> > for
>> > two table, so I want to write a script to change all field in all table
>> > of
>> > a
>> > database to use the same collation, how to write that script?
>> > Thx!!
>>|||I still don't know the answer, could you write more explicit? I check
"Changing Collations" in BOL, and it only teach how to change collation for
one column only, and with limitation that if the column is an index..., then
the method is not work, so could you write the solution more clearly.
Thx!!
"Uri Dimant" wrote:
> kei
> Have you checked it?
>
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:76EABC8C-E8BA-4161-A8FA-24E98AD79A47@.microsoft.com...
> > But no method to change the collation of all field in all table of a
> > database? if sepcify collation in the join statement, then all sql
> > statement
> > need to use such method, quite trouble...
> > I know there is alter column..., but I also know this method can't work on
> > primary, foreign key, index field..., right?
> >
> > "MC" wrote:
> >
> >> Meanwhile, you can specify collation in the join statement. Check for
> >> COLLATE.
> >>
> >> MC
> >>
> >>
> >> "kei" <kei@.discussions.microsoft.com> wrote in message
> >> news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> >> > when I join two table, I got the error "Cannot resolve collation
> >> > conflict
> >> > for
> >> > equal to operation.", I know this is due to the collation is different
> >> > for
> >> > two table, so I want to write a script to change all field in all table
> >> > of
> >> > a
> >> > database to use the same collation, how to write that script?
> >> > Thx!!
> >>
> >>
> >>
>
>|||You need to alter each column that has text datatype (varchar, char....).
Alternatively, you can script your database in enterprise manager and
specify 7.0 compatibility. That should give you script without collations.
Then you could execute that script and you get empty database. All thats
left for you is transfer data....
MC
"kei" <kei@.discussions.microsoft.com> wrote in message
news:26C46269-DC86-4102-89A5-C9195EB47A57@.microsoft.com...
>I still don't know the answer, could you write more explicit? I check
> "Changing Collations" in BOL, and it only teach how to change collation
> for
> one column only, and with limitation that if the column is an index...,
> then
> the method is not work, so could you write the solution more clearly.
> Thx!!
> "Uri Dimant" wrote:
>> kei
>> Have you checked it?
>>
>> "kei" <kei@.discussions.microsoft.com> wrote in message
>> news:76EABC8C-E8BA-4161-A8FA-24E98AD79A47@.microsoft.com...
>> > But no method to change the collation of all field in all table of a
>> > database? if sepcify collation in the join statement, then all sql
>> > statement
>> > need to use such method, quite trouble...
>> > I know there is alter column..., but I also know this method can't work
>> > on
>> > primary, foreign key, index field..., right?
>> >
>> > "MC" wrote:
>> >
>> >> Meanwhile, you can specify collation in the join statement. Check for
>> >> COLLATE.
>> >>
>> >> MC
>> >>
>> >>
>> >> "kei" <kei@.discussions.microsoft.com> wrote in message
>> >> news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
>> >> > when I join two table, I got the error "Cannot resolve collation
>> >> > conflict
>> >> > for
>> >> > equal to operation.", I know this is due to the collation is
>> >> > different
>> >> > for
>> >> > two table, so I want to write a script to change all field in all
>> >> > table
>> >> > of
>> >> > a
>> >> > database to use the same collation, how to write that script?
>> >> > Thx!!
>> >>
>> >>
>> >>
>>

How to change the collation of all field in all table of a databas

when I join two table, I got the error "Cannot resolve collation conflict fo
r
equal to operation.", I know this is due to the collation is different for
two table, so I want to write a script to change all field in all table of a
database to use the same collation, how to write that script?
Thx!!kei
ALTER TABLE....ALTER COLUMN......
For more details please refer to the BOL.
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!|||Meanwhile, you can specify collation in the join statement. Check for
COLLATE.
MC
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!

How to change collation on MODEL ?

I have a 2005 SP1 server with the wrong server collation.
All user databases on this server have the right collation.
I am getting collation conflict errors when I use temp tables.
I believe this is because tempdb is created based on "model", and model has
the server collation.
I have tried to change the "model" collation using ALTER DATABASE, but I get
the error message "Cannot alter the database 'model' because it is a system
database.
"
How can I force tempdb to have a specific collation, instead of the same
collation as the server collation?> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?
You have to rebuild the system databases, as you cannot have different collations across the system
databases. This is done using the SETUP program in 2005. This is described in
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/df40c888-691c-4962-a420-78a57852364d.htm.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pat" <pat@.online.nospam> wrote in message
news:B4BB3864-D003-426A-9860-CC2FC80D0E06@.microsoft.com...
>I have a 2005 SP1 server with the wrong server collation.
> All user databases on this server have the right collation.
> I am getting collation conflict errors when I use temp tables.
> I believe this is because tempdb is created based on "model", and model has
> the server collation.
> I have tried to change the "model" collation using ALTER DATABASE, but I get
> the error message "Cannot alter the database 'model' because it is a system
> database.
> "
> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?|||Hi Pat,
Thank you for your posting!
I agreed with Tibor. You need to rebuild the system database.
Also, here is an article for your reference.
325335 How to transfer a database from one collation to another collation
in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;325335
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||The doc mentions that all service packs and hotfixes are lost when rebuilding
the system databases, and need to be re-applied.
We have SP1 and several hotfixes, this sounds like a lot of work for just
changing the collation.
"Tibor Karaszi" wrote:
> > How can I force tempdb to have a specific collation, instead of the same
> > collation as the server collation?
> You have to rebuild the system databases, as you cannot have different collations across the system
> databases. This is done using the SETUP program in 2005. This is described in
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/df40c888-691c-4962-a420-78a57852364d.htm.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Pat" <pat@.online.nospam> wrote in message
> news:B4BB3864-D003-426A-9860-CC2FC80D0E06@.microsoft.com...
> >I have a 2005 SP1 server with the wrong server collation.
> > All user databases on this server have the right collation.
> >
> > I am getting collation conflict errors when I use temp tables.
> > I believe this is because tempdb is created based on "model", and model has
> > the server collation.
> >
> > I have tried to change the "model" collation using ALTER DATABASE, but I get
> > the error message "Cannot alter the database 'model' because it is a system
> > database.
> > "
> >
> > How can I force tempdb to have a specific collation, instead of the same
> > collation as the server collation?
>|||> We have SP1 and several hotfixes, this sounds like a lot of work for just
> changing the collation.
Yep, that is why I always advocate to be very careful when selecting the collation during
installation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pat" <pat@.online.nospam> wrote in message
news:DFA53097-0EB5-4125-A9EC-E0EF157212D9@.microsoft.com...
> The doc mentions that all service packs and hotfixes are lost when rebuilding
> the system databases, and need to be re-applied.
> We have SP1 and several hotfixes, this sounds like a lot of work for just
> changing the collation.
> "Tibor Karaszi" wrote:
>> > How can I force tempdb to have a specific collation, instead of the same
>> > collation as the server collation?
>> You have to rebuild the system databases, as you cannot have different collations across the
>> system
>> databases. This is done using the SETUP program in 2005. This is described in
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/df40c888-691c-4962-a420-78a57852364d.htm.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Pat" <pat@.online.nospam> wrote in message
>> news:B4BB3864-D003-426A-9860-CC2FC80D0E06@.microsoft.com...
>> >I have a 2005 SP1 server with the wrong server collation.
>> > All user databases on this server have the right collation.
>> >
>> > I am getting collation conflict errors when I use temp tables.
>> > I believe this is because tempdb is created based on "model", and model has
>> > the server collation.
>> >
>> > I have tried to change the "model" collation using ALTER DATABASE, but I get
>> > the error message "Cannot alter the database 'model' because it is a system
>> > database.
>> > "
>> >
>> > How can I force tempdb to have a specific collation, instead of the same
>> > collation as the server collation?
>>|||"Pat" wrote:
> I have a 2005 SP1 server with the wrong server collation.
> All user databases on this server have the right collation.
> I am getting collation conflict errors when I use temp tables.
> I believe this is because tempdb is created based on "model", and model has
> the server collation.
> I have tried to change the "model" collation using ALTER DATABASE, but I get
> the error message "Cannot alter the database 'model' because it is a system
> database.
> "
> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?
In addition to the other replies to your query, there may be another way to
do this.
If you create a new temporary table in a stored procedure, use the good
collation.
example:
Create Table #Status
(
State nvarchar(128) collate database_default
)
This might be a good solution if there are not too many places where
temporary tables are created.
hope this helps.
kind regards,
Bart.sql