Clustered DB Server: following

Showing posts with label following. Show all posts

Sunday, March 25, 2012

collation error...

I have a database on one SQL server.
I have backed up the database and then restored it onto another SQLServer.
I have the following SQL statement:
exec myStoredProcedure 'stress', NULL,NULL, 1, -1
When I run it against the database on serverA it works, but when I run it
against the backup SQL server I get the following error message:
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
Server: Msg 446, Level 16, State 1, Line 1
Cannot resolve collation conflict for equal to operation.
I don't know how to fix this...
I presume that it's the server collation, but don't know how to update it.
Please advise...
Thanks
GriffIs there a join aganst a temp table? If the system db's are a different
collation and the join is there, that would explain it. To change server
collation, you'll need to use the rebuildm utility, and there are lots of
caveats there because you will loose all of your system database objects
like jobs, maintenance plans, logins, dts packages, and so on.
See REBUILDM or rebuild master in books on line.
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:Oz7R447zDHA.536@.tk2msftngp13.phx.gbl...
> I have a database on one SQL server.
> I have backed up the database and then restored it onto another SQLServer.
> I have the following SQL statement:
> exec myStoredProcedure 'stress', NULL,NULL, 1, -1
> When I run it against the database on serverA it works, but when I run it
> against the backup SQL server I get the following error message:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> Server: Msg 446, Level 16, State 1, Line 1
> Cannot resolve collation conflict for equal to operation.
> I don't know how to fix this...
> I presume that it's the server collation, but don't know how to update it.
> Please advise...
> Thanks
> Griff
>|||Great help - thanks.
Had to resort to that... as you pointed out, now need to rebuild everything.
That will have to wait until next year though!
Happy New Year
Griff

Collation difference between SQL 6.5 server and SQL 2000 server

Hello,
We are facing the following problem:
We have installed SQL 6.5 server Enterprise edition on Windows 2000
Advanced server. On this system, in the regional settings, the Locale
is Japanese and in Office 2003 Language settings also, we have added
Japanese.
We have another system with Windows 2000 server on which we have
installed SQL 2000 server Enterprise edition with SP4. On this system,
in the regional settings, the Locale is Japanese and in Office 2003
Language settings also, we have added Japanese.

>From the front end VB code, a query is formed which contains Japanese
value. This works in the SQL 6.5 environment but does not work in the
SQL 2000 environment. If we directly execute the query in SQL 2000
server, it is returning the row. But the VB front end is not
recognizing the row.
The query is like this:
SELECT * FROM Tstar_Currency WHERE Currency =3D N'=C6=CE=DD'
This query works when the VB client connects to the SQL 6.5 server and
the front end fetches one row. But when the VB client connects to SQL
2000 server, the front end is not fetching any row. However, if we
directly execute this in query analyzer in SQL 2000 server, it returns
one row.
Any idea why this is happening? What collation setting should we give
in the SQL 2000 server? Do we need to do anything in the front end
application?
Any help will be greatly appreciated. This is very urgent. Please also
mail to ravi.meenakshi@.gmail.com
Thanks!I would sugest running the VB app in debug mode and see what happens when
the VB app issues the select statment. In addition at the same time you may
open Profiler.
The collation you are using in sql 6.5 works well for you so I would try to
keep the same collation with sql 2000.
Be aware that with sql2000 you have collation at three levels: server,
database and columns.
Verify that you have installed sql 2000 with the desired collation.
Yaniv
--
<Ravi.Meenakshi@.gmail.com> wrote in message
news:1135763223.233337.224300@.g49g2000cwa.googlegroups.com...
Hello,
We are facing the following problem:
We have installed SQL 6.5 server Enterprise edition on Windows 2000
Advanced server. On this system, in the regional settings, the Locale
is Japanese and in Office 2003 Language settings also, we have added
Japanese.
We have another system with Windows 2000 server on which we have
installed SQL 2000 server Enterprise edition with SP4. On this system,
in the regional settings, the Locale is Japanese and in Office 2003
Language settings also, we have added Japanese.

>From the front end VB code, a query is formed which contains Japanese
value. This works in the SQL 6.5 environment but does not work in the
SQL 2000 environment. If we directly execute the query in SQL 2000
server, it is returning the row. But the VB front end is not
recognizing the row.
The query is like this:
SELECT * FROM Tstar_Currency WHERE Currency = N''
This query works when the VB client connects to the SQL 6.5 server and
the front end fetches one row. But when the VB client connects to SQL
2000 server, the front end is not fetching any row. However, if we
directly execute this in query analyzer in SQL 2000 server, it returns
one row.
Any idea why this is happening? What collation setting should we give
in the SQL 2000 server? Do we need to do anything in the front end
application?
Any help will be greatly appreciated. This is very urgent. Please also
mail to ravi.meenakshi@.gmail.com
Thanks!|||(Ravi.Meenakshi@.gmail.com) writes:
> We have installed SQL 6.5 server Enterprise edition on Windows 2000
> Advanced server. On this system, in the regional settings, the Locale
> is Japanese and in Office 2003 Language settings also, we have added
> Japanese.
> We have another system with Windows 2000 server on which we have
> installed SQL 2000 server Enterprise edition with SP4. On this system,
> in the regional settings, the Locale is Japanese and in Office 2003
> Language settings also, we have added Japanese.
>
> value. This works in the SQL 6.5 environment but does not work in the
> SQL 2000 environment. If we directly execute the query in SQL 2000
> server, it is returning the row. But the VB front end is not
> recognizing the row.
> The query is like this:
> SELECT * FROM Tstar_Currency WHERE Currency = N''
> This query works when the VB client connects to the SQL 6.5 server and
> the front end fetches one row. But when the VB client connects to SQL
> 2000 server, the front end is not fetching any row. However, if we
> directly execute this in query analyzer in SQL 2000 server, it returns
> one row.
Wait a minute, that query does not even have legal syntax for
SQL 6.5. Is the N really there?
What does sp_helpsort return on SQL 6.5?
On SQL 2000, what does SELECT Serverproperty('Collation') return?
If you run sp_help on Tstar_Currency on SQL 2000, which is the
data type, and which is the collation for that column?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The N is there in the query only in SQL 2000, not in 6.5
The data type of Currency column in Tstar_Currency in SQL 6.5 is
char(20) and in SQL 2000 is nchar(20)
The collation of this column in SQL 2000 is Japanese_CI_AS
The only change in the VB code is the inclusion of N before the
Japanese value.
Any clues on how to solve the problem?|||I ran profiler for the 2000 environment. In the query I first saw in
the profiler, I saw boxes. Then I changed the font in the Profiler to
Japanese. Then the query displayed like this:
SELECT MSCICode FROM Tstar_Currency WHERE Currency =3D N'=EF=BE=86=EF=BE=8E=
=EF=BE=9D'
When I executed this query in query analyzer, it did not return any
rows.
Please note that the client PC on which the VB application is running,
the regional settings is changed to Japanese.
I can't understand why the Japanese value was not passed correctly from
VB to SQL Server.
Any clues?|||(Ravi.Meenakshi@.gmail.com) writes:
> The N is there in the query only in SQL 2000, not in 6.5
> The data type of Currency column in Tstar_Currency in SQL 6.5 is
> char(20) and in SQL 2000 is nchar(20)
> The collation of this column in SQL 2000 is Japanese_CI_AS
> The only change in the VB code is the inclusion of N before the
> Japanese value.
> Any clues on how to solve the problem?
I asked you these questions:
What does sp_helpsort return on SQL 6.5?
On SQL 2000, what does SELECT Serverproperty('Collation') return?
It's difficult to give clues, if I don't get any.
But it does sound funny to me that the sole difference should be the
addition of the N. If the characters in the string are Unicode, it would
not work on 6.5. Since it works on 6.5, I suspect that the string is in
some other character set. In which case it will of course not work with
a Unicode column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The output of sp_helpsort in 65. is:
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 52, nocase
Case-insensitive dictionary sort order for use with several We
stern-European languages including English, French, and German
. Uses the ISO 8859-1 character set.
The output of SELECT Serverproperty('Collation') in SQL 2000 is
SQL_Latin1_General_CP1_CI_AS
The collation setting of the SQL 2000 server is the default setting.
The collation for the database that the front end code is accessing is
Japanese_CI_AS|||(Ravi.Meenakshi@.gmail.com) writes:
> The output of sp_helpsort in 65. is:
> Character Set = 1, iso_1
> ISO 8859-1 (Latin-1) - Western European 8-bit character set.
> Sort Order = 52, nocase
> Case-insensitive dictionary sort order for use with several We
> stern-European languages including English, French, and German
> . Uses the ISO 8859-1 character set.
> The output of SELECT Serverproperty('Collation') in SQL 2000 is
It sounds like a miracle if you get Japanese text correcly into that
server. Or at least a great deal of luck.

