binary sort versus DOCI

Hello:
Whether you are using SQL 2000 or SQL 2005, what is the best script to run
in Query Analyzer to determine the sort order that SQL was originally
installed under (binary versus dictionary order case insensitivity)?
Thanks!
childofthe1980sOn 27 Oct, 17:41, childofthe1980s
<childofthe19...@.discussions.microsoft.com> wrote:
> Hello:
> Whether you are using SQL 2000 or SQL 2005, what is the best script to run
> in Query Analyzer to determine the sort order that SQL was originally
> installed under (binary versus dictionary order case insensitivity)?
> Thanks!
> childofthe1980s
SERVERPROPERTY('Collation')
returns the name of the server's default collation.
--
David Portas|||Thank you, David!
childofthe1980s
"David Portas" wrote:
> On 27 Oct, 17:41, childofthe1980s
> <childofthe19...@.discussions.microsoft.com> wrote:
> > Hello:
> >
> > Whether you are using SQL 2000 or SQL 2005, what is the best script to run
> > in Query Analyzer to determine the sort order that SQL was originally
> > installed under (binary versus dictionary order case insensitivity)?
> >
> > Thanks!
> >
> > childofthe1980s
>
> SERVERPROPERTY('Collation')
> returns the name of the server's default collation.
> --
> David Portas
>sql

Binary sort order and clustering

I am trying to create a four node cluster. One of my instances has a Binary
sort order. The SQL Server cluster install and SP4 worked. Now I am tryin
g
to add additional drives for SQL Server to use. I am able to do this for my
case insensitive instances, and those SQL Server instances can see the
drives.
On the Binary instance, once I add the drive and try to bring the instance
back online, SQL Server will not come back up. In cluster manager, the
status just says "Online Pending".
If I go to the services applet and try to start it, I get a message the the
service started and stopped. The instance will failover to another node.
The error log actually looks like SQL Server is still running, and I can see
sqlservr.exe in Task Manager. I can also connect to it using Query Analyzer
.
When I run this: "select * from ::fn_servershareddrives()", the correct
drive letters show up in the results. From Enterprise Manager, It looks lik
e
the instance is stopped. I can also navigate to the drive.
Has anyone successfully clustered a case sensitive instance? What could I
be doing wrong?Never mind. It was something besides adding the drive and nothing to do with
the collation.
"Kathi Kellenberger" wrote:

> I am trying to create a four node cluster. One of my instances has a Bina
ry
> sort order. The SQL Server cluster install and SP4 worked. Now I am try
ing
> to add additional drives for SQL Server to use. I am able to do this for
my
> case insensitive instances, and those SQL Server instances can see the
> drives.
> On the Binary instance, once I add the drive and try to bring the instance
> back online, SQL Server will not come back up. In cluster manager, the
> status just says "Online Pending".
> If I go to the services applet and try to start it, I get a message the th
e
> service started and stopped. The instance will failover to another node.
> The error log actually looks like SQL Server is still running, and I can s
ee
> sqlservr.exe in Task Manager. I can also connect to it using Query Analyz
er.
> When I run this: "select * from ::fn_servershareddrives()", the correct
> drive letters show up in the results. From Enterprise Manager, It looks l
ike
> the instance is stopped. I can also navigate to the drive.
> Has anyone successfully clustered a case sensitive instance? What could I
> be doing wrong?
>

Binary sort order and clustering

I am trying to create a four node cluster. One of my instances has a Binary
sort order. The SQL Server cluster install and SP4 worked. Now I am trying
to add additional drives for SQL Server to use. I am able to do this for my
case insensitive instances, and those SQL Server instances can see the
drives.
On the Binary instance, once I add the drive and try to bring the instance
back online, SQL Server will not come back up. In cluster manager, the
status just says "Online Pending".
If I go to the services applet and try to start it, I get a message the the
service started and stopped. The instance will failover to another node.
The error log actually looks like SQL Server is still running, and I can see
sqlservr.exe in Task Manager. I can also connect to it using Query Analyzer.
When I run this: "select * from ::fn_servershareddrives()", the correct
drive letters show up in the results. From Enterprise Manager, It looks like
the instance is stopped. I can also navigate to the drive.
Has anyone successfully clustered a case sensitive instance? What could I
be doing wrong?Never mind. It was something besides adding the drive and nothing to do with
the collation.
"Kathi Kellenberger" wrote:
> I am trying to create a four node cluster. One of my instances has a Binary
> sort order. The SQL Server cluster install and SP4 worked. Now I am trying
> to add additional drives for SQL Server to use. I am able to do this for my
> case insensitive instances, and those SQL Server instances can see the
> drives.
> On the Binary instance, once I add the drive and try to bring the instance
> back online, SQL Server will not come back up. In cluster manager, the
> status just says "Online Pending".
> If I go to the services applet and try to start it, I get a message the the
> service started and stopped. The instance will failover to another node.
> The error log actually looks like SQL Server is still running, and I can see
> sqlservr.exe in Task Manager. I can also connect to it using Query Analyzer.
> When I run this: "select * from ::fn_servershareddrives()", the correct
> drive letters show up in the results. From Enterprise Manager, It looks like
> the instance is stopped. I can also navigate to the drive.
> Has anyone successfully clustered a case sensitive instance? What could I
> be doing wrong?
>

