collation name

Is there any way, I can query across all objects in a given database for what is the current collation name for each column in an object?select [Object_Name]=object_name(id),
[Column Name] = name,
[Collation] = collation
from syscolumns
where objectproperty(id, 'ismsshipped') = 0
order by object_name(id), colid

Collation issue

Dear all,
I''ve got a database which own sql1_latin1_General_cp1_ci_as. That's fine.
I'd like to change the current collation for 'Traditional_Spanish_CI_AS'
using the following statements:
1-ALTER DATABASE bdtestman SET SINGLE_USER with rollback immediate
2-ALTER DATABASE bdtestman COLLATE Traditional_Spanish_CI_AS
3-ALTER DATABASE bdtestman Set MULTI_USER
2 failed and said me:
Server: Msg 5075, Level 16, State 1, Line 1
The column 'F211_Maastrich_OverallTestsResultsNoSha
ll.NOMPROYECTO' is
dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'F211_Maastrich_OverallTestsResultsNoSha
ll.NOMNIVEL' is dependent
on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'F211_Maastrich_OverallTestsResultsNoSha
ll.NOMELEMENTO' is
dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'F211_Maastrich_OverallTestsResultsNoSha
ll.NOMPRUEBA' is
dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'F211_Maastrich_OverallTestsResultsNoSha
ll.NOMCASO' is dependent
on database collation.
Does anyone ever used this statement in order to change the current collatio
n?
Thanks for any input and regards,If F211_Maastrich_OverallTestsResultsNoShal
l is a view, drop it before you
try changing the database collation, then recreate it after the change is
made.
ML
http://milambda.blogspot.com/|||according to books online
You are the only one currently using the database.
No schema bound object is dependent on the collation of the database.
If the following objects, which are dependent on the database
collation, exist in the database, the ALTER DATABASE database COLLATE
statement will fail. SQL Server will return an error message for each
object blocking the ALTER action:
User-defined functions and views created with SCHEMABINDING.
Computed columns.
CHECK constraints.
Table-valued functions that return tables with character columns with
collations inherited from the default database collation.
Look at alter database in books onlne.
Regardssqlsql

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
| > | > |
| > | > |
| > | >
| > | >
| > |
| >
| >
|

Coexisting SQL Reader & UPDATE

In my current application, I have an administration form that fills in labels and checked states via data entered into the database using a similar user input field. What the admin page does is it first lists all the record names in a listview, then on select, it fills in the form based on what the records contain. This means labels text change, and check states change based on the string "True" or "False". This was done using the SQL Reader command.

Within the same form, the read checkboxes are editable via the admin. When the admin edits the controls, he will click the update button at the bottom and the database will UPDATE .. WHERE UserName = (Scalar for ListBox1.SelectedValue)

I've used the exact same UPDATE command in my form for the user, except the only difference was @. the WHERE clause-- I had it updating based on a GUID. I know my SQL statement is correct, but it just won't update the data.

Is it possible that the READER, which starts (and closes) on pageload cannot coexist within the same form as the UPDATE code?

My code is incredibly long, so for the purposes of a short post I'm not including any bit of it-- but if you would like to see it, just let me know.

Why do you need to use a GUID? Why not use an identity integer primary key? Update will then be a simple stored procedure and a bit of wrapper code.

|||

I thought that too, but the GUID's working fine on the other pages-- thats not what I have a problem with. I'm modifying an existing structure, and if the GUID scheme works, I'd rather not make more work for myself.

On the form in question, GUID doesn't even exist as an instance on it. ListBox1.SelectedValue is what I'm trying to get as my WHERE arguement-- and it does pass, no errors generated, but the form does not update.

|||

When you run it in debug mode with your breakpoints set, where does it stop at? When you click update does it call to execute your sql statement?

|||

when I set breakpoints, I have it set at the NonQuery, and everything inputs correctly: my SQL statement is properly formed down to the ListBox selected value translating into an EXISTING column name (eg, Current user is Test, Test is databound to listbox. Test's data is displayed, modified, then "updated". Break. SQL statement reads 'UPDATE Login SET ... WHERE UserName = 'Test', properly formed and existing), the ExecuteNonQuery goes through, there are no errors generated. and the redirect coded after the update processes. Display test's data again, and no changes were made.

|||

Figured it out.

I made two functions, "ReadMe()" and "UpdateMe()", and called them on load and on button click. However, although all my statements were correct, ButtonClick calls a postback, and with the reader executing onpostback, the database automatically rolled back the variables.

Solution: I changed the ReadMe() function to execute on ListBox1.SelectedIndex changed, and the update was successful.

Whoops!

Clustering Question...