> SQL_Latin1_General_CP1_CI_AS
> The collation setting of the SQL 2000 server is the default setting.
> The collation for the database that the front end code is accessing is
> Japanese_CI_AS
I have not really managed to fully understand at what collation the
client APIs looks at when they convert data between character sets,
but I suspect that's the server collation. In which case Japanese text
that is the ANSI code page, will be mangled before it's sent to SQL Server.
Apparently you are building a an entire command string in VB. Try to use
parameterised command instead, as then you can declare the type of the
parameter.
You could also try to install an instance of SQL 2000 that has
Japanese_CI_AS as its server collation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql sql

Collation confusion (may be not)

Hello to everyone
I am litlle bit confused with the following sitatuion
OS (Win2000 SERVER) has COLLATE HEBREW_CI_AS (Let me call it A)
I have database (Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort
Order 138 on Code Page 1255 for non-Unicode Data) and employee table.
In the table we have Lastname column declared as VARCHAR(50)
Now I'm sitting on another Server with OS (Win2000 SERVER) COLLATE
LATIN-CENERAL...
I registered the 'A' SQL Server and with QA I performed SELECT lastname
From Table ,works fine (I see all lastname's in hebrew) after that I did
exactly the same statement but by EM and instead of lastname's I have seen
question's mark.
What is going on? Can some shed light on the problem
Note: I know that I can declare NVARCHAR(20) and change COLLATION and it
will be worked but my question is
Are there different ways SQL Server perfoming query by QA and EM.? Why I see
my lastname column correctly when I perfom the query with QA and questions
mark by perfomning the same query with EM
(Note: I am sitting on another server and registered to server 'A' with EM)
Thank you.The "show all rows" function in Enterprise Manager uses non-Unicode
controls. This means that Enterprise Manager can't be used to view data
that is not defined in that client machine's ANSI code page (see
HKLM\SYSTEM\CurrentControlSet\Control\Nls\CodePage, value "ACP" to find out
what this is on a given SQL client machine, but don't change the code page
directly from this reg key).
In other words, don't expect that you can view arbitrary Unicode data from
that feature in Enterprise Manager. Query Analyzer, as a fully
Unicode-enabled app, can be used to display or store any Unicode data.
HTH,
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
From: "Uri Dimant" <urid@.iscar.co.il>
Subject: Collation confusion (may be not)
Date: Tue, 11 Nov 2003 10:14:25 +0200
Lines: 28
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <#Ck$qvCqDHA.372@.TK2MSFTNGP11.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: bzq-25-106-78.cust.bezeqint.net 212.25.106.78
Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXA06.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP0
8.phx.gbl!TK2MSFTNGP11.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:315781
X-Tomcat-NG: microsoft.public.sqlserver.server
Hello to everyone
I am litlle bit confused with the following sitatuion
OS (Win2000 SERVER) has COLLATE HEBREW_CI_AS (Let me call it A)
I have database (Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort
Order 138 on Code Page 1255 for non-Unicode Data) and employee table.
In the table we have Lastname column declared as VARCHAR(50)
Now I'm sitting on another Server with OS (Win2000 SERVER) COLLATE
LATIN-CENERAL...
I registered the 'A' SQL Server and with QA I performed SELECT lastname
From Table ,works fine (I see all lastname's in hebrew) after that I did
exactly the same statement but by EM and instead of lastname's I have seen
question's mark.
What is going on? Can some shed light on the problem
Note: I know that I can declare NVARCHAR(20) and change COLLATION and it
will be worked but my question is
Are there different ways SQL Server perfoming query by QA and EM.? Why I see
my lastname column correctly when I perfom the query with QA and questions
mark by perfomning the same query with EM
(Note: I am sitting on another server and registered to server 'A' with EM)
Thank you.

Thursday, March 22, 2012

Collation Conflict

Hi,
I am running a query over 2 tables in 2 different databases.
I get the following error "Cannot resolve collation conflict for equal to
operation"
for example
SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
WHERE [TB2].[ProdGroup]=@.PG
@.PG is a string parameter.
Regards
TimModify your query so that it converts your joins to
unicode data. This will make it collation independant.
PS Do you want to know why it went wrong or are you ok
with it ?
J
>--Original Message--
>Hi,
>I am running a query over 2 tables in 2 different
databases.
>I get the following error "Cannot resolve collation
conflict for equal to
>operation"
>for example
>SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
[dbo].[TB1] [TB1]
>INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
>WHERE [TB2].[ProdGroup]=@.PG
>@.PG is a string parameter.
>Regards
>Tim
>
>.
>|||Your TEMPDB collation differs to your database,
You either need to preform Julie option or do a rebuildm to the correct
collation but this would wipe out your users, and user databases (you can
reattached).
J
"Tim Marsden" <TM@.UK.COM> wrote in message
news:e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am running a query over 2 tables in 2 different databases.
> I get the following error "Cannot resolve collation conflict for equal to
> operation"
> for example
> SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo].[TB1] [TB1]
> INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
> WHERE [TB2].[ProdGroup]=@.PG
> @.PG is a string parameter.
> Regards
> Tim
>|||Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx.gbl...
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> >--Original Message--
> >Hi,
> >
> >I am running a query over 2 tables in 2 different
> databases.
> >I get the following error "Cannot resolve collation
> conflict for equal to
> >operation"
> >
> >for example
> >
> >SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
> [dbo].[TB1] [TB1]
> >INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=> [TB2].[ProductCode])
> >WHERE [TB2].[ProdGroup]=@.PG
> >
> >@.PG is a string parameter.
> >
> >Regards
> >Tim
> >
> >
> >.
> >|||Your [TB1].[Product] and [TB2].[ProductCode] columns have different
collations, so the result of the join expression is ambiguous. Take a
look at the BOL topic "Collation Precedence" -- it provides a good
explanation of the problem. You can avoid this fairly trivially by
providing a COLLATE clause that removes the ambiguity like this:
SELECT TOP 100 [TB1].[Product] AS Q0000000
FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2]
ON [TB1].[Product]=[TB2].[ProductCode]) COLLATE database_default
WHERE [TB2].[ProdGroup]=@.PG
but this will make it impossible for the QP to use an index seek on the
right side of the join. If this is a big problem it may be better to
change the collation of one of the two columns (using ALTER TABLE ALTER
COLUMN) so that the collations match. Note that to run ALTER COLUMN on a
column's collation you must first drop any indexes, stats, or constraints
that reference the column.
Bart
--
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.
From: "Tim Marsden" <TM@.UK.COM>
References: <e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl>
<11c6101c4423b$568580b0$a301280a@.phx.gbl>
Subject: Re: Collation Conflict
Date: Tue, 25 May 2004 14:49:31 +0100
Lines: 46
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <OC3oY8lQEHA.3988@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host213-122-182-242.in-addr.btopenworld.com
213.122.182.242
Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp
13.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:342917
X-Tomcat-NG: microsoft.public.sqlserver.server
Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx.gbl...
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> >--Original Message--
> >Hi,
> >
> >I am running a query over 2 tables in 2 different
> databases.
> >I get the following error "Cannot resolve collation
> conflict for equal to
> >operation"
> >
> >for example
> >
> >SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
> [dbo].[TB1] [TB1]
> >INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=> [TB2].[ProductCode])
> >WHERE [TB2].[ProdGroup]=@.PG
> >
> >@.PG is a string parameter.
> >
> >Regards
> >Tim
> >
> >
> >.
> >