Binary sort order

Binary sort order on SQL 7 is NOT the same as binary sort order in SQL 2000. SQL 2000 shows the databases brought over from the SQL 7 server generates a collation compatability error. I am seeing errors when running reports in Great Plains Dynamics.

Is there a work around to this?

Also why would Microsoft recommend DOCI over Binary sort order for their e-product offerings.The CP437_Binary sort order is exactly the same in SQL 2000 as it is in SQL 7.0.

The reason Microsoft recommends DOCI over binary sort order is because binary is not compatible with anything and makes everything difficult. We use it where I work because the old DBA believed it was still more efficient. What a pain!!!

You need to check your database to make sure it is using CP437. You also need to do a quick scan of syscolumns to make sure each column in there is CP437. If anyone creates a table or alter statement on their box and puts it on your binary box, it's very likely they scripted out the default collation which is not binary.

What are the exact errors you are receiving from the reports? I should be able to help you out quite a bit more if you can tell me the error. Unfortunately I have way more experience with binary on SQL 2000 then I ever wanted to have. I thought we were done with it after 6.5.|||Originally posted by derrickleggett
The CP437_Binary sort order is exactly the same in SQL 2000 as it is in SQL 7.0.

The reason Microsoft recommends DOCI over binary sort order is because binary is not compatible with anything and makes everything difficult. We use it where I work because the old DBA believed it was still more efficient. What a pain!!!

You need to check your database to make sure it is using CP437. You also need to do a quick scan of syscolumns to make sure each column in there is CP437. If anyone creates a table or alter statement on their box and puts it on your binary box, it's very likely they scripted out the default collation which is not binary.

What are the exact errors you are receiving from the reports? I should be able to help you out quite a bit more if you can tell me the error. Unfortunately I have way more experience with binary on SQL 2000 then I ever wanted to have. I thought we were done with it after 6.5.

Binary collation

Is there a code page independent binary collation? I want a collation that
will sort binary order that will not force translations from other collations.
My problem is this: I want the user to be able to use any collation they
want. My process is going to work with their data in binary order. Even if
I could retrieve the collation they used, I don't know how to, in a program,
to convert that collation into a binary collation. For some collations, its
obvious. If their collation was SQL_CZECH_CP1350_CI_AS I could probably
construct Czech_BIN from that.
Hello Michael,
Did you try to cast the value to binary? Here's a sample i've just written:
create table mytable (Col1 nvarchar(20));
insert into mytable values (N'Die Nu, die Nsse');
select CAST ( Col1 AS binary (40)) from mytable
The query will return the following value as binary. Note that there is no
collation on binary.
0x44006900650020004E007500DF002C002000640069006500 20004E00FC0073007300650000
000000
"Michael Bauers" <MichaelBauers@.discussions.microsoft.com> wrote in message
news:54F4F3E1-0FD9-4209-9301-1182139A7DC1@.microsoft.com...
> Is there a code page independent binary collation? I want a collation
that
> will sort binary order that will not force translations from other
collations.
> My problem is this: I want the user to be able to use any collation they
> want. My process is going to work with their data in binary order. Even
if
> I could retrieve the collation they used, I don't know how to, in a
program,
> to convert that collation into a binary collation. For some collations,
its
> obvious. If their collation was SQL_CZECH_CP1350_CI_AS I could probably
> construct Czech_BIN from that.
|||What if the values come from another table (not constant values.)
"Michael Thomas [Microsoft]" wrote:

