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

BI studio

hi,

I have to connect to use a cube that is residing on different machine in order to develop a mining structure using MS associations rules algorithm.

Can anybody tell me the connection type (for Data Source) I should use so that I can use the same to create Data Source View OR Mining Structure?

I tried using OLAP, Analysis Service 9.0, OLAP 8.0, Data Mining - connection types. I could see only data source created using Data Mining connection while i was creating data source view.

Please help me as to which method I should use to connect cube on remote server to create a mining structure.

Thank you.

Aashutosh

SQL Server Data Mining does not allow you to create mining structure on cubes residing on remote servers or in another database on the same server - the source cube for an OLAP mining structure needs to be in the same database as the mining structure.

|||The only option would be to use DMX statements to create and train a mining structure (CREATE MINING STRUCTURE and INSERT INTO statements)|||

Hi,

thank you for your help.

I will try using DMX. One more question:

If I connect to remote server (which has the cube) using SQL server Management Studio to create a mining structure, will it modify the database on the remote server?

Aashutosh

|||

Creating a mining structure on the remote server is modifying the database as you are creating an object.

If you don't want to permanently change the database, you can use CREATE SESSION MINING STRUCTURE.... but you first have to enable session objects on that server (available in server properties).

If you are creating a mining structure on a server and populating from another analysis services database through OPENQUERY, then you do not modify the source database.

|||

Thank you for your help.

Will the same thing happen in case of relational database, i.e.

If I use relational database on remote server and create mining structure using BI development studio installed on local machine, will it modify the database on the remote server?

thank you once again.

Aashutosh Magdum

BI studio

hi,

I have to connect to use a cube that is residing on different machine in order to develop a mining structure using MS associations rules algorithm.

Can anybody tell me the connection type (for Data Source) I should use so that I can use the same to create Data Source View OR Mining Structure?

I tried using OLAP, Analysis Service 9.0, OLAP 8.0, Data Mining - connection types. I could see only data source created using Data Mining connection while i was creating data source view.

Please help me as to which method I should use to connect cube on remote server to create a mining structure.

Thank you.

Aashutosh

SQL Server Data Mining does not allow you to create mining structure on cubes residing on remote servers or in another database on the same server - the source cube for an OLAP mining structure needs to be in the same database as the mining structure.

|||The only option would be to use DMX statements to create and train a mining structure (CREATE MINING STRUCTURE and INSERT INTO statements)|||

Hi,

thank you for your help.

I will try using DMX. One more question:

If I connect to remote server (which has the cube) using SQL server Management Studio to create a mining structure, will it modify the database on the remote server?

Aashutosh

|||

Creating a mining structure on the remote server is modifying the database as you are creating an object.

If you don't want to permanently change the database, you can use CREATE SESSION MINING STRUCTURE.... but you first have to enable session objects on that server (available in server properties).

If you are creating a mining structure on a server and populating from another analysis services database through OPENQUERY, then you do not modify the source database.

|||

Thank you for your help.

Will the same thing happen in case of relational database, i.e.

If I use relational database on remote server and create mining structure using BI development studio installed on local machine, will it modify the database on the remote server?

thank you once again.

Aashutosh Magdum

Best way to worj with replication

Hello there
I have subscription who work perfectly
In order to see all the chages at the time it being done I set it to work as
soon as the publicion is done.
But this way cost in working of comouter. the computers are working mutch
more sloly.
So i set it to work at 00:00 every day.
It seems that on 00:00 it do update the subsriber. But is there a way to run
it maually?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
Sorry Roy - I didn't really follow what your scenario is. However to answer
your last question, running the subscription agent (distribution/merge)
manually can be achieved by running sp_start_job, or windows synchronization
manager (pull) out of the box or by programming the activeX controls if you
want a custom solution.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||What Version of SQL Server are you using? 2000, 2005?
Scott E. Hunley (MCAD)
Measure Twice, Cut Once...
"Roy Goldhammer" wrote:

> Hello there
> I have subscription who work perfectly
> In order to see all the chages at the time it being done I set it to work as
> soon as the publicion is done.
> But this way cost in working of comouter. the computers are working mutch
> more sloly.
> So i set it to work at 00:00 every day.
> It seems that on 00:00 it do update the subsriber. But is there a way to run
> it maually?
> --
> ??òé a?ì????
> òúé? ?e??ú ú??e?
> èì' 03-5611606
> ?ìX' 050-7709399
> Xé?ééì: roy@.atidsm.co.il
>
>
|||Whell Scott: 2000
When i sed the push subscription to work immediatly when the chages are done
be the publisher, the changes are being done on the subsciber in 3 seconds,
bet the performance of the disbributor computer becomes mutch more slower.
and that for only one table on my huge database (Approx 1.5Gb)
My main question is: if i will run the replication on the entire database,
how mutch it will cost on the performance of the computer? and if i will run
it at 00:00
and the changes that are being made on single day can be more then 10000
records a day, what will happen?
My second question: As i see on the wizart, when i create Transactional
replication, it said that at first time he run Snapshot replication.
Is that at first time on the subsciber i need only an empty database with
the same structure of the publisher database?
"Scott Hunley" <ScottHunley@.discussions.microsoft.com> wrote in message
news:E90C578A-8256-454F-AEE5-D744F529B760@.microsoft.com...[vbcol=seagreen]
> What Version of SQL Server are you using? 2000, 2005?
> --
> Scott E. Hunley (MCAD)
> Measure Twice, Cut Once...
>
> "Roy Goldhammer" wrote:

best way to preserve all dependencies

I know ive asked this before and created seperate publications for tables
and views,etc in order to avoid conflicts such as view being created before
the table,etc..
What do u guys prefer as the best method to handle this, especially if
reinitialize a publication that has tables in it and it results in error as
the table properties has a drop to it and hence would not be able to drop as
a view is present..
There must be better ways to handle all of this . If only I could create one
publication for all objects belonging to one database ..if only all of the
dependencies were ordered.
Hassan,
like you, I have separate publications - one for tables, one for views and
one for UDFs.
AFAIK, dropping a table should be fine unless the view has been created
using schemabinding.
For those cases where dependency info of views is incorrect, sp_refreshview
is sometimes useful; sometimes use sp_addscriptexec; sometimes just add the
view manually on the subscriber when there is connectivity and there is only
one subscriber.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

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