Collation Conflict

Tuesday, March 20, 2012

collation & compatibility issues

1) how to check the colllation settings on SQL 7 ?
2) I have 2 SQL Srv - SQL7 and SQL2000.
A client application does the following:
- retrieves data from SQL2000 to the SQL7 for queries.
- data updated in SQL7 is also periodically syn with the SQL2000 srv.
there is no direct user acess with the tables, all is interfaced thru the
client app. So must the SQL Server 2000 compatibility settings (under server
properties) be changed to 7.0 ?
Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
SQL2000 from being accessed by other app.
The WAIT TYPE is usually networkIO for the blocking connection from SQL7 to
SQL2000.
So I suspect could it be collation settings or some other in-compatibility
between the 2 SQL Servers.>
> 1) how to check the collation settings on SQL 7 ?
--
The error log will tell you this information.
> 2) I have 2 SQL Srv - SQL7 and SQL2000.
> A client application does the following:
> - retrieves data from SQL2000 to the SQL7 for queries.
> - data updated in SQL7 is also periodically syn with the SQL2000 srv.
> there is no direct user acess with the tables, all is interfaced thru the
> client app. So must the SQL Server 2000 compatibility settings (under
server
> properties) be changed to 7.0 ?
> Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
> SQL2000 from being accessed by other app.
> The WAIT TYPE is usually networkIO for the blocking connection from SQL7
to
> SQL2000.
> So I suspect could it be collation settings or some other in-compatibility
> between the 2 SQL Servers.
--
You should run a blocker script to identify the top of blocking chains. The
script from this article might help:
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Regards,
--
Eric Cárdenas
SQL Server senior support professional

Collation

I have following collation setting in my current sql 7 server.
Locale ID = 33280
case sensitive
Sort Order Description
Character Set = 2, cp850
Code Page 850 (Multilingual) character set.
Sort Order = 40, bin_cp850
Binary Sort Order for the CodePage 850 Character Set
I plan to install sql 2000 on a new server and restore backup from sql 7.
What Locale ID and collation setting I should be using in sql2000.
Thanks
Select "SQL collations" at the collation selection dialog, then select
"Binary Order, for use with the CodePage 850 Character Set" (same sort
order that sp_helpsort reports for your 7.0 server).
Bart
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: Collation
| thread-index: AcSLbDA3HenK5wRzRMK500SXnoE2ZQ==
| X-WBNR-Posting-Host: 199.20.71.17
| From: "=?Utf-8?B?U0IxMzg=?=" <SB138@.discussions.microsoft.com>
| Subject: Collation
| Date: Thu, 26 Aug 2004 05:57:05 -0700
| Lines: 14
| Message-ID: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| 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!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:356327
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I have following collation setting in my current sql 7 server.
| Locale ID = 33280
| case sensitive
| Sort Order Description
|
| Character Set = 2, cp850
| Code Page 850 (Multilingual) character set.
| Sort Order = 40, bin_cp850
| Binary Sort Order for the CodePage 850 Character Set
|
| I plan to install sql 2000 on a new server and restore backup from sql 7.
| What Locale ID and collation setting I should be using in sql2000.
| Thanks
|
|
|||Hi Bart,
I select "Binary order, for use with the 850(Multilingual) character set"
sp_helpsort on sql 2000 gives following:
Latin1-General, binary sort for Unicode Data, SQL Server Sort Order 40 on
Code Page 850 for non-Unicode Data
Whereas in sql 7 installation it gives:
case sensitive
Sort Order Description
Character Set = 2, cp850
Code Page 850 (Multilingual) character set.
Sort Order = 40, bin_cp850
Binary Sort Order for the CodePage 850 Character Set
What I ma missing?
"Bart Duncan [MSFT]" wrote:

> Select "SQL collations" at the collation selection dialog, then select
> "Binary Order, for use with the CodePage 850 Character Set" (same sort
> order that sp_helpsort reports for your 7.0 server).
> Bart
> --
> 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: Collation
> | thread-index: AcSLbDA3HenK5wRzRMK500SXnoE2ZQ==
> | X-WBNR-Posting-Host: 199.20.71.17
> | From: "=?Utf-8?B?U0IxMzg=?=" <SB138@.discussions.microsoft.com>
> | Subject: Collation
> | Date: Thu, 26 Aug 2004 05:57:05 -0700
> | Lines: 14
> | Message-ID: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | 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!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:356327
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | I have following collation setting in my current sql 7 server.
> | Locale ID = 33280
> | case sensitive
> | Sort Order Description
> |
> | Character Set = 2, cp850
> | Code Page 850 (Multilingual) character set.
> | Sort Order = 40, bin_cp850
> | Binary Sort Order for the CodePage 850 Character Set
> |
> | I plan to install sql 2000 on a new server and restore backup from sql 7.
> | What Locale ID and collation setting I should be using in sql2000.
> | Thanks
> |
> |
>
|||sp_helpsort formats its output differently on the two versions.
Every collation specifies three things: (1) the sorting rules to use for
Unicode data, (2) the code page to use for non-Unicode data, and (3) the
sort order to use for non-Unicode data. Per your 7.0 sp_helpsort output
your 7.0 collation has the following properties:
1. Unicode sort = binary ("locale 33280" is binary)
2. Non-Unicode code page = 850
3. Non-Unicode sort order = 40
Your 2000 server has the same collation properties.
Try the backup and restore. Then run "sp_helpdb" to find the collation
name of the upgraded database, and SELECT SERVERPROPERTY('Collation') to
find the SQL 2000 server's collation name. They should be the same. If
they are not the same, reply back with the database's and SQL 2000 server's
collation names.
HTH,
Bart
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: Collation
| thread-index: AcSWm3Hjx0LhTQwxScuh1HZyoGdqpA==
| X-WBNR-Posting-Host: 199.20.71.17
| From: "=?Utf-8?B?U0IxMzg=?=" <SB138@.discussions.microsoft.com>
| References: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
<o8n2qv4iEHA.2968@.cpmsftngxa10.phx.gbl>
| Subject: RE: Collation
| Date: Thu, 9 Sep 2004 11:33:04 -0700
| Lines: 73
| Message-ID: <9FCF40ED-A3BB-4935-8486-DADDF4E04F61@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| 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!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:358302
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hi Bart,
| I select "Binary order, for use with the 850(Multilingual) character set"
| sp_helpsort on sql 2000 gives following:
| Latin1-General, binary sort for Unicode Data, SQL Server Sort Order 40 on
| Code Page 850 for non-Unicode Data
|
| Whereas in sql 7 installation it gives:
| case sensitive
| Sort Order Description
|
| Character Set = 2, cp850
| Code Page 850 (Multilingual) character set.
| Sort Order = 40, bin_cp850
| Binary Sort Order for the CodePage 850 Character Set
| What I ma missing?
|
| "Bart Duncan [MSFT]" wrote:
|
| > Select "SQL collations" at the collation selection dialog, then select
| > "Binary Order, for use with the CodePage 850 Character Set" (same sort
| > order that sp_helpsort reports for your 7.0 server).
| >
| > Bart
| > --
| > 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: Collation
| > | thread-index: AcSLbDA3HenK5wRzRMK500SXnoE2ZQ==
| > | X-WBNR-Posting-Host: 199.20.71.17
| > | From: "=?Utf-8?B?U0IxMzg=?=" <SB138@.discussions.microsoft.com>
| > | Subject: Collation
| > | Date: Thu, 26 Aug 2004 05:57:05 -0700
| > | Lines: 14
| > | Message-ID: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | 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!TK2MSFTNGXA03.phx.gbl
| > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:356327
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | I have following collation setting in my current sql 7 server.
| > | Locale ID = 33280
| > | case sensitive
| > | Sort Order Description
| >
| > |
| > | Character Set = 2, cp850
| > | Code Page 850 (Multilingual) character set.
| > | Sort Order = 40, bin_cp850
| > | Binary Sort Order for the CodePage 850 Character Set
| > |
| > | I plan to install sql 2000 on a new server and restore backup from
sql 7.
| > | What Locale ID and collation setting I should be using in sql2000.
| > | Thanks
| > |
| > |
| >
| >
|
|||Thanks for clarifying.
How I can find out what locale_id in 2K, and is it case sensitive or
insensitive? I just want to be sure that collation settings on 2000 are same
as 7 before I restore sql 7 backup onto sql 2K.
"Bart Duncan [MSFT]" wrote:

> sp_helpsort formats its output differently on the two versions.
> Every collation specifies three things: (1) the sorting rules to use for
> Unicode data, (2) the code page to use for non-Unicode data, and (3) the
> sort order to use for non-Unicode data. Per your 7.0 sp_helpsort output
> your 7.0 collation has the following properties:
> 1. Unicode sort = binary ("locale 33280" is binary)
> 2. Non-Unicode code page = 850
> 3. Non-Unicode sort order = 40
> Your 2000 server has the same collation properties.
> Try the backup and restore. Then run "sp_helpdb" to find the collation
> name of the upgraded database, and SELECT SERVERPROPERTY('Collation') to
> find the SQL 2000 server's collation name. They should be the same. If
> they are not the same, reply back with the database's and SQL 2000 server's
> collation names.
> HTH,
> Bart
> --
> 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: Collation
> | thread-index: AcSWm3Hjx0LhTQwxScuh1HZyoGdqpA==
> | X-WBNR-Posting-Host: 199.20.71.17
> | From: "=?Utf-8?B?U0IxMzg=?=" <SB138@.discussions.microsoft.com>
> | References: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
> <o8n2qv4iEHA.2968@.cpmsftngxa10.phx.gbl>
> | Subject: RE: Collation
> | Date: Thu, 9 Sep 2004 11:33:04 -0700
> | Lines: 73
> | Message-ID: <9FCF40ED-A3BB-4935-8486-DADDF4E04F61@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | 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!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:358302
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Hi Bart,
> | I select "Binary order, for use with the 850(Multilingual) character set"
> | sp_helpsort on sql 2000 gives following:
> | Latin1-General, binary sort for Unicode Data, SQL Server Sort Order 40 on
> | Code Page 850 for non-Unicode Data
> |
> | Whereas in sql 7 installation it gives:
> | case sensitive
> | Sort Order Description
> |
> | Character Set = 2, cp850
> | Code Page 850 (Multilingual) character set.
> | Sort Order = 40, bin_cp850
> | Binary Sort Order for the CodePage 850 Character Set
> | What I ma missing?
> |
> | "Bart Duncan [MSFT]" wrote:
> |
> | > Select "SQL collations" at the collation selection dialog, then select
> | > "Binary Order, for use with the CodePage 850 Character Set" (same sort
> | > order that sp_helpsort reports for your 7.0 server).
> | >
> | > Bart
> | > --
> | > 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: Collation
> | > | thread-index: AcSLbDA3HenK5wRzRMK500SXnoE2ZQ==
> | > | X-WBNR-Posting-Host: 199.20.71.17
> | > | From: "=?Utf-8?B?U0IxMzg=?=" <SB138@.discussions.microsoft.com>
> | > | Subject: Collation
> | > | Date: Thu, 26 Aug 2004 05:57:05 -0700
> | > | Lines: 14
> | > | Message-ID: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
> | > | MIME-Version: 1.0
> | > | Content-Type: text/plain;
> | > | charset="Utf-8"
> | > | Content-Transfer-Encoding: 7bit
> | > | 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!TK2MSFTNGXA03.phx.gbl
> | > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:356327
> | > | X-Tomcat-NG: microsoft.public.sqlserver.server
> | > |
> | > | I have following collation setting in my current sql 7 server.
> | > | Locale ID = 33280
> | > | case sensitive
> | > | Sort Order Description
> | >
> | > |
> | > | Character Set = 2, cp850
> | > | Code Page 850 (Multilingual) character set.
> | > | Sort Order = 40, bin_cp850
> | > | Binary Sort Order for the CodePage 850 Character Set
> | > |
> | > | I plan to install sql 2000 on a new server and restore backup from
> sql 7.
> | > | What Locale ID and collation setting I should be using in sql2000.
> | > | Thanks
> | > |
> | > |
> | >
> | >
> |
>
|||SQL 7.0 lets you indepently specify different values for the three
properties of a collation. If you really want to you can set up a 7.0
server with totally inconsistent collation settings (for example, binary
non-Unicode sort order, Japanese non-Unicode code page, Greek case
insensitive Unicode sorting). In SQL 2000, though, the three collation
properties are no longer independently configurable; you just specify a
single collation, and that collation name implicitly determines the three
collation properties (Unicode sorting, non-Unicode sorting + non-Unicode
code page). The reason for this change in SQL 2000 is to try to ensure
that the handling of non-Unicode data and Unicode data are as consistent as
possible.
One side effect of this change is that SQL 2000's sp_helpsort output does
not report the three properties independently, in the same way that 7.0
does. In the 7.0 sp_helpsort output, Unicode sorting (this is called
"locale ID" in the 7.0 sp_helpsort) is identified explicitly by a specific
number, but in SQL 2000 this setting is only implied by the collation
identifier. In the 7.0 Books Online topic "Unicode Collation" you'll find
a list of locale IDs. This will tell you that 33280 is the binary Unicode
locale, which should be equivalent to "binary sort for Unicode Data" in the
SQL 2000 sp_helpsort output.
If you're thinking that this is a lot of trouble to go through just to
figure out what SQL 2000 setup settings you need to select to get a server
with the same collation settings as your 7.0 server, I agree; it's
unfortunately more complex than it could be. For what it's worth, there
have been some attempts to make improvements in this area in Yukon setup.
Bart
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: Collation
| thread-index: AcSZnOYevuO9yaKqSoifBdvA0OJFLQ==
| X-WBNR-Posting-Host: 199.20.71.17
| From: "=?Utf-8?B?U0IxMzg=?=" <SB138@.discussions.microsoft.com>
| References: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
<o8n2qv4iEHA.2968@.cpmsftngxa10.phx.gbl>
<9FCF40ED-A3BB-4935-8486-DADDF4E04F61@.microsoft.com>
<mrGZsi2lEHA.3608@.cpmsftngxa10.phx.gbl>
| Subject: RE: Collation
| Date: Mon, 13 Sep 2004 07:21:02 -0700
| Lines: 140
| Message-ID: <5BC4C012-F922-40D2-AC4F-0CC17429381B@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| 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!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:358661
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Thanks for clarifying.
| How I can find out what locale_id in 2K, and is it case sensitive or
| insensitive? I just want to be sure that collation settings on 2000 are
same
| as 7 before I restore sql 7 backup onto sql 2K.
| "Bart Duncan [MSFT]" wrote:
|
| > sp_helpsort formats its output differently on the two versions.
| >
| > Every collation specifies three things: (1) the sorting rules to use
for
| > Unicode data, (2) the code page to use for non-Unicode data, and (3)
the
| > sort order to use for non-Unicode data. Per your 7.0 sp_helpsort
output
| > your 7.0 collation has the following properties:
| > 1. Unicode sort = binary ("locale 33280" is binary)
| > 2. Non-Unicode code page = 850
| > 3. Non-Unicode sort order = 40
| >
| > Your 2000 server has the same collation properties.
| >
| > Try the backup and restore. Then run "sp_helpdb" to find the collation
| > name of the upgraded database, and SELECT SERVERPROPERTY('Collation')
to
| > find the SQL 2000 server's collation name. They should be the same.
If
| > they are not the same, reply back with the database's and SQL 2000
server's
| > collation names.
| >
| > HTH,
| > Bart
| > --
| > 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: Collation
| > | thread-index: AcSWm3Hjx0LhTQwxScuh1HZyoGdqpA==
| > | X-WBNR-Posting-Host: 199.20.71.17
| > | From: "=?Utf-8?B?U0IxMzg=?=" <SB138@.discussions.microsoft.com>
| > | References: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
| > <o8n2qv4iEHA.2968@.cpmsftngxa10.phx.gbl>
| > | Subject: RE: Collation
| > | Date: Thu, 9 Sep 2004 11:33:04 -0700
| > | Lines: 73
| > | Message-ID: <9FCF40ED-A3BB-4935-8486-DADDF4E04F61@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | 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!TK2MSFTNGXA03.phx.gbl
| > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:358302
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | Hi Bart,
| > | I select "Binary order, for use with the 850(Multilingual) character
set"
| > | sp_helpsort on sql 2000 gives following:
| > | Latin1-General, binary sort for Unicode Data, SQL Server Sort Order
40 on
| > | Code Page 850 for non-Unicode Data
| > |
| > | Whereas in sql 7 installation it gives:
| > | case sensitive
| > | Sort Order Description
| >
| > |
| > | Character Set = 2, cp850
| > | Code Page 850 (Multilingual) character set.
| > | Sort Order = 40, bin_cp850
| > | Binary Sort Order for the CodePage 850 Character Set
| > | What I ma missing?
| > |
| > | "Bart Duncan [MSFT]" wrote:
| > |
| > | > Select "SQL collations" at the collation selection dialog, then
select
| > | > "Binary Order, for use with the CodePage 850 Character Set" (same
sort
| > | > order that sp_helpsort reports for your 7.0 server).
| > | >
| > | > Bart
| > | > --
| > | > 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: Collation
| > | > | thread-index: AcSLbDA3HenK5wRzRMK500SXnoE2ZQ==
| > | > | X-WBNR-Posting-Host: 199.20.71.17
| > | > | From: "=?Utf-8?B?U0IxMzg=?=" <SB138@.discussions.microsoft.com>
| > | > | Subject: Collation
| > | > | Date: Thu, 26 Aug 2004 05:57:05 -0700
| > | > | Lines: 14
| > | > | Message-ID: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
| > | > | MIME-Version: 1.0
| > | > | Content-Type: text/plain;
| > | > | charset="Utf-8"
| > | > | Content-Transfer-Encoding: 7bit
| > | > | 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!TK2MSFTNGXA03.phx.gbl
| > | > | Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.server:356327
| > | > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > | > |
| > | > | I have following collation setting in my current sql 7 server.
| > | > | Locale ID = 33280
| > | > | case sensitive
| > | > | Sort Order Description
| >
| > | >
| > | > |
| > | > | Character Set = 2, cp850
| > | > | Code Page 850 (Multilingual) character set.
| > | > | Sort Order = 40, bin_cp850
| > | > | Binary Sort Order for the CodePage 850 Character Set
| > | > |
| > | > | I plan to install sql 2000 on a new server and restore backup
from
| > sql 7.
| > | > | What Locale ID and collation setting I should be using in sql2000.
| > | > | Thanks
| > | > |
| > | > |
| > | >
| > | >
| > |
| >
| >
|