Say I have a current standalone SQL 2005 node that is SAN attached.
How can I get it clustered by adding a passive secondary node to it ? Can I
do so ? Or do I have to setup an entirely 2 new clustered set of servers and
move the database from the standalone server to it ?
I am trying to avoid buying 2 new servers and move the database across but
would like to just add another node to the existing standalone server and
get it clustered. If this is possible, how can I do it ?
Clusters have built from the ground up. You can't make it a cluster after
the install of SQL. Migrate to a new cluster, built from the ground up, that
is the best option.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23PKJjfJHHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Say I have a current standalone SQL 2005 node that is SAN attached.
> How can I get it clustered by adding a passive secondary node to it ? Can
> I do so ? Or do I have to setup an entirely 2 new clustered set of servers
> and move the database from the standalone server to it ?
> I am trying to avoid buying 2 new servers and move the database across but
> would like to just add another node to the existing standalone server and
> get it clustered. If this is possible, how can I do it ?
>
>
|||Just to expand on what Rodney said, it is possible to create a cluster from
existing equipment, but it is neither desirable nor supported. The
potential disruption during the install and migration process will likely
cause some unplanned downtime, which defeats the entire purpose of a
cluster. It is best to start from a clean slate and migrate the data into
the new system.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:ODTKVxJHHHA.4804@.TK2MSFTNGP03.phx.gbl...
> Clusters have built from the ground up. You can't make it a cluster after
> the install of SQL. Migrate to a new cluster, built from the ground up,
> that is the best option.
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://www.msmvps.com/clustering - Blog
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23PKJjfJHHHA.3872@.TK2MSFTNGP06.phx.gbl...
>
|||What you can do, however, if you are willing to live with a new network
name, is to build a new single-node cluster and install SQL Server on it.
After migrating the databases to this new host, you can then rebuild the
original server and then have it joined to the new cluster as a new cluster
node.
This last bit has very specific steps. Look them up in Books Online or
TechNet. I even think I have a detailed list lying around this new group
somewhere. If I find it, I'll shoot it out to you.
Anyway, this should save you the cost of buying a third server.
Sincerely,
Anthony Thomas

"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23WajdlSHHHA.960@.TK2MSFTNGP04.phx.gbl...
> Just to expand on what Rodney said, it is possible to create a cluster
from[vbcol=seagreen]
> existing equipment, but it is neither desirable nor supported. The
> potential disruption during the install and migration process will likely
> cause some unplanned downtime, which defeats the entire purpose of a
> cluster. It is best to start from a clean slate and migrate the data into
> the new system.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:ODTKVxJHHHA.4804@.TK2MSFTNGP03.phx.gbl...
after[vbcol=seagreen]
Can[vbcol=seagreen]
server
>

Clustering or log shipping MS SQL 2000?