> Hello Michael,
> Did you try to cast the value to binary? Here's a sample i've just written:
> create table mytable (Col1 nvarchar(20));
> insert into mytable values (N'Die Nu?, die Nüsse');
> select CAST ( Col1 AS binary (40)) from mytable
>
>
> The query will return the following value as binary. Note that there is no
> collation on binary.
>
> 0x44006900650020004E007500DF002C002000640069006500 20004E00FC0073007300650000
> 000000
>
> "Michael Bauers" <MichaelBauers@.discussions.microsoft.com> wrote in message
> news:54F4F3E1-0FD9-4209-9301-1182139A7DC1@.microsoft.com...
> that
> collations.
> if
> program,
> its
>
>
|||What about storing the character data as Unicode (nvarchar, nchar, and
following http://support.microsoft.com/?id=239530)? Once it's Unicode it
won't undergo any code page converstions.
If you're set on using non-Unicode data types and don't want the
possibility of code page conversions when the text data flows between
environments with different code pages, the proper thing to do is to store
the data in varbinary or image columns. char/varchar is considered to be
string data, and SQL will always attempt to retain the "meaning" of the
characters as they move between different code page environments. If you
want to remove this functionality, then you're essentially asking SQL to
treat the data as a raw binary byte stream anyway. Don't attempt to store
code page X character data in a code page Y varchar column.
Can you clarify your scenario a bit? Is your app intended to work with
arbitrary database schemas (like a generic data transfer tool), or is the
database schema yours? What's the primary problem you're trying to avoid?
Pulling code page X data out of the data into a non-Unicode variable in a
code page Y application, and getting data loss as a result?
Would it help to be able to determine the code page associated with each
collation so you could pick an appropriate binary collation?
SELECT name, COLLATIONPROPERTY (name, 'CodePage')
FROM ::fn_helpcollations ()
WHERE name LIKE '%_BIN%'
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: Binary collation
| thread-index: AcTMGjaHGpqnfmm4Qp2RM3AZmAHZXg==
| X-WBNR-Posting-Host: 12.24.200.251
| From: "=?Utf-8?B?TWljaGFlbCBCYXVlcnM=?="
<MichaelBauers@.discussions.microsoft.com>
| References: <54F4F3E1-0FD9-4209-9301-1182139A7DC1@.microsoft.com>
<udSTNlBzEHA.3656@.TK2MSFTNGP09.phx.gbl>
| Subject: Re: Binary collation
| Date: Tue, 16 Nov 2004 12:24:02 -0800
| Lines: 47
| Message-ID: <EA0BBCAD-D82F-4393-8FC0-12DFABAC5DCB@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:367631
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| What if the values come from another table (not constant values.)
|
|
| "Michael Thomas [Microsoft]" wrote:
|
| > Hello Michael,
| >
| > Did you try to cast the value to binary? Here's a sample i've just
written:
| >
| > create table mytable (Col1 nvarchar(20));
| >
| > insert into mytable values (N'Die Nu?, die Nüsse');
| >
| > select CAST ( Col1 AS binary (40)) from mytable
| >
| >
| >
| >
| >
| > The query will return the following value as binary. Note that there is
no
| > collation on binary.
| >
| >
| >
| >
0x44006900650020004E007500DF002C002000640069006500 20004E00FC0073007300650000
| > 000000
| >
| >
| > "Michael Bauers" <MichaelBauers@.discussions.microsoft.com> wrote in
message
| > news:54F4F3E1-0FD9-4209-9301-1182139A7DC1@.microsoft.com...
| > > Is there a code page independent binary collation? I want a collation
| > that
| > > will sort binary order that will not force translations from other
| > collations.
| > >
| > > My problem is this: I want the user to be able to use any collation
they
| > > want. My process is going to work with their data in binary order.
Even
| > if
| > > I could retrieve the collation they used, I don't know how to, in a
| > program,
| > > to convert that collation into a binary collation. For some
collations,
| > its
| > > obvious. If their collation was SQL_CZECH_CP1350_CI_AS I could
probably
| > > construct Czech_BIN from that.
| >
| >
| >
|
|||Michael,
Just saw your other post on this topic where you provided a bit more detail
about what you are doing:
> I should clarify the situation. We are inserting rows into a table with
> LATIN1_BIN collation from a table with CZECH_BIN collation.
From this I gather that the problem you are facing is data loss as the
strings undergo a code page conversion.
My first question is: why not use the same collation for the destination
table?
My second question would be: Can you use Unicode types for the destination
table? A Unicode column can store data from any other Unicode or
non-Unicode column regardless of collation, without data loss.
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.
| Newsgroups: microsoft.public.sqlserver.server
| From: bartd@.online.microsoft.com (Bart Duncan [MSFT])
| Organization: Microsoft
| Date: Wed, 17 Nov 2004 18:45:21 GMT
| Subject: Re: Binary collation
| X-Tomcat-NG: microsoft.public.sqlserver.server
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
|
| What about storing the character data as Unicode (nvarchar, nchar, and
| following http://support.microsoft.com/?id=239530)? Once it's Unicode it
| won't undergo any code page converstions.
|
| If you're set on using non-Unicode data types and don't want the
| possibility of code page conversions when the text data flows between
| environments with different code pages, the proper thing to do is to
store
| the data in varbinary or image columns. char/varchar is considered to be
| string data, and SQL will always attempt to retain the "meaning" of the
| characters as they move between different code page environments. If you
| want to remove this functionality, then you're essentially asking SQL to
| treat the data as a raw binary byte stream anyway. Don't attempt to
store
| code page X character data in a code page Y varchar column.
|
| Can you clarify your scenario a bit? Is your app intended to work with
| arbitrary database schemas (like a generic data transfer tool), or is the
| database schema yours? What's the primary problem you're trying to
avoid?
| Pulling code page X data out of the data into a non-Unicode variable in a
| code page Y application, and getting data loss as a result?
|
| Would it help to be able to determine the code page associated with each
| collation so you could pick an appropriate binary collation?
| SELECT name, COLLATIONPROPERTY (name, 'CodePage')
| FROM ::fn_helpcollations ()
| WHERE name LIKE '%_BIN%'
|
| 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: Binary collation
| | thread-index: AcTMGjaHGpqnfmm4Qp2RM3AZmAHZXg==
| | X-WBNR-Posting-Host: 12.24.200.251
| | From: "=?Utf-8?B?TWljaGFlbCBCYXVlcnM=?="
| <MichaelBauers@.discussions.microsoft.com>
| | References: <54F4F3E1-0FD9-4209-9301-1182139A7DC1@.microsoft.com>
| <udSTNlBzEHA.3656@.TK2MSFTNGP09.phx.gbl>
| | Subject: Re: Binary collation
| | Date: Tue, 16 Nov 2004 12:24:02 -0800
| | Lines: 47
| | Message-ID: <EA0BBCAD-D82F-4393-8FC0-12DFABAC5DCB@.microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: text/plain;
| | charset="Utf-8"
| | Content-Transfer-Encoding: 8bit
| | X-Newsreader: Microsoft CDO for Windows 2000
| | Content-Class: urn:content-classes:message
| | Importance: normal
| | Priority: normal
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| | Newsgroups: microsoft.public.sqlserver.server
| | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:367631
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| | What if the values come from another table (not constant values.)
| |
| |
| | "Michael Thomas [Microsoft]" wrote:
| |
| | > Hello Michael,
| | >
| | > Did you try to cast the value to binary? Here's a sample i've just
| written:
| | >
| | > create table mytable (Col1 nvarchar(20));
| | >
| | > insert into mytable values (N'Die Nu?, die Nüsse');
| | >
| | > select CAST ( Col1 AS binary (40)) from mytable
| | >
| | >
| | >
| | >
| | >
| | > The query will return the following value as binary. Note that there
is
| no
| | > collation on binary.
| | >
| | >
| | >
| | >
|
0x44006900650020004E007500DF002C002000640069006500 20004E00FC0073007300650000
| | > 000000
| | >
| | >
| | > "Michael Bauers" <MichaelBauers@.discussions.microsoft.com> wrote in
| message
| | > news:54F4F3E1-0FD9-4209-9301-1182139A7DC1@.microsoft.com...
| | > > Is there a code page independent binary collation? I want a
collation
| | > that
| | > > will sort binary order that will not force translations from other
| | > collations.
| | > >
| | > > My problem is this: I want the user to be able to use any collation
| they
| | > > want. My process is going to work with their data in binary order.
| Even
| | > if
| | > > I could retrieve the collation they used, I don't know how to, in a
| | > program,
| | > > to convert that collation into a binary collation. For some
| collations,
| | > its
| | > > obvious. If their collation was SQL_CZECH_CP1350_CI_AS I could
| probably
| | > > construct Czech_BIN from that.
| | >
| | >
| | >
| |
|

Binary collation

Is there a code page independent binary collation? I want a collation that
will sort binary order that will not force translations from other collation
s.
My problem is this: I want the user to be able to use any collation they
want. My process is going to work with their data in binary order. Even if
I could retrieve the collation they used, I don't know how to, in a program,
to convert that collation into a binary collation. For some collations, its
obvious. If their collation was SQL_CZECH_CP1350_CI_AS I could probably
construct Czech_BIN from that.Hello Michael,
Did you try to cast the value to binary? Here's a sample i've just written:
create table mytable (Col1 nvarchar(20));
insert into mytable values (N'Die Nu, die Nsse');
select CAST ( Col1 AS binary (40)) from mytable
The query will return the following value as binary. Note that there is no
collation on binary.
0x44006900650020004E007500DF002C00200064
006900650020004E00FC0073007300650000
000000
"Michael Bauers" <MichaelBauers@.discussions.microsoft.com> wrote in message
news:54F4F3E1-0FD9-4209-9301-1182139A7DC1@.microsoft.com...
> Is there a code page independent binary collation? I want a collation
that
> will sort binary order that will not force translations from other
collations.
> My problem is this: I want the user to be able to use any collation they
> want. My process is going to work with their data in binary order. Even
if
> I could retrieve the collation they used, I don't know how to, in a
program,
> to convert that collation into a binary collation. For some collations,
its
> obvious. If their collation was SQL_CZECH_CP1350_CI_AS I could probably
> construct Czech_BIN from that.|||What if the values come from another table (not constant values.)
"Michael Thomas [Microsoft]" wrote:

> Hello Michael,
> Did you try to cast the value to binary? Here's a sample i've just written
:
> create table mytable (Col1 nvarchar(20));
> insert into mytable values (N'Die Nu?, die Nüsse');
> select CAST ( Col1 AS binary (40)) from mytable
>
>
> The query will return the following value as binary. Note that there is no
> collation on binary.
>
> 0x44006900650020004E007500DF002C00200064
006900650020004E00FC00730073006500
00
> 000000
>
> "Michael Bauers" <MichaelBauers@.discussions.microsoft.com> wrote in messag
e
> news:54F4F3E1-0FD9-4209-9301-1182139A7DC1@.microsoft.com...
> that
> collations.
> if
> program,
> its
>
>|||What about storing the character data as Unicode (nvarchar, nchar, and
following http://support.microsoft.com/?id=239530)? Once it's Unicode it
won't undergo any code page converstions.
If you're set on using non-Unicode data types and don't want the
possibility of code page conversions when the text data flows between
environments with different code pages, the proper thing to do is to store
the data in varbinary or image columns. char/varchar is considered to be
string data, and SQL will always attempt to retain the "meaning" of the
characters as they move between different code page environments. If you
want to remove this functionality, then you're essentially asking SQL to
treat the data as a raw binary byte stream anyway. Don't attempt to store
code page X character data in a code page Y varchar column.
Can you clarify your scenario a bit? Is your app intended to work with
arbitrary database schemas (like a generic data transfer tool), or is the
database schema yours? What's the primary problem you're trying to avoid?
Pulling code page X data out of the data into a non-Unicode variable in a
code page Y application, and getting data loss as a result?
Would it help to be able to determine the code page associated with each
collation so you could pick an appropriate binary collation?
SELECT name, COLLATIONPROPERTY (name, 'CodePage')
FROM ::fn_helpcollations ()
WHERE name LIKE '%_BIN%'
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: Binary collation
| thread-index: AcTMGjaHGpqnfmm4Qp2RM3AZmAHZXg==
| X-WBNR-Posting-Host: 12.24.200.251
| From: "examnotes"
<MichaelBauers@.discussions.microsoft.com>
| References: <54F4F3E1-0FD9-4209-9301-1182139A7DC1@.microsoft.com>
<udSTNlBzEHA.3656@.TK2MSFTNGP09.phx.gbl>
| Subject: Re: Binary collation
| Date: Tue, 16 Nov 2004 12:24:02 -0800
| Lines: 47
| Message-ID: <EA0BBCAD-D82F-4393-8FC0-12DFABAC5DCB@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:367631
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| What if the values come from another table (not constant values.)
|
|
| "Michael Thomas [Microsoft]" wrote:
|
| > Hello Michael,
| >
| > Did you try to cast the value to binary? Here's a sample i've just
written:
| >
| > create table mytable (Col1 nvarchar(20));
| >
| > insert into mytable values (N'Die Nu?, die Nüsse');
| >
| > select CAST ( Col1 AS binary (40)) from mytable
| >
| >
| >
| >
| >
| > The query will return the following value as binary. Note that there is
no
| > collation on binary.
| >
| >
| >
| >
0x44006900650020004E007500DF002C00200064
006900650020004E00FC0073007300650000
| > 000000
| >
| >
| > "Michael Bauers" <MichaelBauers@.discussions.microsoft.com> wrote in
message
| > news:54F4F3E1-0FD9-4209-9301-1182139A7DC1@.microsoft.com...
| > > Is there a code page independent binary collation? I want a collation
| > that
| > > will sort binary order that will not force translations from other
| > collations.
| > >
| > > My problem is this: I want the user to be able to use any collation
they
| > > want. My process is going to work with their data in binary order.
Even
| > if
| > > I could retrieve the collation they used, I don't know how to, in a
| > program,
| > > to convert that collation into a binary collation. For some
collations,
| > its
| > > obvious. If their collation was SQL_CZECH_CP1350_CI_AS I could
probably
| > > construct Czech_BIN from that.
| >
| >
| >
||||Michael,
Just saw your other post on this topic where you provided a bit more detail
about what you are doing:
> I should clarify the situation. We are inserting rows into a table with
> LATIN1_BIN collation from a table with CZECH_BIN collation.
From this I gather that the problem you are facing is data loss as the
strings undergo a code page conversion.
My first question is: why not use the same collation for the destination
table?
My second question would be: Can you use Unicode types for the destination
table? A Unicode column can store data from any other Unicode or
non-Unicode column regardless of collation, without data loss.
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.
| Newsgroups: microsoft.public.sqlserver.server
| From: bartd@.online.microsoft.com (Bart Duncan [MSFT])
| Organization: Microsoft
| Date: Wed, 17 Nov 2004 18:45:21 GMT
| Subject: Re: Binary collation
| X-Tomcat-NG: microsoft.public.sqlserver.server
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
|
| What about storing the character data as Unicode (nvarchar, nchar, and
| following http://support.microsoft.com/?id=239530)? Once it's Unicode it
| won't undergo any code page converstions.
|
| If you're set on using non-Unicode data types and don't want the
| possibility of code page conversions when the text data flows between
| environments with different code pages, the proper thing to do is to
store
| the data in varbinary or image columns. char/varchar is considered to be
| string data, and SQL will always attempt to retain the "meaning" of the
| characters as they move between different code page environments. If you
| want to remove this functionality, then you're essentially asking SQL to
| treat the data as a raw binary byte stream anyway. Don't attempt to
store
| code page X character data in a code page Y varchar column.
|
| Can you clarify your scenario a bit? Is your app intended to work with
| arbitrary database schemas (like a generic data transfer tool), or is the
| database schema yours? What's the primary problem you're trying to
avoid?
| Pulling code page X data out of the data into a non-Unicode variable in a
| code page Y application, and getting data loss as a result?
|
| Would it help to be able to determine the code page associated with each
| collation so you could pick an appropriate binary collation?
| SELECT name, COLLATIONPROPERTY (name, 'CodePage')
| FROM ::fn_helpcollations ()
| WHERE name LIKE '%_BIN%'
|
| 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: Binary collation
| | thread-index: AcTMGjaHGpqnfmm4Qp2RM3AZmAHZXg==
| | X-WBNR-Posting-Host: 12.24.200.251
| | From: "examnotes"
| <MichaelBauers@.discussions.microsoft.com>
| | References: <54F4F3E1-0FD9-4209-9301-1182139A7DC1@.microsoft.com>
| <udSTNlBzEHA.3656@.TK2MSFTNGP09.phx.gbl>
| | Subject: Re: Binary collation
| | Date: Tue, 16 Nov 2004 12:24:02 -0800
| | Lines: 47
| | Message-ID: <EA0BBCAD-D82F-4393-8FC0-12DFABAC5DCB@.microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: text/plain;
| | charset="Utf-8"
| | Content-Transfer-Encoding: 8bit
| | X-Newsreader: Microsoft CDO for Windows 2000
| | Content-Class: urn:content-classes:message
| | Importance: normal
| | Priority: normal
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| | Newsgroups: microsoft.public.sqlserver.server
| | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:367631
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| | What if the values come from another table (not constant values.)
| |
| |
| | "Michael Thomas [Microsoft]" wrote:
| |
| | > Hello Michael,
| | >
| | > Did you try to cast the value to binary? Here's a sample i've just
| written:
| | >
| | > create table mytable (Col1 nvarchar(20));
| | >
| | > insert into mytable values (N'Die Nu?, die Nüsse');
| | >
| | > select CAST ( Col1 AS binary (40)) from mytable
| | >
| | >
| | >
| | >
| | >
| | > The query will return the following value as binary. Note that there
is
| no
| | > collation on binary.
| | >
| | >
| | >
| | >
|
0x44006900650020004E007500DF002C00200064
006900650020004E00FC0073007300650000
| | > 000000
| | >
| | >
| | > "Michael Bauers" <MichaelBauers@.discussions.microsoft.com> wrote in
| message
| | > news:54F4F3E1-0FD9-4209-9301-1182139A7DC1@.microsoft.com...
| | > > Is there a code page independent binary collation? I want a
collation
| | > that
| | > > will sort binary order that will not force translations from other
| | > collations.
| | > >
| | > > My problem is this: I want the user to be able to use any collation
| they
| | > > want. My process is going to work with their data in binary order.
| Even
| | > if
| | > > I could retrieve the collation they used, I don't know how to, in a
| | > program,
| | > > to convert that collation into a binary collation. For some
| collations,
| | > its
| | > > obvious. If their collation was SQL_CZECH_CP1350_CI_AS I could
| probably
| | > > construct Czech_BIN from that.
| | >
| | >
| | >
| |
|

Binary collation

Is there a code page independent binary collation? I want a collation that
will sort binary order that will not force translations from other collations.
My problem is this: I want the user to be able to use any collation they
want. My process is going to work with their data in binary order. Even if
I could retrieve the collation they used, I don't know how to, in a program,
to convert that collation into a binary collation. For some collations, its
obvious. If their collation was SQL_CZECH_CP1350_CI_AS I could probably
construct Czech_BIN from that.Hello Michael,
Did you try to cast the value to binary? Here's a sample i've just written:
create table mytable (Col1 nvarchar(20));
insert into mytable values (N'Die Nuß, die Nüsse');
select CAST ( Col1 AS binary (40)) from mytable
The query will return the following value as binary. Note that there is no
collation on binary.
0x44006900650020004E007500DF002C00200064006900650020004E00FC0073007300650000
000000
"Michael Bauers" <MichaelBauers@.discussions.microsoft.com> wrote in message
news:54F4F3E1-0FD9-4209-9301-1182139A7DC1@.microsoft.com...
> Is there a code page independent binary collation? I want a collation
that
> will sort binary order that will not force translations from other
collations.
> My problem is this: I want the user to be able to use any collation they
> want. My process is going to work with their data in binary order. Even
if
> I could retrieve the collation they used, I don't know how to, in a
program,
> to convert that collation into a binary collation. For some collations,
its
> obvious. If their collation was SQL_CZECH_CP1350_CI_AS I could probably
> construct Czech_BIN from that.|||What if the values come from another table (not constant values.)
"Michael Thomas [Microsoft]" wrote:
> Hello Michael,
> Did you try to cast the value to binary? Here's a sample i've just written:
> create table mytable (Col1 nvarchar(20));
> insert into mytable values (N'Die Nu�, die Nüsse');
> select CAST ( Col1 AS binary (40)) from mytable
>
>
> The query will return the following value as binary. Note that there is no
> collation on binary.
>
> 0x44006900650020004E007500DF002C00200064006900650020004E00FC0073007300650000
> 000000
>
> "Michael Bauers" <MichaelBauers@.discussions.microsoft.com> wrote in message
> news:54F4F3E1-0FD9-4209-9301-1182139A7DC1@.microsoft.com...
> > Is there a code page independent binary collation? I want a collation
> that
> > will sort binary order that will not force translations from other
> collations.
> >
> > My problem is this: I want the user to be able to use any collation they
> > want. My process is going to work with their data in binary order. Even
> if
> > I could retrieve the collation they used, I don't know how to, in a
> program,
> > to convert that collation into a binary collation. For some collations,
> its
> > obvious. If their collation was SQL_CZECH_CP1350_CI_AS I could probably
> > construct Czech_BIN from that.
>
>|||What about storing the character data as Unicode (nvarchar, nchar, and
following http://support.microsoft.com/?id=239530)? Once it's Unicode it
won't undergo any code page converstions.
If you're set on using non-Unicode data types and don't want the
possibility of code page conversions when the text data flows between
environments with different code pages, the proper thing to do is to store
the data in varbinary or image columns. char/varchar is considered to be
string data, and SQL will always attempt to retain the "meaning" of the
characters as they move between different code page environments. If you
want to remove this functionality, then you're essentially asking SQL to
treat the data as a raw binary byte stream anyway. Don't attempt to store
code page X character data in a code page Y varchar column.
Can you clarify your scenario a bit? Is your app intended to work with
arbitrary database schemas (like a generic data transfer tool), or is the
database schema yours? What's the primary problem you're trying to avoid?
Pulling code page X data out of the data into a non-Unicode variable in a
code page Y application, and getting data loss as a result?
Would it help to be able to determine the code page associated with each
collation so you could pick an appropriate binary collation?
SELECT name, COLLATIONPROPERTY (name, 'CodePage')
FROM ::fn_helpcollations ()
WHERE name LIKE '%_BIN%'
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: Binary collation
| thread-index: AcTMGjaHGpqnfmm4Qp2RM3AZmAHZXg==| X-WBNR-Posting-Host: 12.24.200.251
| From: "=?Utf-8?B?TWljaGFlbCBCYXVlcnM=?="
<MichaelBauers@.discussions.microsoft.com>
| References: <54F4F3E1-0FD9-4209-9301-1182139A7DC1@.microsoft.com>
<udSTNlBzEHA.3656@.TK2MSFTNGP09.phx.gbl>
| Subject: Re: Binary collation
| Date: Tue, 16 Nov 2004 12:24:02 -0800
| Lines: 47
| Message-ID: <EA0BBCAD-D82F-4393-8FC0-12DFABAC5DCB@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:367631
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| What if the values come from another table (not constant values.)
|
|
| "Michael Thomas [Microsoft]" wrote:
|
| > Hello Michael,
| >
| > Did you try to cast the value to binary? Here's a sample i've just
written:
| >
| > create table mytable (Col1 nvarchar(20));
| >
| > insert into mytable values (N'Die Nu�, die Nüsse');
| >
| > select CAST ( Col1 AS binary (40)) from mytable
| >
| >
| >
| >
| >
| > The query will return the following value as binary. Note that there is
no
| > collation on binary.
| >
| >
| >
| >
0x44006900650020004E007500DF002C00200064006900650020004E00FC0073007300650000
| > 000000
| >
| >
| > "Michael Bauers" <MichaelBauers@.discussions.microsoft.com> wrote in
message
| > news:54F4F3E1-0FD9-4209-9301-1182139A7DC1@.microsoft.com...
| > > Is there a code page independent binary collation? I want a collation
| > that
| > > will sort binary order that will not force translations from other
| > collations.
| > >
| > > My problem is this: I want the user to be able to use any collation
they
| > > want. My process is going to work with their data in binary order.
Even
| > if
| > > I could retrieve the collation they used, I don't know how to, in a
| > program,
| > > to convert that collation into a binary collation. For some
collations,
| > its
| > > obvious. If their collation was SQL_CZECH_CP1350_CI_AS I could
probably
| > > construct Czech_BIN from that.
| >
| >
| >
||||Michael,
Just saw your other post on this topic where you provided a bit more detail
about what you are doing:
> I should clarify the situation. We are inserting rows into a table with
> LATIN1_BIN collation from a table with CZECH_BIN collation.
From this I gather that the problem you are facing is data loss as the
strings undergo a code page conversion.
My first question is: why not use the same collation for the destination
table?
My second question would be: Can you use Unicode types for the destination
table? A Unicode column can store data from any other Unicode or
non-Unicode column regardless of collation, without data loss.
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.
| Newsgroups: microsoft.public.sqlserver.server
| From: bartd@.online.microsoft.com (Bart Duncan [MSFT])
| Organization: Microsoft
| Date: Wed, 17 Nov 2004 18:45:21 GMT
| Subject: Re: Binary collation
| X-Tomcat-NG: microsoft.public.sqlserver.server
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
|
| What about storing the character data as Unicode (nvarchar, nchar, and
| following http://support.microsoft.com/?id=239530)? Once it's Unicode it
| won't undergo any code page converstions.
|
| If you're set on using non-Unicode data types and don't want the
| possibility of code page conversions when the text data flows between
| environments with different code pages, the proper thing to do is to
store
| the data in varbinary or image columns. char/varchar is considered to be
| string data, and SQL will always attempt to retain the "meaning" of the
| characters as they move between different code page environments. If you
| want to remove this functionality, then you're essentially asking SQL to
| treat the data as a raw binary byte stream anyway. Don't attempt to
store
| code page X character data in a code page Y varchar column.
|
| Can you clarify your scenario a bit? Is your app intended to work with
| arbitrary database schemas (like a generic data transfer tool), or is the
| database schema yours? What's the primary problem you're trying to
avoid?
| Pulling code page X data out of the data into a non-Unicode variable in a
| code page Y application, and getting data loss as a result?
|
| Would it help to be able to determine the code page associated with each
| collation so you could pick an appropriate binary collation?
| SELECT name, COLLATIONPROPERTY (name, 'CodePage')
| FROM ::fn_helpcollations ()
| WHERE name LIKE '%_BIN%'
|
| 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: Binary collation
| | thread-index: AcTMGjaHGpqnfmm4Qp2RM3AZmAHZXg==| | X-WBNR-Posting-Host: 12.24.200.251
| | From: "=?Utf-8?B?TWljaGFlbCBCYXVlcnM=?="
| <MichaelBauers@.discussions.microsoft.com>
| | References: <54F4F3E1-0FD9-4209-9301-1182139A7DC1@.microsoft.com>
| <udSTNlBzEHA.3656@.TK2MSFTNGP09.phx.gbl>
| | Subject: Re: Binary collation
| | Date: Tue, 16 Nov 2004 12:24:02 -0800
| | Lines: 47
| | Message-ID: <EA0BBCAD-D82F-4393-8FC0-12DFABAC5DCB@.microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: text/plain;
| | charset="Utf-8"
| | Content-Transfer-Encoding: 8bit
| | X-Newsreader: Microsoft CDO for Windows 2000
| | Content-Class: urn:content-classes:message
| | Importance: normal
| | Priority: normal
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| | Newsgroups: microsoft.public.sqlserver.server
| | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:367631
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| | What if the values come from another table (not constant values.)
| |
| |
| | "Michael Thomas [Microsoft]" wrote:
| |
| | > Hello Michael,
| | >
| | > Did you try to cast the value to binary? Here's a sample i've just
| written:
| | >
| | > create table mytable (Col1 nvarchar(20));
| | >
| | > insert into mytable values (N'Die Nu�, die Nüsse');
| | >
| | > select CAST ( Col1 AS binary (40)) from mytable
| | >
| | >
| | >
| | >
| | >
| | > The query will return the following value as binary. Note that there
is
| no
| | > collation on binary.
| | >
| | >
| | >
| | >
|
0x44006900650020004E007500DF002C00200064006900650020004E00FC0073007300650000
| | > 000000
| | >
| | >
| | > "Michael Bauers" <MichaelBauers@.discussions.microsoft.com> wrote in
| message
| | > news:54F4F3E1-0FD9-4209-9301-1182139A7DC1@.microsoft.com...
| | > > Is there a code page independent binary collation? I want a
collation
| | > that
| | > > will sort binary order that will not force translations from other
| | > collations.
| | > >
| | > > My problem is this: I want the user to be able to use any collation
| they
| | > > want. My process is going to work with their data in binary order.
| Even
| | > if
| | > > I could retrieve the collation they used, I don't know how to, in a
| | > program,
| | > > to convert that collation into a binary collation. For some
| collations,
| | > its
| | > > obvious. If their collation was SQL_CZECH_CP1350_CI_AS I could
| probably
| | > > construct Czech_BIN from that.
| | >
| | >
| | >
| |
|

Best way to print a report.

I'm trying to figure out the best way to print the report I have.
printing in excell will sort of work for me, but my tables are to wide.
Excell has a page options setup, which will fit to 1 page wide, and shrink
my tables.
But when I do this it fits it to 1/5 of the page, making it to small to see.
I have a book which talks about a 'Print' Format, but I don't see that
listed anywhere else.Exporting to PDF is your best bet.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:u0FR$CEyEHA.4004@.tk2msftngp13.phx.gbl...
> I'm trying to figure out the best way to print the report I have.
> printing in excell will sort of work for me, but my tables are to wide.
> Excell has a page options setup, which will fit to 1 page wide, and shrink
> my tables.
> But when I do this it fits it to 1/5 of the page, making it to small to
see.
> I have a book which talks about a 'Print' Format, but I don't see that
> listed anywhere else.
>

best way to order results sequentially starting from somewhere in the middle

I'm working with SQL Server 2005, and I'm trying to sort the results based on a user selected letter. Say the user selects 'D' to filter his results. I'd like to return the results starting from D followed by E, F, G...Z, A, B, C. What I'm getting is the results for the D entries at the top of the result set, followed by A, B, C, E...Z.

A solution comes to mind that would be very long and db intensive, by querying on 'like 'D', followed by like 'E', followed by like 'F', etc, but I'm sure that there is a much more efficient way to do this. Below is the code that I'm using now.

' where @.SortString = 'd' and @.Test is a temp Table

BEGIN

Insert into @.Test

Select CompanyName,ContactId, CompanyId

from vContacts where CompanyName like @.SortString +'%'

Order by CompanyName

Insert into @.Test

Select CompanyName,ContactId, CompanyId

from vContacts where CompanyName not like @.SortString +'%'

Order by CompanyName

END

Thanks in advance for your help

Code Snippet

SELECT
OrderSet = 1,
CompanyName,
ContactId,
CompanyId
FROM vContacts
WHERE CompanyName >= @.SortString

UNION

Select
2
CompanyName,
ContactId,
CompanyId
FROM vContacts
WHERE CompanyName < @.SortString

ORDER BY
OrderSet,

CompanyName

|||Thank you Arnie. Right on with the answer