Collation

I have following collation setting in my current sql 7 server.
Locale ID = 33280
case sensitive
Sort Order Description
Character Set = 2, cp850
Code Page 850 (Multilingual) character set.
Sort Order = 40, bin_cp850
Binary Sort Order for the CodePage 850 Character Set
I plan to install sql 2000 on a new server and restore backup from sql 7.
What Locale ID and collation setting I should be using in sql2000.
ThanksSelect "SQL collations" at the collation selection dialog, then select
"Binary Order, for use with the CodePage 850 Character Set" (same sort
order that sp_helpsort reports for your 7.0 server).
Bart
--
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: Collation
| thread-index: AcSLbDA3HenK5wRzRMK500SXnoE2ZQ==
| X-WBNR-Posting-Host: 199.20.71.17
| From: "examnotes" <SB138@.discussions.microsoft.com>
| Subject: Collation
| Date: Thu, 26 Aug 2004 05:57:05 -0700
| Lines: 14
| Message-ID: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| 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!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:356327
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I have following collation setting in my current sql 7 server.
| Locale ID = 33280
| case sensitive
| Sort Order Description
|
| Character Set = 2, cp850
| Code Page 850 (Multilingual) character set.
| Sort Order = 40, bin_cp850
| Binary Sort Order for the CodePage 850 Character Set
|
| I plan to install sql 2000 on a new server and restore backup from sql 7.
| What Locale ID and collation setting I should be using in sql2000.
| Thanks
|
||||Hi Bart,
I select "Binary order, for use with the 850(Multilingual) character set"
sp_helpsort on sql 2000 gives following:
Latin1-General, binary sort for Unicode Data, SQL Server Sort Order 40 on
Code Page 850 for non-Unicode Data
Whereas in sql 7 installation it gives:
case sensitive
Sort Order Description
Character Set = 2, cp850
Code Page 850 (Multilingual) character set.
Sort Order = 40, bin_cp850
Binary Sort Order for the CodePage 850 Character Set
What I ma missing?
"Bart Duncan [MSFT]" wrote:

> Select "SQL collations" at the collation selection dialog, then select
> "Binary Order, for use with the CodePage 850 Character Set" (same sort
> order that sp_helpsort reports for your 7.0 server).
> Bart
> --
> 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: Collation
> | thread-index: AcSLbDA3HenK5wRzRMK500SXnoE2ZQ==
> | X-WBNR-Posting-Host: 199.20.71.17
> | From: "examnotes" <SB138@.discussions.microsoft.com>
> | Subject: Collation
> | Date: Thu, 26 Aug 2004 05:57:05 -0700
> | Lines: 14
> | Message-ID: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | 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!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:356327
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | I have following collation setting in my current sql 7 server.
> | Locale ID = 33280
> | case sensitive
> | Sort Order Description
> |
> | Character Set = 2, cp850
> | Code Page 850 (Multilingual) character set.
> | Sort Order = 40, bin_cp850
> | Binary Sort Order for the CodePage 850 Character Set
> |
> | I plan to install sql 2000 on a new server and restore backup from sql 7
.
> | What Locale ID and collation setting I should be using in sql2000.
> | Thanks
> |
> |
>|||sp_helpsort formats its output differently on the two versions.
Every collation specifies three things: (1) the sorting rules to use for
Unicode data, (2) the code page to use for non-Unicode data, and (3) the
sort order to use for non-Unicode data. Per your 7.0 sp_helpsort output
your 7.0 collation has the following properties:
1. Unicode sort = binary ("locale 33280" is binary)
2. Non-Unicode code page = 850
3. Non-Unicode sort order = 40
Your 2000 server has the same collation properties.
Try the backup and restore. Then run "sp_helpdb" to find the collation
name of the upgraded database, and SELECT SERVERPROPERTY('Collation') to
find the SQL 2000 server's collation name. They should be the same. If
they are not the same, reply back with the database's and SQL 2000 server's
collation names.
HTH,
Bart
--
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: Collation
| thread-index: AcSWm3Hjx0LhTQwxScuh1HZyoGdqpA==
| X-WBNR-Posting-Host: 199.20.71.17
| From: "examnotes" <SB138@.discussions.microsoft.com>
| References: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
<o8n2qv4iEHA.2968@.cpmsftngxa10.phx.gbl>
| Subject: RE: Collation
| Date: Thu, 9 Sep 2004 11:33:04 -0700
| Lines: 73
| Message-ID: <9FCF40ED-A3BB-4935-8486-DADDF4E04F61@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| 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!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:358302
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hi Bart,
| I select "Binary order, for use with the 850(Multilingual) character set"
| sp_helpsort on sql 2000 gives following:
| Latin1-General, binary sort for Unicode Data, SQL Server Sort Order 40 on
| Code Page 850 for non-Unicode Data
|
| Whereas in sql 7 installation it gives:
| case sensitive
| Sort Order Description
|
| Character Set = 2, cp850
| Code Page 850 (Multilingual) character set.
| Sort Order = 40, bin_cp850
| Binary Sort Order for the CodePage 850 Character Set
| What I ma missing?
|
| "Bart Duncan [MSFT]" wrote:
|
| > Select "SQL collations" at the collation selection dialog, then select
| > "Binary Order, for use with the CodePage 850 Character Set" (same sort
| > order that sp_helpsort reports for your 7.0 server).
| >
| > Bart
| > --
| > 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: Collation
| > | thread-index: AcSLbDA3HenK5wRzRMK500SXnoE2ZQ==
| > | X-WBNR-Posting-Host: 199.20.71.17
| > | From: "examnotes" <SB138@.discussions.microsoft.com>
| > | Subject: Collation
| > | Date: Thu, 26 Aug 2004 05:57:05 -0700
| > | Lines: 14
| > | Message-ID: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | 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!TK2MSFTNGXA03.phx.gbl
| > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:356327
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | I have following collation setting in my current sql 7 server.
| > | Locale ID = 33280
| > | case sensitive
| > | Sort Order Description
| >
| > |
| > | Character Set = 2, cp850
| > | Code Page 850 (Multilingual) character set.
| > | Sort Order = 40, bin_cp850
| > | Binary Sort Order for the CodePage 850 Character Set
| > |
| > | I plan to install sql 2000 on a new server and restore backup from
sql 7.
| > | What Locale ID and collation setting I should be using in sql2000.
| > | Thanks
| > |
| > |
| >
| >
||||Thanks for clarifying.
How I can find out what locale_id in 2K, and is it case sensitive or
insensitive? I just want to be sure that collation settings on 2000 are same
as 7 before I restore sql 7 backup onto sql 2K.
"Bart Duncan [MSFT]" wrote:

> sp_helpsort formats its output differently on the two versions.
> Every collation specifies three things: (1) the sorting rules to use for
> Unicode data, (2) the code page to use for non-Unicode data, and (3) the
> sort order to use for non-Unicode data. Per your 7.0 sp_helpsort output
> your 7.0 collation has the following properties:
> 1. Unicode sort = binary ("locale 33280" is binary)
> 2. Non-Unicode code page = 850
> 3. Non-Unicode sort order = 40
> Your 2000 server has the same collation properties.
> Try the backup and restore. Then run "sp_helpdb" to find the collation
> name of the upgraded database, and SELECT SERVERPROPERTY('Collation') to
> find the SQL 2000 server's collation name. They should be the same. If
> they are not the same, reply back with the database's and SQL 2000 server'
s
> collation names.
> HTH,
> Bart
> --
> 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: Collation
> | thread-index: AcSWm3Hjx0LhTQwxScuh1HZyoGdqpA==
> | X-WBNR-Posting-Host: 199.20.71.17
> | From: "examnotes" <SB138@.discussions.microsoft.com>
> | References: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
> <o8n2qv4iEHA.2968@.cpmsftngxa10.phx.gbl>
> | Subject: RE: Collation
> | Date: Thu, 9 Sep 2004 11:33:04 -0700
> | Lines: 73
> | Message-ID: <9FCF40ED-A3BB-4935-8486-DADDF4E04F61@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | 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!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:358302
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Hi Bart,
> | I select "Binary order, for use with the 850(Multilingual) character se
t"
> | sp_helpsort on sql 2000 gives following:
> | Latin1-General, binary sort for Unicode Data, SQL Server Sort Order 40 o
n
> | Code Page 850 for non-Unicode Data
> |
> | Whereas in sql 7 installation it gives:
> | case sensitive
> | Sort Order Description
> |
> | Character Set = 2, cp850
> | Code Page 850 (Multilingual) character set.
> | Sort Order = 40, bin_cp850
> | Binary Sort Order for the CodePage 850 Character Set
> | What I ma missing?
> |
> | "Bart Duncan [MSFT]" wrote:
> |
> | > Select "SQL collations" at the collation selection dialog, then select
> | > "Binary Order, for use with the CodePage 850 Character Set" (same sort
> | > order that sp_helpsort reports for your 7.0 server).
> | >
> | > Bart
> | > --
> | > 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: Collation
> | > | thread-index: AcSLbDA3HenK5wRzRMK500SXnoE2ZQ==
> | > | X-WBNR-Posting-Host: 199.20.71.17
> | > | From: "examnotes" <SB138@.discussions.microsoft.com>
> | > | Subject: Collation
> | > | Date: Thu, 26 Aug 2004 05:57:05 -0700
> | > | Lines: 14
> | > | Message-ID: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
> | > | MIME-Version: 1.0
> | > | Content-Type: text/plain;
> | > | charset="Utf-8"
> | > | Content-Transfer-Encoding: 7bit
> | > | 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!TK2MSFTNGXA03.phx.gbl
> | > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:356327
> | > | X-Tomcat-NG: microsoft.public.sqlserver.server
> | > |
> | > | I have following collation setting in my current sql 7 server.
> | > | Locale ID = 33280
> | > | case sensitive
> | > | Sort Order Description
> | >
> | > |
> | > | Character Set = 2, cp850
> | > | Code Page 850 (Multilingual) character set.
> | > | Sort Order = 40, bin_cp850
> | > | Binary Sort Order for the CodePage 850 Character Set
> | > |
> | > | I plan to install sql 2000 on a new server and restore backup from
> sql 7.
> | > | What Locale ID and collation setting I should be using in sql2000.
> | > | Thanks
> | > |
> | > |
> | >
> | >
> |
>|||SQL 7.0 lets you indepently specify different values for the three
properties of a collation. If you really want to you can set up a 7.0
server with totally inconsistent collation settings (for example, binary
non-Unicode sort order, Japanese non-Unicode code page, Greek case
insensitive Unicode sorting). In SQL 2000, though, the three collation
properties are no longer independently configurable; you just specify a
single collation, and that collation name implicitly determines the three
collation properties (Unicode sorting, non-Unicode sorting + non-Unicode
code page). The reason for this change in SQL 2000 is to try to ensure
that the handling of non-Unicode data and Unicode data are as consistent as
possible.
One side effect of this change is that SQL 2000's sp_helpsort output does
not report the three properties independently, in the same way that 7.0
does. In the 7.0 sp_helpsort output, Unicode sorting (this is called
"locale ID" in the 7.0 sp_helpsort) is identified explicitly by a specific
number, but in SQL 2000 this setting is only implied by the collation
identifier. In the 7.0 Books Online topic "Unicode Collation" you'll find
a list of locale IDs. This will tell you that 33280 is the binary Unicode
locale, which should be equivalent to "binary sort for Unicode Data" in the
SQL 2000 sp_helpsort output.
If you're thinking that this is a lot of trouble to go through just to
figure out what SQL 2000 setup settings you need to select to get a server
with the same collation settings as your 7.0 server, I agree; it's
unfortunately more complex than it could be. For what it's worth, there
have been some attempts to make improvements in this area in Yukon setup.
Bart
--
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: Collation
| thread-index: AcSZnOYevuO9yaKqSoifBdvA0OJFLQ==
| X-WBNR-Posting-Host: 199.20.71.17
| From: "examnotes" <SB138@.discussions.microsoft.com>
| References: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
<o8n2qv4iEHA.2968@.cpmsftngxa10.phx.gbl>
<9FCF40ED-A3BB-4935-8486-DADDF4E04F61@.microsoft.com>
<mrGZsi2lEHA.3608@.cpmsftngxa10.phx.gbl>
| Subject: RE: Collation
| Date: Mon, 13 Sep 2004 07:21:02 -0700
| Lines: 140
| Message-ID: <5BC4C012-F922-40D2-AC4F-0CC17429381B@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| 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!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:358661
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Thanks for clarifying.
| How I can find out what locale_id in 2K, and is it case sensitive or
| insensitive? I just want to be sure that collation settings on 2000 are
same
| as 7 before I restore sql 7 backup onto sql 2K.
| "Bart Duncan [MSFT]" wrote:
|
| > sp_helpsort formats its output differently on the two versions.
| >
| > Every collation specifies three things: (1) the sorting rules to use
for
| > Unicode data, (2) the code page to use for non-Unicode data, and (3)
the
| > sort order to use for non-Unicode data. Per your 7.0 sp_helpsort
output
| > your 7.0 collation has the following properties:
| > 1. Unicode sort = binary ("locale 33280" is binary)
| > 2. Non-Unicode code page = 850
| > 3. Non-Unicode sort order = 40
| >
| > Your 2000 server has the same collation properties.
| >
| > Try the backup and restore. Then run "sp_helpdb" to find the collation
| > name of the upgraded database, and SELECT SERVERPROPERTY('Collation')
to
| > find the SQL 2000 server's collation name. They should be the same.
If
| > they are not the same, reply back with the database's and SQL 2000
server's
| > collation names.
| >
| > HTH,
| > Bart
| > --
| > 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: Collation
| > | thread-index: AcSWm3Hjx0LhTQwxScuh1HZyoGdqpA==
| > | X-WBNR-Posting-Host: 199.20.71.17
| > | From: "examnotes" <SB138@.discussions.microsoft.com>
| > | References: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
| > <o8n2qv4iEHA.2968@.cpmsftngxa10.phx.gbl>
| > | Subject: RE: Collation
| > | Date: Thu, 9 Sep 2004 11:33:04 -0700
| > | Lines: 73
| > | Message-ID: <9FCF40ED-A3BB-4935-8486-DADDF4E04F61@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | 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!TK2MSFTNGXA03.phx.gbl
| > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:358302
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | Hi Bart,
| > | I select "Binary order, for use with the 850(Multilingual) character
set"
| > | sp_helpsort on sql 2000 gives following:
| > | Latin1-General, binary sort for Unicode Data, SQL Server Sort Order
40 on
| > | Code Page 850 for non-Unicode Data
| > |
| > | Whereas in sql 7 installation it gives:
| > | case sensitive
| > | Sort Order Description
| >
| > |
| > | Character Set = 2, cp850
| > | Code Page 850 (Multilingual) character set.
| > | Sort Order = 40, bin_cp850
| > | Binary Sort Order for the CodePage 850 Character Set
| > | What I ma missing?
| > |
| > | "Bart Duncan [MSFT]" wrote:
| > |
| > | > Select "SQL collations" at the collation selection dialog, then
select
| > | > "Binary Order, for use with the CodePage 850 Character Set" (same
sort
| > | > order that sp_helpsort reports for your 7.0 server).
| > | >
| > | > Bart
| > | > --
| > | > 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: Collation
| > | > | thread-index: AcSLbDA3HenK5wRzRMK500SXnoE2ZQ==
| > | > | X-WBNR-Posting-Host: 199.20.71.17
| > | > | From: "examnotes" <SB138@.discussions.microsoft.com>
| > | > | Subject: Collation
| > | > | Date: Thu, 26 Aug 2004 05:57:05 -0700
| > | > | Lines: 14
| > | > | Message-ID: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
| > | > | MIME-Version: 1.0
| > | > | Content-Type: text/plain;
| > | > | charset="Utf-8"
| > | > | Content-Transfer-Encoding: 7bit
| > | > | 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!TK2MSFTNGXA03.phx.gbl
| > | > | Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.server:356327
| > | > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > | > |
| > | > | I have following collation setting in my current sql 7 server.
| > | > | Locale ID = 33280
| > | > | case sensitive
| > | > | Sort Order Description
| >
| > | >
| > | > |
| > | > | Character Set = 2, cp850
| > | > | Code Page 850 (Multilingual) character set.
| > | > | Sort Order = 40, bin_cp850
| > | > | Binary Sort Order for the CodePage 850 Character Set
| > | > |
| > | > | I plan to install sql 2000 on a new server and restore backup
from
| > sql 7.
| > | > | What Locale ID and collation setting I should be using in sql2000.
| > | > | Thanks
| > | > |
| > | > |
| > | >
| > | >
| > |
| >
| >
|