Our current SAP production environment consists of a window 2000 advanced
server and MS SQL 2000 with SAP 4.7. We would like to setup a reporting
server which contains an exact copy of the production database which is
up-to-date all the time during the day.
I started looking into log shipping but it appears there will be some down
time when the transaction logs are shipped from one sql server to the second
sql server. If the second sql server takes 10 minutes to apply the
transaction logs then the database is unavailable for that time period. Are
there any other options? What are other Basis admins doing? Are they using
Microsoft Clustering services to replicate the database? Or using SAN
imaging technology to replicate the data to another server?
Please advise,
Clustering won't get you there...its a high-availability solution, not
distributed processing.
The requirement of near-time data pretty much says you are looking at
transactional replication, or a 3rd party utility.
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Chris T." <tatroc@.donotspam.gmail.com> wrote in message
news:%23X3FDSCMHHA.5104@.TK2MSFTNGP06.phx.gbl...
> Our current SAP production environment consists of a window 2000 advanced
> server and MS SQL 2000 with SAP 4.7. We would like to setup a reporting
> server which contains an exact copy of the production database which is
> up-to-date all the time during the day.
>
> I started looking into log shipping but it appears there will be some down
> time when the transaction logs are shipped from one sql server to the
> second sql server. If the second sql server takes 10 minutes to apply the
> transaction logs then the database is unavailable for that time period.
> Are there any other options? What are other Basis admins doing? Are they
> using Microsoft Clustering services to replicate the database? Or using
> SAN imaging technology to replicate the data to another server?
>
> Please advise,
>
|||Chris,
There are several SAN Solutions that accomplish exactly what you want. This
is probably the way to go, although expensive.
-- Bill
"Chris T." <tatroc@.donotspam.gmail.com> wrote in message
news:%23X3FDSCMHHA.5104@.TK2MSFTNGP06.phx.gbl...
> Our current SAP production environment consists of a window 2000 advanced
> server and MS SQL 2000 with SAP 4.7. We would like to setup a reporting
> server which contains an exact copy of the production database which is
> up-to-date all the time during the day.
>
> I started looking into log shipping but it appears there will be some down
> time when the transaction logs are shipped from one sql server to the
> second sql server. If the second sql server takes 10 minutes to apply the
> transaction logs then the database is unavailable for that time period.
> Are there any other options? What are other Basis admins doing? Are they
> using Microsoft Clustering services to replicate the database? Or using
> SAN imaging technology to replicate the data to another server?
>
> Please advise,
>
|||replication is ideal for this. Some SAN solutions allow you to do this sort
of a thing but they tend to be very expensive.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chris T." <tatroc@.donotspam.gmail.com> wrote in message
news:%23X3FDSCMHHA.5104@.TK2MSFTNGP06.phx.gbl...
> Our current SAP production environment consists of a window 2000 advanced
> server and MS SQL 2000 with SAP 4.7. We would like to setup a reporting
> server which contains an exact copy of the production database which is
> up-to-date all the time during the day.
>
> I started looking into log shipping but it appears there will be some down
> time when the transaction logs are shipped from one sql server to the
> second sql server. If the second sql server takes 10 minutes to apply the
> transaction logs then the database is unavailable for that time period.
> Are there any other options? What are other Basis admins doing? Are they
> using Microsoft Clustering services to replicate the database? Or using
> SAN imaging technology to replicate the data to another server?
>
> Please advise,
>
|||Would Transactional Replication that is built into SQL 2000 work for this?
Or is this log shipping?
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23U3jCBNMHHA.448@.TK2MSFTNGP04.phx.gbl...
> replication is ideal for this. Some SAN solutions allow you to do this
> sort of a thing but they tend to be very expensive.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Chris T." <tatroc@.donotspam.gmail.com> wrote in message
> news:%23X3FDSCMHHA.5104@.TK2MSFTNGP06.phx.gbl...
>
|||Transactional repl.
Log shipping is not going to give you the up to date you are looking for AND
the ability to use it as a reporting server. Either one, not both
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Chris T." <tatroc@.donotspam.gmail.com> wrote in message
news:OH%23EOy0MHHA.1252@.TK2MSFTNGP02.phx.gbl...
> Would Transactional Replication that is built into SQL 2000 work for this?
> Or is this log shipping?
>
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23U3jCBNMHHA.448@.TK2MSFTNGP04.phx.gbl...
>

cluster-compatible OS versions

I have an existing 2-node cluster on older hardware that is hosting 3
SQL2000 virtual server/named instances. The current OS version for both
nodes is Windows 2003 Ent SP1.
I intend on upgrading this to a 3-node cluster by adding two very new
host nodes followed by removal of one older node, giving me an
active/active/passive failover setup.
The question is: can I simply load Win2003 R2 Ent SP2 on the new host
nodes and join them to the cluster, or do I need to first upgrade the
existing nodes in some fashion?
> The question is: can I simply load Win2003 R2 Ent SP2 on the new host
> nodes and join them to the cluster, or do I need to first upgrade the
> existing nodes in some fashion?
I have not done this specific scenario, but I'd guess that it should work
because that's no different from an OS rolling upgrade. In any event, I'd
keep the OS versions on all nodes the same when all is said and done.
Linchi
"horseradish" wrote:

> I have an existing 2-node cluster on older hardware that is hosting 3
> SQL2000 virtual server/named instances. The current OS version for both
> nodes is Windows 2003 Ent SP1.
> I intend on upgrading this to a 3-node cluster by adding two very new
> host nodes followed by removal of one older node, giving me an
> active/active/passive failover setup.
> The question is: can I simply load Win2003 R2 Ent SP2 on the new host
> nodes and join them to the cluster, or do I need to first upgrade the
> existing nodes in some fashion?
>
|||I would upgrade to SP2 (if your vendors support it) on the existing node.
Make sure you have a valid, restorable backup first. Then add in the second
SP2 nodes.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"horseradish" <millardjk@.gmail.com> wrote in message
news:%23ilR6MojHHA.4904@.TK2MSFTNGP05.phx.gbl...
>I have an existing 2-node cluster on older hardware that is hosting 3
>SQL2000 virtual server/named instances. The current OS version for both
>nodes is Windows 2003 Ent SP1.
> I intend on upgrading this to a 3-node cluster by adding two very new host
> nodes followed by removal of one older node, giving me an
> active/active/passive failover setup.
> The question is: can I simply load Win2003 R2 Ent SP2 on the new host
> nodes and join them to the cluster, or do I need to first upgrade the
> existing nodes in some fashion?