Hello all:
I try to change the Collation of database, but it not work in
vb.net 2005, have any one do it before?
MatchSQL,
Use "alter database", but this does not change the collation for all char /
nchar / varchar / nvarchar / etc. columns.
How to transfer a database from one collation to another collation in SQL
Server
[url]http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.server& cat=en_US_671e06d0-f20d-4bb3-9c6a-42c825ddb1dc&lang=en&cr=US[/url]
AMB
"MatchSQL" wrote:
> Hello all:
> I try to change the Collation of database, but it not work in
> vb.net 2005, have any one do it before?
>
Showing posts with label collation. Show all posts
Showing posts with label collation. Show all posts
Friday, March 30, 2012
How to change the DataBase Collation!
Hello all:
I try to change the Collation of database, but it not work in
vb.net 2005, have any one do it before?MatchSQL,
Use "alter database", but this does not change the collation for all char /
nchar / varchar / nvarchar / etc. columns.
How to transfer a database from one collation to another collation in SQL
Server
http://www.microsoft.com/communitie...n&cr=US
AMB
"MatchSQL" wrote:
> Hello all:
> I try to change the Collation of database, but it not work in
> vb.net 2005, have any one do it before?
>
I try to change the Collation of database, but it not work in
vb.net 2005, have any one do it before?MatchSQL,
Use "alter database", but this does not change the collation for all char /
nchar / varchar / nvarchar / etc. columns.
How to transfer a database from one collation to another collation in SQL
Server
http://www.microsoft.com/communitie...n&cr=US
AMB
"MatchSQL" wrote:
> Hello all:
> I try to change the Collation of database, but it not work in
> vb.net 2005, have any one do it before?
>
How to change the DataBase Collation!
Hello all:
I try to change the Collation of database, but it not work in
vb.net 2005, have any one do it before?MatchSQL,
Use "alter database", but this does not change the collation for all char /
nchar / varchar / nvarchar / etc. columns.
How to transfer a database from one collation to another collation in SQL
Serve
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.server&cat=en_US_671e06d0-f20d-4bb3-9c6a-42c825ddb1dc&lang=en&cr=US
AMB
"MatchSQL" wrote:
> Hello all:
> I try to change the Collation of database, but it not work in
> vb.net 2005, have any one do it before?
>
I try to change the Collation of database, but it not work in
vb.net 2005, have any one do it before?MatchSQL,
Use "alter database", but this does not change the collation for all char /
nchar / varchar / nvarchar / etc. columns.
How to transfer a database from one collation to another collation in SQL
Serve
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.server&cat=en_US_671e06d0-f20d-4bb3-9c6a-42c825ddb1dc&lang=en&cr=US
AMB
"MatchSQL" wrote:
> Hello all:
> I try to change the Collation of database, but it not work in
> vb.net 2005, have any one do it before?
>
Wednesday, March 28, 2012
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!!
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!!
>> >>
>> >>
>> >>
>>
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!!
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 the collation name of server in SQL server 2005
Hi,
Pls, help me on how to change the server coollation name in SQL server 2005.
Thanks in advance
See the response to your identical post in the SQL Server Setup & Upgrade forum here.
Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).
How to change the Collation name in SQL server 2005
Pls, any one help me regarding the how to change the collation name of the server in SQL server 2005.
In SQL server 2000 we can do with rebuildm.exe, like that is there any one.
Thanks in advance
This is nothing to do with SQL Server Integration Services (SSSI), so moving to a better forum.
Try Books Online -
Setting and Changing Collations
(http://msdn2.microsoft.com/en-us/library/ms177439.aspx)
Setting and Changing the Server Collation
(http://msdn2.microsoft.com/en-us/library/ms179254.aspx)
Setup now has options that were previously handled by rebuildm
How to change SQL2005 Express server collation ?
Hi:
How to I change SQL2005 Express server collation ?
In SQL2005 Standard I use:
start /wait <SQL2005 installation folder>\setup.exe /qb
INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1
SAPWD=<NewStrongPassword> SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS
but in SQL2005 Express the C:\Program Files\Microsoft SQL
Server\90\Setup Bootstrap\setup.exe seem not work
Please help
JCVoon
Hi:
Never mind, my problem solved.
JCVoon
sql
How to I change SQL2005 Express server collation ?
In SQL2005 Standard I use:
start /wait <SQL2005 installation folder>\setup.exe /qb
INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1
SAPWD=<NewStrongPassword> SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS
but in SQL2005 Express the C:\Program Files\Microsoft SQL
Server\90\Setup Bootstrap\setup.exe seem not work
Please help
JCVoon
Hi:
Never mind, my problem solved.
JCVoon
sql
How to change SQL2005 Express server collation ?
Hi:
How to I change SQL2005 Express server collation ?
In SQL2005 Standard I use:
start /wait <SQL2005 installation folder>\setup.exe /qb
INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1
SAPWD=<NewStrongPassword> SQLCOLLATION=SQL_Latin1_General_CP1_CI_A
S
but in SQL2005 Express the C:\Program Files\Microsoft SQL
Server\90\Setup Bootstrap\setup.exe seem not work
Please help
JCVoonHi:
Never mind, my problem solved.
JCVoon
How to I change SQL2005 Express server collation ?
In SQL2005 Standard I use:
start /wait <SQL2005 installation folder>\setup.exe /qb
INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1
SAPWD=<NewStrongPassword> SQLCOLLATION=SQL_Latin1_General_CP1_CI_A
S
but in SQL2005 Express the C:\Program Files\Microsoft SQL
Server\90\Setup Bootstrap\setup.exe seem not work
Please help
JCVoonHi:
Never mind, my problem solved.
JCVoon
How to change SQL2005 Express server collation ?
Hi:
How to I change SQL2005 Express server collation ?
In SQL2005 Standard I use:
start /wait <SQL2005 installation folder>\setup.exe /qb
INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1
SAPWD=<NewStrongPassword> SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS
but in SQL2005 Express the C:\Program Files\Microsoft SQL
Server\90\Setup Bootstrap\setup.exe seem not work
Please help
JCVoonHi:
Never mind, my problem solved.
JCVoon
How to I change SQL2005 Express server collation ?
In SQL2005 Standard I use:
start /wait <SQL2005 installation folder>\setup.exe /qb
INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1
SAPWD=<NewStrongPassword> SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS
but in SQL2005 Express the C:\Program Files\Microsoft SQL
Server\90\Setup Bootstrap\setup.exe seem not work
Please help
JCVoonHi:
Never mind, my problem solved.
JCVoon
Monday, March 26, 2012
How to change Server Collation?
Aside from reinstalling, is there a way to change the
server collation settings?No. You can change the collation of individual columns with ALTER TABLE
ALTER COLUMN, but this has lots of caveats and can be difficult depending
on your schema. If you must change the instance-level default collation,
the only options are (a) a reinstall, or (b) use the Rebuild Master utility
(rebuildm.exe). RebuildM will replace your system databases, so extract or
backup anything you need from them before running it. Also, RebuildM will
change the instance-level defaults but it doesn't change the collation of
any existing user databases.
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
Content-Class: urn:content-classes:message
From: "Noldz" <rdevera@.wizardsgroup.com>
Sender: "Noldz" <rdevera@.wizardsgroup.com>
Subject: How to change Server Collation?
Date: Mon, 6 Oct 2003 20:14:17 -0700
Lines: 2
Message-ID: <09c601c38c81$178ecc10$a301280a@.phx.gbl>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Thread-Index: AcOMgReOEkR3oghcTTStBjXKZ+hd2w==Newsgroups: microsoft.public.sqlserver.server
Path: cpmsftngxa06.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:309919
NNTP-Posting-Host: TK2MSFTNGXA11 10.40.1.163
X-Tomcat-NG: microsoft.public.sqlserver.server
Aside from reinstalling, is there a way to change the
server collation settings?|||Noldz,
You can also set a database's collation when creating it. So a given SQL
Server can have many databases, each with different collations.
Ron
--
Ron Talmage
SQL Server MVP
"Noldz" <rdevera@.wizardsgroup.com> wrote in message
news:09c601c38c81$178ecc10$a301280a@.phx.gbl...
> Aside from reinstalling, is there a way to change the
> server collation settings?|||Hi Ron,
Thanks for replying.
Master DB Collation was set to case-sensitive. So when you
use the fully qualified name in distributed queries
eg. "db.dbo.tabname" instead of "DB.dbo.tabname" it
returns invalid object name "db.dbo.tabname".
I already reinstall my sql server and my web apps works
perfectly.
regards,
Noldz
>--Original Message--
>Noldz,
>You can also set a database's collation when creating it.
So a given SQL
>Server can have many databases, each with different
collations.
>Ron
>--
>Ron Talmage
>SQL Server MVP
>"Noldz" <rdevera@.wizardsgroup.com> wrote in message
>news:09c601c38c81$178ecc10$a301280a@.phx.gbl...
>> Aside from reinstalling, is there a way to change the
>> server collation settings?
>
>.
>
server collation settings?No. You can change the collation of individual columns with ALTER TABLE
ALTER COLUMN, but this has lots of caveats and can be difficult depending
on your schema. If you must change the instance-level default collation,
the only options are (a) a reinstall, or (b) use the Rebuild Master utility
(rebuildm.exe). RebuildM will replace your system databases, so extract or
backup anything you need from them before running it. Also, RebuildM will
change the instance-level defaults but it doesn't change the collation of
any existing user databases.
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
Content-Class: urn:content-classes:message
From: "Noldz" <rdevera@.wizardsgroup.com>
Sender: "Noldz" <rdevera@.wizardsgroup.com>
Subject: How to change Server Collation?
Date: Mon, 6 Oct 2003 20:14:17 -0700
Lines: 2
Message-ID: <09c601c38c81$178ecc10$a301280a@.phx.gbl>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Thread-Index: AcOMgReOEkR3oghcTTStBjXKZ+hd2w==Newsgroups: microsoft.public.sqlserver.server
Path: cpmsftngxa06.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:309919
NNTP-Posting-Host: TK2MSFTNGXA11 10.40.1.163
X-Tomcat-NG: microsoft.public.sqlserver.server
Aside from reinstalling, is there a way to change the
server collation settings?|||Noldz,
You can also set a database's collation when creating it. So a given SQL
Server can have many databases, each with different collations.
Ron
--
Ron Talmage
SQL Server MVP
"Noldz" <rdevera@.wizardsgroup.com> wrote in message
news:09c601c38c81$178ecc10$a301280a@.phx.gbl...
> Aside from reinstalling, is there a way to change the
> server collation settings?|||Hi Ron,
Thanks for replying.
Master DB Collation was set to case-sensitive. So when you
use the fully qualified name in distributed queries
eg. "db.dbo.tabname" instead of "DB.dbo.tabname" it
returns invalid object name "db.dbo.tabname".
I already reinstall my sql server and my web apps works
perfectly.
regards,
Noldz
>--Original Message--
>Noldz,
>You can also set a database's collation when creating it.
So a given SQL
>Server can have many databases, each with different
collations.
>Ron
>--
>Ron Talmage
>SQL Server MVP
>"Noldz" <rdevera@.wizardsgroup.com> wrote in message
>news:09c601c38c81$178ecc10$a301280a@.phx.gbl...
>> Aside from reinstalling, is there a way to change the
>> server collation settings?
>
>.
>
Wednesday, March 21, 2012
how to change collations to SQL_Latin1_General_CP1_CI_AS?
Hi,
I installed SQL Server 2000 SP3a on win2003 in English with the
collation Latin1_General_CI_AS.
Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
I am not really interested to reinstall SQL-server.
I tried the rebuildm.exe-tool but it couldn´t start because some
process was still using SQL-Server (I stopped everything I knew).
Is there a special query or a software-tool which can handle this
transformation?
thanks in advance,
RagnarHi,
> I installed SQL Server 2000 SP3a on win2003 in English with the
> collation Latin1_General_CI_AS.
> Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
ONLY RESTORE?
SQL Server 2000 supports multiple collations.
SEE Books Online
[SQL Server Architecture]
+ [Database Architecture]
+ [Logical Database Components]
+ [Collations]
+ [Mixed Collation Environments]
PARA. Collations in BACKUP and RESTORE
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2003
"Ragnar Heil" <r@.gnar.de> wrote in message
news:8a70509.0309262355.5d06abc1@.posting.google.com...
> Hi,
> I installed SQL Server 2000 SP3a on win2003 in English with the
> collation Latin1_General_CI_AS.
> Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
> I am not really interested to reinstall SQL-server.
> I tried the rebuildm.exe-tool but it couldn´t start because some
> process was still using SQL-Server (I stopped everything I knew).
> Is there a special query or a software-tool which can handle this
> transformation?
> thanks in advance,
> Ragnar|||I thought that the collation of
SQL_Latin1_General_CP1_CI_AS and Latin1_General_CP1_CI_AS
are the same.
Am I right ?|||Hi,
> SQL_Latin1_General_CP1_CI_AS and Latin1_General_CP1_CI_AS
> are the same.
I feel so too.
But,
Latin1_General_CI_AS (Windows collation)
SQL_Latin1_General_CP1_CI_AS (SQL collation)
And Mixed Collation Environments CAUSE Collation conflict ERROR (449)
--
SELECT CategoryName FROM Northwind.dbo.Categories
WHERE
CASE WHEN 1=1
THEN CategoryName COLLATE Latin1_General_CI_AS
ELSE CategoryName COLLATE SQL_Latin1_General_CP1_CI_AS
END
LIKE 'C%' COLLATE Latin1_General_CI_AS
--
CAUSE Collation conflict ERROR.
--
SELECT CategoryName FROM Northwind.dbo.Categories
WHERE
CASE WHEN 1=1
THEN CategoryName COLLATE Latin1_General_CI_AS
ELSE CategoryName COLLATE Latin1_General_CI_AS
END
LIKE 'C%' COLLATE Latin1_General_CI_AS
--
RETURN some records.
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2003
"Johnny" <jtao@.ssc.nsw.gov.au> wrote in message
news:132601c3864c$393336c0$a101280a@.phx.gbl...
> I thought that the collation of
> SQL_Latin1_General_CP1_CI_AS and Latin1_General_CP1_CI_AS
> are the same.
> Am I right ?
>|||> SQL_Latin1_General_CP1_CI_AS and Latin1_General_CP1_CI_AS
> are the same.
They are not quite the same. Latin1_General_CI_AS uses Unicode sorting
rules for both Unicode and non-Unicode data. To see one difference (not
the only one), try the following:
IF 'a-c' COLLATE Latin1_General_CI_AS > 'ab' COLLATE
Latin1_General_CI_AS
PRINT 'Latin1_General_CI_AS: a-c > ab'
ELSE
PRINT 'Latin1_General_CI_AS: a-c < ab'
IF 'a-c' COLLATE SQL_Latin1_General_CP1_CI_AS > 'ab' COLLATE
SQL_Latin1_General_CP1_CI_AS
PRINT 'SQL_Latin1_General_CP1_CI_AS: a-c > ab'
ELSE
PRINT 'SQL_Latin1_General_CP1_CI_AS: a-c < ab'
> I tried the rebuildm.exe-tool but it couldn´t start because some
> process was still using SQL-Server (I stopped everything I knew).
To use rebuildm.exe, make sure that the SQL Server service is fully stopped
(e.g. go to Computer Managment --> Services and stop anything named
"MSSQLServer" or "MSSQL$...").
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
From: "Shinichi Yoneda" <mxl04371@.nifty.ne.jp>
References: <8a70509.0309262355.5d06abc1@.posting.google.com>
<OOX7gGVhDHA.1200@.TK2MSFTNGP12.phx.gbl>
<132601c3864c$393336c0$a101280a@.phx.gbl>
Subject: Re: how to change collations to SQL_Latin1_General_CP1_CI_AS?
Date: Mon, 29 Sep 2003 21:45:35 +0900
Lines: 48
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <ePiiaeohDHA.4024@.TK2MSFTNGP11.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: ntehme007118.ehme.nt.adsl.ppp.infoweb.ne.jp
218.229.98.118
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:308787
X-Tomcat-NG: microsoft.public.sqlserver.server
Hi,
> SQL_Latin1_General_CP1_CI_AS and Latin1_General_CP1_CI_AS
> are the same.
I feel so too.
But,
Latin1_General_CI_AS (Windows collation)
SQL_Latin1_General_CP1_CI_AS (SQL collation)
And Mixed Collation Environments CAUSE Collation conflict ERROR (449)
--
SELECT CategoryName FROM Northwind.dbo.Categories
WHERE
CASE WHEN 1=1
THEN CategoryName COLLATE Latin1_General_CI_AS
ELSE CategoryName COLLATE SQL_Latin1_General_CP1_CI_AS
END
LIKE 'C%' COLLATE Latin1_General_CI_AS
--
CAUSE Collation conflict ERROR.
--
SELECT CategoryName FROM Northwind.dbo.Categories
WHERE
CASE WHEN 1=1
THEN CategoryName COLLATE Latin1_General_CI_AS
ELSE CategoryName COLLATE Latin1_General_CI_AS
END
LIKE 'C%' COLLATE Latin1_General_CI_AS
--
RETURN some records.
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2003
"Johnny" <jtao@.ssc.nsw.gov.au> wrote in message
news:132601c3864c$393336c0$a101280a@.phx.gbl...
> I thought that the collation of
> SQL_Latin1_General_CP1_CI_AS and Latin1_General_CP1_CI_AS
> are the same.
> Am I right ?
>
From: "Shinichi Yoneda" <mxl04371@.nifty.ne.jp>
Subject: Re: how to change collations to SQL_Latin1_General_CP1_CI_AS?
Date: Sun, 28 Sep 2003 08:46:34 +0900
Newsgroups: microsoft.public.sqlserver.server
Hi,
> I installed SQL Server 2000 SP3a on win2003 in English with the
> collation Latin1_General_CI_AS.
> Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
ONLY RESTORE?
SQL Server 2000 supports multiple collations.
SEE Books Online
[SQL Server Architecture]
+ [Database Architecture]
+ [Logical Database Components]
+ [Collations]
+ [Mixed Collation Environments]
PARA. Collations in BACKUP and RESTORE
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2003
"Ragnar Heil" <r@.gnar.de> wrote in message
news:8a70509.0309262355.5d06abc1@.posting.google.com...
> Hi,
> I installed SQL Server 2000 SP3a on win2003 in English with the
> collation Latin1_General_CI_AS.
> Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
> I am not really interested to reinstall SQL-server.
> I tried the rebuildm.exe-tool but it couldn´t start because some
> process was still using SQL-Server (I stopped everything I knew).
> Is there a special query or a software-tool which can handle this
> transformation?
> thanks in advance,
> Ragnar|||"Shinichi Yoneda" <mxl04371@.nifty.ne.jp> wrote
> > I installed SQL Server 2000 SP3a on win2003 in English with the
> > collation Latin1_General_CI_AS.
> > Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
> ONLY RESTORE?
> SQL Server 2000 supports multiple collations.
> SEE Books Online
> [SQL Server Architecture]
> + [Database Architecture]
> + [Logical Database Components]
> + [Collations]
> + [Mixed Collation Environments]
> PARA. Collations in BACKUP and RESTORE
Hello,
I read this chapter and it seems that I just have to create a new DB,
choose the right collation and restore the DB. The message is that
multiple collations are supported and a feature of SQL 2000.
But the fact is that I can't choose "SQL_Latin1_General_CP1_CI_AS"
while creating a new DB.
So what I did is I reinstalled SQL Server, changed regional-settings
to English(US), English (UK) for the local user.
Then I got the right collation.
cheers
Ragnar|||Why you can't choose "SQL_Latin1_General_CP1_CI_AS" while
creating a new DB.
I got a SQL server which has server collation
Latin1_General_CI_AS. It is the same as yours. I can
create a brand new db with collation
SQL_Latin1_General_CP1_CI_AS or Latin1_General_CI_AS.|||On Tue, 30 Sep 2003 21:51:20 -0700, "Johnny" <jtao@.ssc.nsw.gov.au>
wrote:
>Why you can't choose "SQL_Latin1_General_CP1_CI_AS" while
>creating a new DB.
>I got a SQL server which has server collation
>Latin1_General_CI_AS. It is the same as yours. I can
>create a brand new db with collation
>SQL_Latin1_General_CP1_CI_AS or Latin1_General_CI_AS.
Yes, but then you will have problems with temp tables, as they will be
created with the same collation as the master database by default.
You can get around this issue, by specifying
"collation database_default" for each character-type column when you
create your temp tables. Of course, that means you need to create them
up-front, and can't use Select ... Into #xxx From ... anymore.
cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander|||Hi,
(a)TRY
SELECT * FROM ::fn_helpcollations()
YOU CAN SEE list of all the collations supported by YOUR SQL Server 2000.
EX.
Latin1_General_CI_AS,Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive
SQL_Latin1_General_CP1_CI_AS,Latin1-General, case-insensitive,
accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data,
SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data
> But the fact is that I can't choose "SQL_Latin1_General_CP1_CI_AS"
> while creating a new DB.
(b)creating a new DB. BUT WITHOUT COLLATION
RESTORE DATABASE WITH REPLACE
the existing database is completely replaced with the contents of the
database on the backup file.
(AFTER RESTORE, COLLATION SQL_Latin1_General_CP1_CI_AS)
(c)FOR Rebuildm.exe YOU MUST COPY FILES FROM CD TO HDD. REMOVE Read-only
attribute.
http://support.microsoft.com/default.aspx?scid=kb;en-us;273572
BUG: Rebuildm.exe Utility Stops Responding When Source Directory is on a CD
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2003
"Ragnar Heil" <r@.gnar.de> wrote in message
news:8a70509.0309300437.7b617e5b@.posting.google.com...
> "Shinichi Yoneda" <mxl04371@.nifty.ne.jp> wrote
> > > I installed SQL Server 2000 SP3a on win2003 in English with the
> > > collation Latin1_General_CI_AS.
> > > Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
> > ONLY RESTORE?
> > SQL Server 2000 supports multiple collations.
> > SEE Books Online
> > [SQL Server Architecture]
> > + [Database Architecture]
> > + [Logical Database Components]
> > + [Collations]
> > + [Mixed Collation Environments]
> > PARA. Collations in BACKUP and RESTORE
> Hello,
> I read this chapter and it seems that I just have to create a new DB,
> choose the right collation and restore the DB. The message is that
> multiple collations are supported and a feature of SQL 2000.
> But the fact is that I can't choose "SQL_Latin1_General_CP1_CI_AS"
> while creating a new DB.
> So what I did is I reinstalled SQL Server, changed regional-settings
> to English(US), English (UK) for the local user.
> Then I got the right collation.
>
> cheers
> Ragnar
I installed SQL Server 2000 SP3a on win2003 in English with the
collation Latin1_General_CI_AS.
Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
I am not really interested to reinstall SQL-server.
I tried the rebuildm.exe-tool but it couldn´t start because some
process was still using SQL-Server (I stopped everything I knew).
Is there a special query or a software-tool which can handle this
transformation?
thanks in advance,
RagnarHi,
> I installed SQL Server 2000 SP3a on win2003 in English with the
> collation Latin1_General_CI_AS.
> Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
ONLY RESTORE?
SQL Server 2000 supports multiple collations.
SEE Books Online
[SQL Server Architecture]
+ [Database Architecture]
+ [Logical Database Components]
+ [Collations]
+ [Mixed Collation Environments]
PARA. Collations in BACKUP and RESTORE
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2003
"Ragnar Heil" <r@.gnar.de> wrote in message
news:8a70509.0309262355.5d06abc1@.posting.google.com...
> Hi,
> I installed SQL Server 2000 SP3a on win2003 in English with the
> collation Latin1_General_CI_AS.
> Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
> I am not really interested to reinstall SQL-server.
> I tried the rebuildm.exe-tool but it couldn´t start because some
> process was still using SQL-Server (I stopped everything I knew).
> Is there a special query or a software-tool which can handle this
> transformation?
> thanks in advance,
> Ragnar|||I thought that the collation of
SQL_Latin1_General_CP1_CI_AS and Latin1_General_CP1_CI_AS
are the same.
Am I right ?|||Hi,
> SQL_Latin1_General_CP1_CI_AS and Latin1_General_CP1_CI_AS
> are the same.
I feel so too.
But,
Latin1_General_CI_AS (Windows collation)
SQL_Latin1_General_CP1_CI_AS (SQL collation)
And Mixed Collation Environments CAUSE Collation conflict ERROR (449)
--
SELECT CategoryName FROM Northwind.dbo.Categories
WHERE
CASE WHEN 1=1
THEN CategoryName COLLATE Latin1_General_CI_AS
ELSE CategoryName COLLATE SQL_Latin1_General_CP1_CI_AS
END
LIKE 'C%' COLLATE Latin1_General_CI_AS
--
CAUSE Collation conflict ERROR.
--
SELECT CategoryName FROM Northwind.dbo.Categories
WHERE
CASE WHEN 1=1
THEN CategoryName COLLATE Latin1_General_CI_AS
ELSE CategoryName COLLATE Latin1_General_CI_AS
END
LIKE 'C%' COLLATE Latin1_General_CI_AS
--
RETURN some records.
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2003
"Johnny" <jtao@.ssc.nsw.gov.au> wrote in message
news:132601c3864c$393336c0$a101280a@.phx.gbl...
> I thought that the collation of
> SQL_Latin1_General_CP1_CI_AS and Latin1_General_CP1_CI_AS
> are the same.
> Am I right ?
>|||> SQL_Latin1_General_CP1_CI_AS and Latin1_General_CP1_CI_AS
> are the same.
They are not quite the same. Latin1_General_CI_AS uses Unicode sorting
rules for both Unicode and non-Unicode data. To see one difference (not
the only one), try the following:
IF 'a-c' COLLATE Latin1_General_CI_AS > 'ab' COLLATE
Latin1_General_CI_AS
PRINT 'Latin1_General_CI_AS: a-c > ab'
ELSE
PRINT 'Latin1_General_CI_AS: a-c < ab'
IF 'a-c' COLLATE SQL_Latin1_General_CP1_CI_AS > 'ab' COLLATE
SQL_Latin1_General_CP1_CI_AS
PRINT 'SQL_Latin1_General_CP1_CI_AS: a-c > ab'
ELSE
PRINT 'SQL_Latin1_General_CP1_CI_AS: a-c < ab'
> I tried the rebuildm.exe-tool but it couldn´t start because some
> process was still using SQL-Server (I stopped everything I knew).
To use rebuildm.exe, make sure that the SQL Server service is fully stopped
(e.g. go to Computer Managment --> Services and stop anything named
"MSSQLServer" or "MSSQL$...").
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
From: "Shinichi Yoneda" <mxl04371@.nifty.ne.jp>
References: <8a70509.0309262355.5d06abc1@.posting.google.com>
<OOX7gGVhDHA.1200@.TK2MSFTNGP12.phx.gbl>
<132601c3864c$393336c0$a101280a@.phx.gbl>
Subject: Re: how to change collations to SQL_Latin1_General_CP1_CI_AS?
Date: Mon, 29 Sep 2003 21:45:35 +0900
Lines: 48
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <ePiiaeohDHA.4024@.TK2MSFTNGP11.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: ntehme007118.ehme.nt.adsl.ppp.infoweb.ne.jp
218.229.98.118
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:308787
X-Tomcat-NG: microsoft.public.sqlserver.server
Hi,
> SQL_Latin1_General_CP1_CI_AS and Latin1_General_CP1_CI_AS
> are the same.
I feel so too.
But,
Latin1_General_CI_AS (Windows collation)
SQL_Latin1_General_CP1_CI_AS (SQL collation)
And Mixed Collation Environments CAUSE Collation conflict ERROR (449)
--
SELECT CategoryName FROM Northwind.dbo.Categories
WHERE
CASE WHEN 1=1
THEN CategoryName COLLATE Latin1_General_CI_AS
ELSE CategoryName COLLATE SQL_Latin1_General_CP1_CI_AS
END
LIKE 'C%' COLLATE Latin1_General_CI_AS
--
CAUSE Collation conflict ERROR.
--
SELECT CategoryName FROM Northwind.dbo.Categories
WHERE
CASE WHEN 1=1
THEN CategoryName COLLATE Latin1_General_CI_AS
ELSE CategoryName COLLATE Latin1_General_CI_AS
END
LIKE 'C%' COLLATE Latin1_General_CI_AS
--
RETURN some records.
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2003
"Johnny" <jtao@.ssc.nsw.gov.au> wrote in message
news:132601c3864c$393336c0$a101280a@.phx.gbl...
> I thought that the collation of
> SQL_Latin1_General_CP1_CI_AS and Latin1_General_CP1_CI_AS
> are the same.
> Am I right ?
>
From: "Shinichi Yoneda" <mxl04371@.nifty.ne.jp>
Subject: Re: how to change collations to SQL_Latin1_General_CP1_CI_AS?
Date: Sun, 28 Sep 2003 08:46:34 +0900
Newsgroups: microsoft.public.sqlserver.server
Hi,
> I installed SQL Server 2000 SP3a on win2003 in English with the
> collation Latin1_General_CI_AS.
> Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
ONLY RESTORE?
SQL Server 2000 supports multiple collations.
SEE Books Online
[SQL Server Architecture]
+ [Database Architecture]
+ [Logical Database Components]
+ [Collations]
+ [Mixed Collation Environments]
PARA. Collations in BACKUP and RESTORE
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2003
"Ragnar Heil" <r@.gnar.de> wrote in message
news:8a70509.0309262355.5d06abc1@.posting.google.com...
> Hi,
> I installed SQL Server 2000 SP3a on win2003 in English with the
> collation Latin1_General_CI_AS.
> Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
> I am not really interested to reinstall SQL-server.
> I tried the rebuildm.exe-tool but it couldn´t start because some
> process was still using SQL-Server (I stopped everything I knew).
> Is there a special query or a software-tool which can handle this
> transformation?
> thanks in advance,
> Ragnar|||"Shinichi Yoneda" <mxl04371@.nifty.ne.jp> wrote
> > I installed SQL Server 2000 SP3a on win2003 in English with the
> > collation Latin1_General_CI_AS.
> > Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
> ONLY RESTORE?
> SQL Server 2000 supports multiple collations.
> SEE Books Online
> [SQL Server Architecture]
> + [Database Architecture]
> + [Logical Database Components]
> + [Collations]
> + [Mixed Collation Environments]
> PARA. Collations in BACKUP and RESTORE
Hello,
I read this chapter and it seems that I just have to create a new DB,
choose the right collation and restore the DB. The message is that
multiple collations are supported and a feature of SQL 2000.
But the fact is that I can't choose "SQL_Latin1_General_CP1_CI_AS"
while creating a new DB.
So what I did is I reinstalled SQL Server, changed regional-settings
to English(US), English (UK) for the local user.
Then I got the right collation.
cheers
Ragnar|||Why you can't choose "SQL_Latin1_General_CP1_CI_AS" while
creating a new DB.
I got a SQL server which has server collation
Latin1_General_CI_AS. It is the same as yours. I can
create a brand new db with collation
SQL_Latin1_General_CP1_CI_AS or Latin1_General_CI_AS.|||On Tue, 30 Sep 2003 21:51:20 -0700, "Johnny" <jtao@.ssc.nsw.gov.au>
wrote:
>Why you can't choose "SQL_Latin1_General_CP1_CI_AS" while
>creating a new DB.
>I got a SQL server which has server collation
>Latin1_General_CI_AS. It is the same as yours. I can
>create a brand new db with collation
>SQL_Latin1_General_CP1_CI_AS or Latin1_General_CI_AS.
Yes, but then you will have problems with temp tables, as they will be
created with the same collation as the master database by default.
You can get around this issue, by specifying
"collation database_default" for each character-type column when you
create your temp tables. Of course, that means you need to create them
up-front, and can't use Select ... Into #xxx From ... anymore.
cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander|||Hi,
(a)TRY
SELECT * FROM ::fn_helpcollations()
YOU CAN SEE list of all the collations supported by YOUR SQL Server 2000.
EX.
Latin1_General_CI_AS,Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive
SQL_Latin1_General_CP1_CI_AS,Latin1-General, case-insensitive,
accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data,
SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data
> But the fact is that I can't choose "SQL_Latin1_General_CP1_CI_AS"
> while creating a new DB.
(b)creating a new DB. BUT WITHOUT COLLATION
RESTORE DATABASE WITH REPLACE
the existing database is completely replaced with the contents of the
database on the backup file.
(AFTER RESTORE, COLLATION SQL_Latin1_General_CP1_CI_AS)
(c)FOR Rebuildm.exe YOU MUST COPY FILES FROM CD TO HDD. REMOVE Read-only
attribute.
http://support.microsoft.com/default.aspx?scid=kb;en-us;273572
BUG: Rebuildm.exe Utility Stops Responding When Source Directory is on a CD
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2003
"Ragnar Heil" <r@.gnar.de> wrote in message
news:8a70509.0309300437.7b617e5b@.posting.google.com...
> "Shinichi Yoneda" <mxl04371@.nifty.ne.jp> wrote
> > > I installed SQL Server 2000 SP3a on win2003 in English with the
> > > collation Latin1_General_CI_AS.
> > > Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
> > ONLY RESTORE?
> > SQL Server 2000 supports multiple collations.
> > SEE Books Online
> > [SQL Server Architecture]
> > + [Database Architecture]
> > + [Logical Database Components]
> > + [Collations]
> > + [Mixed Collation Environments]
> > PARA. Collations in BACKUP and RESTORE
> Hello,
> I read this chapter and it seems that I just have to create a new DB,
> choose the right collation and restore the DB. The message is that
> multiple collations are supported and a feature of SQL 2000.
> But the fact is that I can't choose "SQL_Latin1_General_CP1_CI_AS"
> while creating a new DB.
> So what I did is I reinstalled SQL Server, changed regional-settings
> to English(US), English (UK) for the local user.
> Then I got the right collation.
>
> cheers
> Ragnar
How to change collation settings of a database
Hi,
what would be the best way to change the collation settings of a database? I
need to change the collation from SQL_Latin1_General_CP1_CI_AS to
Latin1_General_CI_AS.
Thank you a lot in advance!
Danielhttp://msdn.microsoft.com/library/d...br />
819v.asp
ML|||Hi,
have a look at the alter database TSQL in BOL. Thanks
--
--
Thanks,
Ibrahim
If you find this post useful, please click 'yes'
"Daniel Walzenbach" wrote:
> Hi,
> what would be the best way to change the collation settings of a database?
I
> need to change the collation from SQL_Latin1_General_CP1_CI_AS to
> Latin1_General_CI_AS.
> Thank you a lot in advance!
> Daniel|||Thank you ML!
Regards
Daniel
"ML" wrote:
> http://msdn.microsoft.com/library/d... />
a_819v.asp
>
> ML|||Just be make sure you perused the previously posted Books Online topic,
please note the following excerpt:
<Excerpt>
Altering the default collation of a database does not change the collations
of the columns in any existing user-defined tables. These can be changed
with ALTER TABLE. The COLLATE CLAUSE on an ALTER DATABASE statement changes:
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel Walzenbach" <daniel.walzenbach@.newsgroup.nospam> wrote in message
news:E4DEF1D9-83C8-4762-A99E-6B67F94C6261@.microsoft.com...
> Hi,
> what would be the best way to change the collation settings of a database?
> I
> need to change the collation from SQL_Latin1_General_CP1_CI_AS to
> Latin1_General_CI_AS.
> Thank you a lot in advance!
> Daniel|||Hi Dan,
the excerpt you posted is exactly the problem I have. Do you see any
solution to solve this?
Thank you!
Daniel
"Dan Guzman" wrote:
> Just be make sure you perused the previously posted Books Online topic,
> please note the following excerpt:
> <Excerpt>
> Altering the default collation of a database does not change the collation
s
> of the columns in any existing user-defined tables. These can be changed
> with ALTER TABLE. The COLLATE CLAUSE on an ALTER DATABASE statement change
s:
> </Excerpt>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Daniel Walzenbach" <daniel.walzenbach@.newsgroup.nospam> wrote in message
> news:E4DEF1D9-83C8-4762-A99E-6B67F94C6261@.microsoft.com...
>
>|||Unfortunately, there is no easy way to change the collation of all existing
columns to the new collation. You'll need to recreate the tables. See
http://support.microsoft.com/defaul...b;en-us;325335.
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel Walzenbach" <daniel.walzenbach@.newsgroup.nospam> wrote in message
news:F704636E-CA46-42DD-AD1B-07D15243351F@.microsoft.com...
> Hi Dan,
> the excerpt you posted is exactly the problem I have. Do you see any
> solution to solve this?
> Thank you!
> Daniel
> "Dan Guzman" wrote:
>|||Dan,
I feared so ;-)
Thanks for you reply! Regards
Daniel
"Dan Guzman" wrote:
> Unfortunately, there is no easy way to change the collation of all existin
g
> columns to the new collation. You'll need to recreate the tables. See
> http://support.microsoft.com/defaul...b;en-us;325335.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Daniel Walzenbach" <daniel.walzenbach@.newsgroup.nospam> wrote in message
> news:F704636E-CA46-42DD-AD1B-07D15243351F@.microsoft.com...
>
>
what would be the best way to change the collation settings of a database? I
need to change the collation from SQL_Latin1_General_CP1_CI_AS to
Latin1_General_CI_AS.
Thank you a lot in advance!
Danielhttp://msdn.microsoft.com/library/d...br />
819v.asp
ML|||Hi,
have a look at the alter database TSQL in BOL. Thanks
--
--
Thanks,
Ibrahim
If you find this post useful, please click 'yes'
"Daniel Walzenbach" wrote:
> Hi,
> what would be the best way to change the collation settings of a database?
I
> need to change the collation from SQL_Latin1_General_CP1_CI_AS to
> Latin1_General_CI_AS.
> Thank you a lot in advance!
> Daniel|||Thank you ML!
Regards
Daniel
"ML" wrote:
> http://msdn.microsoft.com/library/d... />
a_819v.asp
>
> ML|||Just be make sure you perused the previously posted Books Online topic,
please note the following excerpt:
<Excerpt>
Altering the default collation of a database does not change the collations
of the columns in any existing user-defined tables. These can be changed
with ALTER TABLE. The COLLATE CLAUSE on an ALTER DATABASE statement changes:
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel Walzenbach" <daniel.walzenbach@.newsgroup.nospam> wrote in message
news:E4DEF1D9-83C8-4762-A99E-6B67F94C6261@.microsoft.com...
> Hi,
> what would be the best way to change the collation settings of a database?
> I
> need to change the collation from SQL_Latin1_General_CP1_CI_AS to
> Latin1_General_CI_AS.
> Thank you a lot in advance!
> Daniel|||Hi Dan,
the excerpt you posted is exactly the problem I have. Do you see any
solution to solve this?
Thank you!
Daniel
"Dan Guzman" wrote:
> Just be make sure you perused the previously posted Books Online topic,
> please note the following excerpt:
> <Excerpt>
> Altering the default collation of a database does not change the collation
s
> of the columns in any existing user-defined tables. These can be changed
> with ALTER TABLE. The COLLATE CLAUSE on an ALTER DATABASE statement change
s:
> </Excerpt>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Daniel Walzenbach" <daniel.walzenbach@.newsgroup.nospam> wrote in message
> news:E4DEF1D9-83C8-4762-A99E-6B67F94C6261@.microsoft.com...
>
>|||Unfortunately, there is no easy way to change the collation of all existing
columns to the new collation. You'll need to recreate the tables. See
http://support.microsoft.com/defaul...b;en-us;325335.
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel Walzenbach" <daniel.walzenbach@.newsgroup.nospam> wrote in message
news:F704636E-CA46-42DD-AD1B-07D15243351F@.microsoft.com...
> Hi Dan,
> the excerpt you posted is exactly the problem I have. Do you see any
> solution to solve this?
> Thank you!
> Daniel
> "Dan Guzman" wrote:
>|||Dan,
I feared so ;-)
Thanks for you reply! Regards
Daniel
"Dan Guzman" wrote:
> Unfortunately, there is no easy way to change the collation of all existin
g
> columns to the new collation. You'll need to recreate the tables. See
> http://support.microsoft.com/defaul...b;en-us;325335.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Daniel Walzenbach" <daniel.walzenbach@.newsgroup.nospam> wrote in message
> news:F704636E-CA46-42DD-AD1B-07D15243351F@.microsoft.com...
>
>
How to change collation settings after install
I need to change the collation setting on a server which has already been
installed and setup. How do I do this?
Thanks.
moondaddy@.newsgroup.nospamIt depends on to what extent you want to change collation. The collation for system databases you
change using rebuildm.exe (2000) or setup (2005). See books online for details. This will rebuild
the system databases, so you need to recreate everything you need in the system databases.
A user database is a different thing...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"moondaddy" <moondaddy@.newsgroup.nospam> wrote in message
news:eTbPg7gAIHA.4232@.TK2MSFTNGP04.phx.gbl...
>I need to change the collation setting on a server which has already been installed and setup. How
>do I do this?
> Thanks.
>
> moondaddy@.newsgroup.nospam
>|||Take a look at this tip:
http://www.sqlcommunity.com/Default.aspx?grm2id=25&tabid=77
Hope this helps.
Saleem
WWW.SQLCOMMUNITY.COM
SQL Server Tips, SQL Server Scripts, SQL Server Forums, SQL Server Blogs,
SQL Server Radio, SQL Server Events..
"Tibor Karaszi" wrote:
> It depends on to what extent you want to change collation. The collation for system databases you
> change using rebuildm.exe (2000) or setup (2005). See books online for details. This will rebuild
> the system databases, so you need to recreate everything you need in the system databases.
> A user database is a different thing...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "moondaddy" <moondaddy@.newsgroup.nospam> wrote in message
> news:eTbPg7gAIHA.4232@.TK2MSFTNGP04.phx.gbl...
> >I need to change the collation setting on a server which has already been installed and setup. How
> >do I do this?
> >
> > Thanks.
> >
> >
> > moondaddy@.newsgroup.nospam
> >
>|||Hi,
I agree with Tibor. Just add some helpful links here for your reference.
For SQL 2000, please refer to:
How to rebuild the master database (Rebuild Master utility)
http://msdn2.microsoft.com/en-us/library/aa197950(sql.80).aspx
For SQL 2005, you may refer to:
Setting and Changing the Server Collation
http://msdn2.microsoft.com/en-us/library/ms179254.aspx
How to: Install SQL Server 2005 from the Command Prompt
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
Additionally if you would like to change your user database collation, you
can use ALTER DATABASE statement which is applicable for both SQL 2000 and
2005:
Setting and Changing the Database Collation
http://msdn2.microsoft.com/en-us/library/ms175835.aspx
ALTER DATABASE (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms174269.aspx
Please feel free to let us know if you have any questions or concerns.
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
installed and setup. How do I do this?
Thanks.
moondaddy@.newsgroup.nospamIt depends on to what extent you want to change collation. The collation for system databases you
change using rebuildm.exe (2000) or setup (2005). See books online for details. This will rebuild
the system databases, so you need to recreate everything you need in the system databases.
A user database is a different thing...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"moondaddy" <moondaddy@.newsgroup.nospam> wrote in message
news:eTbPg7gAIHA.4232@.TK2MSFTNGP04.phx.gbl...
>I need to change the collation setting on a server which has already been installed and setup. How
>do I do this?
> Thanks.
>
> moondaddy@.newsgroup.nospam
>|||Take a look at this tip:
http://www.sqlcommunity.com/Default.aspx?grm2id=25&tabid=77
Hope this helps.
Saleem
WWW.SQLCOMMUNITY.COM
SQL Server Tips, SQL Server Scripts, SQL Server Forums, SQL Server Blogs,
SQL Server Radio, SQL Server Events..
"Tibor Karaszi" wrote:
> It depends on to what extent you want to change collation. The collation for system databases you
> change using rebuildm.exe (2000) or setup (2005). See books online for details. This will rebuild
> the system databases, so you need to recreate everything you need in the system databases.
> A user database is a different thing...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "moondaddy" <moondaddy@.newsgroup.nospam> wrote in message
> news:eTbPg7gAIHA.4232@.TK2MSFTNGP04.phx.gbl...
> >I need to change the collation setting on a server which has already been installed and setup. How
> >do I do this?
> >
> > Thanks.
> >
> >
> > moondaddy@.newsgroup.nospam
> >
>|||Hi,
I agree with Tibor. Just add some helpful links here for your reference.
For SQL 2000, please refer to:
How to rebuild the master database (Rebuild Master utility)
http://msdn2.microsoft.com/en-us/library/aa197950(sql.80).aspx
For SQL 2005, you may refer to:
Setting and Changing the Server Collation
http://msdn2.microsoft.com/en-us/library/ms179254.aspx
How to: Install SQL Server 2005 from the Command Prompt
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
Additionally if you would like to change your user database collation, you
can use ALTER DATABASE statement which is applicable for both SQL 2000 and
2005:
Setting and Changing the Database Collation
http://msdn2.microsoft.com/en-us/library/ms175835.aspx
ALTER DATABASE (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms174269.aspx
Please feel free to let us know if you have any questions or concerns.
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
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
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
How to change collation on MODEL ?
> 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 colla
tions across the system
databases. This is done using the SETUP program in 2005. This is described i
n
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 ha
s
> the server collation.
> I have tried to change the "model" collation using ALTER DATABASE, but I g
et
> the error message "Cannot alter the database 'model' because it is a syste
m
> database.
> "
> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?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 colla
tions across the system
databases. This is done using the SETUP program in 2005. This is described i
n
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 ha
s
> the server collation.
> I have tried to change the "model" collation using ALTER DATABASE, but I g
et
> the error message "Cannot alter the database 'model' because it is a syste
m
> 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/defaul...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.|||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/defaul...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 rebuildin
g
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:
> You have to rebuild the system databases, as you cannot have different col
lations 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-a42
0-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...
>|||> 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 col
lation 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...[vbcol=seagreen]
> The doc mentions that all service packs and hotfixes are lost when rebuild
ing
> 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:
>|||The doc mentions that all service packs and hotfixes are lost when rebuildin
g
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:
> You have to rebuild the system databases, as you cannot have different col
lations 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-a42
0-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...
>|||> 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 col
lation 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...[vbcol=seagreen]
> The doc mentions that all service packs and hotfixes are lost when rebuild
ing
> 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:
>|||"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 ha
s
> the server collation.
> I have tried to change the "model" collation using ALTER DATABASE, but I g
et
> the error message "Cannot alter the database 'model' because it is a syste
m
> 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.
> collation as the server collation?
You have to rebuild the system databases, as you cannot have different colla
tions across the system
databases. This is done using the SETUP program in 2005. This is described i
n
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 ha
s
> the server collation.
> I have tried to change the "model" collation using ALTER DATABASE, but I g
et
> the error message "Cannot alter the database 'model' because it is a syste
m
> database.
> "
> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?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 colla
tions across the system
databases. This is done using the SETUP program in 2005. This is described i
n
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 ha
s
> the server collation.
> I have tried to change the "model" collation using ALTER DATABASE, but I g
et
> the error message "Cannot alter the database 'model' because it is a syste
m
> 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/defaul...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.|||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/defaul...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 rebuildin
g
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:
> You have to rebuild the system databases, as you cannot have different col
lations 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-a42
0-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...
>|||> 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 col
lation 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...[vbcol=seagreen]
> The doc mentions that all service packs and hotfixes are lost when rebuild
ing
> 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:
>|||The doc mentions that all service packs and hotfixes are lost when rebuildin
g
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:
> You have to rebuild the system databases, as you cannot have different col
lations 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-a42
0-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...
>|||> 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 col
lation 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...[vbcol=seagreen]
> The doc mentions that all service packs and hotfixes are lost when rebuild
ing
> 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:
>|||"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 ha
s
> the server collation.
> I have tried to change the "model" collation using ALTER DATABASE, but I g
et
> the error message "Cannot alter the database 'model' because it is a syste
m
> 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.
How to change collation on a server?
I use SQL server 2000.
How to change collation on a server?. When I execute "rebuildm.exe" to
change collation on a server, computer always be Hung. Why is it happended?
How to solve it?Hi
Use ALTER DATABASE ... command. Actually the old objects will remain with
old COLLATION.
To change it take a looka ALTER TABLE....ALTER COLUMN ... commands
For more details please refer to the BOL.
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:%23xfHor5VGHA.5076@.TK2MSFTNGP11.phx.gbl...
>I use SQL server 2000.
> How to change collation on a server?. When I execute "rebuildm.exe" to
> change collation on a server, computer always be Hung. Why is it
> happended?
> How to solve it?
>|||Copy the source files for the system database to the hard drive, and *remove
the read-only
attribute* of those files. Then point to that location when you run rebuildm
.exe.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:%23xfHor5VGHA.5076@.TK2MSFTNGP11.phx.gbl...
>I use SQL server 2000.
> How to change collation on a server?. When I execute "rebuildm.exe" to
> change collation on a server, computer always be Hung. Why is it happended
?
> How to solve it?
>
How to change collation on a server?. When I execute "rebuildm.exe" to
change collation on a server, computer always be Hung. Why is it happended?
How to solve it?Hi
Use ALTER DATABASE ... command. Actually the old objects will remain with
old COLLATION.
To change it take a looka ALTER TABLE....ALTER COLUMN ... commands
For more details please refer to the BOL.
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:%23xfHor5VGHA.5076@.TK2MSFTNGP11.phx.gbl...
>I use SQL server 2000.
> How to change collation on a server?. When I execute "rebuildm.exe" to
> change collation on a server, computer always be Hung. Why is it
> happended?
> How to solve it?
>|||Copy the source files for the system database to the hard drive, and *remove
the read-only
attribute* of those files. Then point to that location when you run rebuildm
.exe.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:%23xfHor5VGHA.5076@.TK2MSFTNGP11.phx.gbl...
>I use SQL server 2000.
> How to change collation on a server?. When I execute "rebuildm.exe" to
> change collation on a server, computer always be Hung. Why is it happended
?
> How to solve it?
>
How to change collation name?
How to change collation name of msdb and model database? If it can't how to
export all users in Security\login.
>
> How to change collation name of msdb and model database? If it can't how
to
> export all users in Security\login.
Rebuilding the master database will change the collation of the msdb and
model databases. To export logins, you need to script the logins from your
source server and then run the script from your target server.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Could you please to give example scripts?
"Eric Cárdenas [MSFT]" wrote:
> to
> --
> Rebuilding the master database will change the collation of the msdb and
> model databases. To export logins, you need to script the logins from your
> source server and then run the script from your target server.
> Hope this helps,
> --
> Eric Cárdenas
> Senior support professional
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/kb/246133
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: How to change collation name?
| thread-index: AcTXk+3iPuL+BKKcRdOyxeOn6i5IHA==
| X-WBNR-Posting-Host: 203.151.8.41
| From: "=?Utf-8?B?TWVzYWs=?=" <Mesak@.discussions.microsoft.com>
| References: <3EAEF5CB-E55F-4720-BB81-A4F996613170@.microsoft.com>
<tU#dca31EHA.3820@.cpmsftngxa10.phx.gbl>
| Subject: RE: How to change collation name?
| Date: Wed, 1 Dec 2004 02:53:02 -0800
| Lines: 22
| Message-ID: <DE104C07-1804-49BA-A5CE-AFD8424D24CE@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFT NGP08.phx.gbl!TK2MSFTNGXA0
3.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:369391
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Could you please to give example scripts?
|
| "Eric Cárdenas [MSFT]" wrote:
|
| > >
| > > How to change collation name of msdb and model database? If it can't
how
| > to
| > > export all users in Security\login.
| > --
| > Rebuilding the master database will change the collation of the msdb
and
| > model databases. To export logins, you need to script the logins from
your
| > source server and then run the script from your target server.
| >
| > Hope this helps,
| >
| > --
| > Eric Cárdenas
| > Senior support professional
| >
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
|
export all users in Security\login.
>
> How to change collation name of msdb and model database? If it can't how
to
> export all users in Security\login.
Rebuilding the master database will change the collation of the msdb and
model databases. To export logins, you need to script the logins from your
source server and then run the script from your target server.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Could you please to give example scripts?
"Eric Cárdenas [MSFT]" wrote:
> to
> --
> Rebuilding the master database will change the collation of the msdb and
> model databases. To export logins, you need to script the logins from your
> source server and then run the script from your target server.
> Hope this helps,
> --
> Eric Cárdenas
> Senior support professional
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/kb/246133
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: How to change collation name?
| thread-index: AcTXk+3iPuL+BKKcRdOyxeOn6i5IHA==
| X-WBNR-Posting-Host: 203.151.8.41
| From: "=?Utf-8?B?TWVzYWs=?=" <Mesak@.discussions.microsoft.com>
| References: <3EAEF5CB-E55F-4720-BB81-A4F996613170@.microsoft.com>
<tU#dca31EHA.3820@.cpmsftngxa10.phx.gbl>
| Subject: RE: How to change collation name?
| Date: Wed, 1 Dec 2004 02:53:02 -0800
| Lines: 22
| Message-ID: <DE104C07-1804-49BA-A5CE-AFD8424D24CE@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFT NGP08.phx.gbl!TK2MSFTNGXA0
3.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:369391
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Could you please to give example scripts?
|
| "Eric Cárdenas [MSFT]" wrote:
|
| > >
| > > How to change collation name of msdb and model database? If it can't
how
| > to
| > > export all users in Security\login.
| > --
| > Rebuilding the master database will change the collation of the msdb
and
| > model databases. To export logins, you need to script the logins from
your
| > source server and then run the script from your target server.
| >
| > Hope this helps,
| >
| > --
| > Eric Cárdenas
| > Senior support professional
| >
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
|
Monday, March 19, 2012
How to change collation name?
How to change collation name of msdb and model database? If it can't how to
export all users in Security\login.>
> How to change collation name of msdb and model database? If it can't how
to
> export all users in Security\login.
--
Rebuilding the master database will change the collation of the msdb and
model databases. To export logins, you need to script the logins from your
source server and then run the script from your target server.
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.|||Could you please to give example scripts?
"Eric Cárdenas [MSFT]" wrote:
> >
> > How to change collation name of msdb and model database? If it can't how
> to
> > export all users in Security\login.
> --
> Rebuilding the master database will change the collation of the msdb and
> model databases. To export logins, you need to script the logins from your
> source server and then run the script from your target server.
> Hope this helps,
> --
> Eric Cárdenas
> Senior support professional
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/kb/246133
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: How to change collation name?
| thread-index: AcTXk+3iPuL+BKKcRdOyxeOn6i5IHA==| X-WBNR-Posting-Host: 203.151.8.41
| From: "=?Utf-8?B?TWVzYWs=?=" <Mesak@.discussions.microsoft.com>
| References: <3EAEF5CB-E55F-4720-BB81-A4F996613170@.microsoft.com>
<tU#dca31EHA.3820@.cpmsftngxa10.phx.gbl>
| Subject: RE: How to change collation name?
| Date: Wed, 1 Dec 2004 02:53:02 -0800
| Lines: 22
| Message-ID: <DE104C07-1804-49BA-A5CE-AFD8424D24CE@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA0
3.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:369391
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Could you please to give example scripts?
|
| "Eric Cárdenas [MSFT]" wrote:
|
| > >
| > > How to change collation name of msdb and model database? If it can't
how
| > to
| > > export all users in Security\login.
| > --
| > Rebuilding the master database will change the collation of the msdb
and
| > model databases. To export logins, you need to script the logins from
your
| > source server and then run the script from your target server.
| >
| > Hope this helps,
| >
| > --
| > Eric Cárdenas
| > Senior support professional
| >
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
|
export all users in Security\login.>
> How to change collation name of msdb and model database? If it can't how
to
> export all users in Security\login.
--
Rebuilding the master database will change the collation of the msdb and
model databases. To export logins, you need to script the logins from your
source server and then run the script from your target server.
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.|||Could you please to give example scripts?
"Eric Cárdenas [MSFT]" wrote:
> >
> > How to change collation name of msdb and model database? If it can't how
> to
> > export all users in Security\login.
> --
> Rebuilding the master database will change the collation of the msdb and
> model databases. To export logins, you need to script the logins from your
> source server and then run the script from your target server.
> Hope this helps,
> --
> Eric Cárdenas
> Senior support professional
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/kb/246133
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: How to change collation name?
| thread-index: AcTXk+3iPuL+BKKcRdOyxeOn6i5IHA==| X-WBNR-Posting-Host: 203.151.8.41
| From: "=?Utf-8?B?TWVzYWs=?=" <Mesak@.discussions.microsoft.com>
| References: <3EAEF5CB-E55F-4720-BB81-A4F996613170@.microsoft.com>
<tU#dca31EHA.3820@.cpmsftngxa10.phx.gbl>
| Subject: RE: How to change collation name?
| Date: Wed, 1 Dec 2004 02:53:02 -0800
| Lines: 22
| Message-ID: <DE104C07-1804-49BA-A5CE-AFD8424D24CE@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA0
3.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:369391
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Could you please to give example scripts?
|
| "Eric Cárdenas [MSFT]" wrote:
|
| > >
| > > How to change collation name of msdb and model database? If it can't
how
| > to
| > > export all users in Security\login.
| > --
| > Rebuilding the master database will change the collation of the msdb
and
| > model databases. To export logins, you need to script the logins from
your
| > source server and then run the script from your target server.
| >
| > Hope this helps,
| >
| > --
| > Eric Cárdenas
| > Senior support professional
| >
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
|
Subscribe to:
Posts (Atom)