Monday, March 19, 2012

COLLATE Question

Greetings to all. It would thank for much to them if they
could help me with a problem, that is the following one: I
have a data base in a server x with a table with a field
that has the COLLATE in Hungarian_CS_AI, and at the time
of concatenating this field with a field that has the
COLLATE of the SQL_Latin1_General_CP1_CI_AI type and when
executing this marks an error to me and it solved it
adding at the end of the sentence that the COLLATE of a
single type, but that is not what I look for but what I
would like that somebody explains to me in case a type of
COLLATE exists that can support to all the types like a
UNICODE or something similar. It is for this reason that I
need to know if it is possible to do that single collate
accepts all type of characters. Thanks.Have you tried to bring the data with the different collation types into another table that has only one collation. Do you need the Hungarian collation still? If so I am assuming that the Hungarian collation (due to the need for more special characters) would allow for all Latin1 permissible characters and therefore try to port your Latin collation data to Hungarian collation.|||So why not use Unicode?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Miramontes" <anonymous@.discussions.microsoft.com> wrote in message
news:1afb001c44f3f$e3dad580$a401280a@.phx.gbl...
> Greetings to all. It would thank for much to them if they
> could help me with a problem, that is the following one: I
> have a data base in a server x with a table with a field
> that has the COLLATE in Hungarian_CS_AI, and at the time
> of concatenating this field with a field that has the
> COLLATE of the SQL_Latin1_General_CP1_CI_AI type and when
> executing this marks an error to me and it solved it
> adding at the end of the sentence that the COLLATE of a
> single type, but that is not what I look for but what I
> would like that somebody explains to me in case a type of
> COLLATE exists that can support to all the types like a
> UNICODE or something similar. It is for this reason that I
> need to know if it is possible to do that single collate
> accepts all type of characters. Thanks.|||Do you now the name for this type of collate for
unicode ?, because i need that unicode.
Thanks in advanced.
>--Original Message--
>So why not use Unicode?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Miramontes" <anonymous@.discussions.microsoft.com> wrote
in message
>news:1afb001c44f3f$e3dad580$a401280a@.phx.gbl...
>> Greetings to all. It would thank for much to them if
they
>> could help me with a problem, that is the following
one: I
>> have a data base in a server x with a table with a field
>> that has the COLLATE in Hungarian_CS_AI, and at the time
>> of concatenating this field with a field that has the
>> COLLATE of the SQL_Latin1_General_CP1_CI_AI type and
when
>> executing this marks an error to me and it solved it
>> adding at the end of the sentence that the COLLATE of a
>> single type, but that is not what I look for but what I
>> would like that somebody explains to me in case a type
of
>> COLLATE exists that can support to all the types like a
>> UNICODE or something similar. It is for this reason
that I
>> need to know if it is possible to do that single collate
>> accepts all type of characters. Thanks.
>
>.
>|||Unicode is the datatype (actually nvarchar, nchar and ntext). You then pick a collation to go with it. Seems
like Hungarian_CS_AI should be the one to use, as the other is "general", hence shouldn't care how the
"extended" characters are sorted etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Miramontes" <anonymous@.discussions.microsoft.com> wrote in message
news:1b69901c44fba$dffe8fd0$a001280a@.phx.gbl...
> Do you now the name for this type of collate for
> unicode ?, because i need that unicode.
> Thanks in advanced.
> >--Original Message--
> >So why not use Unicode?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Miramontes" <anonymous@.discussions.microsoft.com> wrote
> in message
> >news:1afb001c44f3f$e3dad580$a401280a@.phx.gbl...
> >> Greetings to all. It would thank for much to them if
> they
> >> could help me with a problem, that is the following
> one: I
> >> have a data base in a server x with a table with a field
> >> that has the COLLATE in Hungarian_CS_AI, and at the time
> >> of concatenating this field with a field that has the
> >> COLLATE of the SQL_Latin1_General_CP1_CI_AI type and
> when
> >> executing this marks an error to me and it solved it
> >> adding at the end of the sentence that the COLLATE of a
> >> single type, but that is not what I look for but what I
> >> would like that somebody explains to me in case a type
> of
> >> COLLATE exists that can support to all the types like a
> >> UNICODE or something similar. It is for this reason
> that I
> >> need to know if it is possible to do that single collate
> >> accepts all type of characters. Thanks.
> >
> >
> >.
> >

Collate

Hi,
Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL/NULL do in the following code?
CREATE TABLE [TABLE1] (
[ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL )
Why do I need COLLATE? What does COLLATION mean in table creation?
Thanks a lot!
Mike
Collate sets the order that items are compared with. It determines how an
order by is handled. It also determines whether two items are the same or
different. The collation you have there states that "APPLE" = "Apple" A
different collation will make these different.
This is one of the annoying quirks of the tool to create SQL. Since
individual columns can have their own collation, the tool exports all of the
collations even if the collation is the default for the database.
Unless you have a good reason to use a specific collation, I would delete
the whole collate clause.
Russel Loski, MCSD.Net
"Michael" wrote:

> Hi,
> Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL/NULL do in the following code?
> CREATE TABLE [TABLE1] (
> [ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL )
> Why do I need COLLATE? What does COLLATION mean in table creation?
> Thanks a lot!
> Mike
>
|||Thanks a lot!
RLoski wrote:[vbcol=seagreen]
> Collate sets the order that items are compared with. It determines how an
> order by is handled. It also determines whether two items are the same or
> different. The collation you have there states that "APPLE" = "Apple" A
> different collation will make these different.
> This is one of the annoying quirks of the tool to create SQL. Since
> individual columns can have their own collation, the tool exports all of the
> collations even if the collation is the default for the database.
> Unless you have a good reason to use a specific collation, I would delete
> the whole collate clause.
> --
> Russel Loski, MCSD.Net
>
> "Michael" wrote:
|||RLoski,
While generating your script, use unicode option. Then it will not generate
the collate... statements. so you don't need to go and delete them manually.
Venkat
"RLoski" wrote:
[vbcol=seagreen]
> Collate sets the order that items are compared with. It determines how an
> order by is handled. It also determines whether two items are the same or
> different. The collation you have there states that "APPLE" = "Apple" A
> different collation will make these different.
> This is one of the annoying quirks of the tool to create SQL. Since
> individual columns can have their own collation, the tool exports all of the
> collations even if the collation is the default for the database.
> Unless you have a good reason to use a specific collation, I would delete
> the whole collate clause.
> --
> Russel Loski, MCSD.Net
>
> "Michael" wrote:

Collate

Hi,
Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL/NULL do in the following code?
CREATE TABLE [TABLE1] (
[ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL )
Why do I need COLLATE? What does COLLATION mean in table creation?
Thanks a lot!
MikeCollate sets the order that items are compared with. It determines how an
order by is handled. It also determines whether two items are the same or
different. The collation you have there states that "APPLE" = "Apple" A
different collation will make these different.
This is one of the annoying quirks of the tool to create SQL. Since
individual columns can have their own collation, the tool exports all of the
collations even if the collation is the default for the database.
Unless you have a good reason to use a specific collation, I would delete
the whole collate clause.
Russel Loski, MCSD.Net
"Michael" wrote:

> Hi,
> Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL/NULL do in the following code?
> CREATE TABLE [TABLE1] (
> [ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT
> NULL )
> Why do I need COLLATE? What does COLLATION mean in table creation?
> Thanks a lot!
> Mike
>|||Thanks a lot!
RLoski wrote:[vbcol=seagreen]
> Collate sets the order that items are compared with. It determines how an
> order by is handled. It also determines whether two items are the same or
> different. The collation you have there states that "APPLE" = "Apple" A
> different collation will make these different.
> This is one of the annoying quirks of the tool to create SQL. Since
> individual columns can have their own collation, the tool exports all of t
he
> collations even if the collation is the default for the database.
> Unless you have a good reason to use a specific collation, I would delete
> the whole collate clause.
> --
> Russel Loski, MCSD.Net
>
> "Michael" wrote:
>|||RLoski,
While generating your script, use unicode option. Then it will not generate
the collate... statements. so you don't need to go and delete them manually.
Venkat
"RLoski" wrote:
[vbcol=seagreen]
> Collate sets the order that items are compared with. It determines how an
> order by is handled. It also determines whether two items are the same or
> different. The collation you have there states that "APPLE" = "Apple" A
> different collation will make these different.
> This is one of the annoying quirks of the tool to create SQL. Since
> individual columns can have their own collation, the tool exports all of t
he
> collations even if the collation is the default for the database.
> Unless you have a good reason to use a specific collation, I would delete
> the whole collate clause.
> --
> Russel Loski, MCSD.Net
>
> "Michael" wrote:
>

Collate

Hi,
Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL/NULL do in the following code?
CREATE TABLE [TABLE1] (
[ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL )
Why do I need COLLATE? What does COLLATION mean in table creation?
Thanks a lot!
MikeThanks a lot!
RLoski wrote:
> Collate sets the order that items are compared with. It determines how an
> order by is handled. It also determines whether two items are the same or
> different. The collation you have there states that "APPLE" = "Apple" A
> different collation will make these different.
> This is one of the annoying quirks of the tool to create SQL. Since
> individual columns can have their own collation, the tool exports all of the
> collations even if the collation is the default for the database.
> Unless you have a good reason to use a specific collation, I would delete
> the whole collate clause.
> --
> Russel Loski, MCSD.Net
>
> "Michael" wrote:
> > Hi,
> >
> > Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> > NULL/NULL do in the following code?
> >
> > CREATE TABLE [TABLE1] (
> > [ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> > NULL )
> >
> > Why do I need COLLATE? What does COLLATION mean in table creation?
> >
> > Thanks a lot!
> > Mike
> >
> >|||RLoski,
While generating your script, use unicode option. Then it will not generate
the collate... statements. so you don't need to go and delete them manually.
Venkat
"RLoski" wrote:
> Collate sets the order that items are compared with. It determines how an
> order by is handled. It also determines whether two items are the same or
> different. The collation you have there states that "APPLE" = "Apple" A
> different collation will make these different.
> This is one of the annoying quirks of the tool to create SQL. Since
> individual columns can have their own collation, the tool exports all of the
> collations even if the collation is the default for the database.
> Unless you have a good reason to use a specific collation, I would delete
> the whole collate clause.
> --
> Russel Loski, MCSD.Net
>
> "Michael" wrote:
> > Hi,
> >
> > Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> > NULL/NULL do in the following code?
> >
> > CREATE TABLE [TABLE1] (
> > [ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> > NULL )
> >
> > Why do I need COLLATE? What does COLLATION mean in table creation?
> >
> > Thanks a lot!
> > Mike
> >
> >

Sunday, March 25, 2012

collation error...

Collation difference between SQL 6.5 server and SQL 2000 server

Collation confusion (may be not)

Thursday, March 22, 2012

Collation Conflict

Collation Conflict

Collation Conflict

Tuesday, March 20, 2012

collation & compatibility issues

Collation

Collation

Monday, March 19, 2012

COLLATE Question

COLLATE Question

COLLATE Question

Collate

Collate

Collate

Clustered DB Server

Blog Archive

About Me