By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,220 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,220 IT Pros & Developers. It's quick & easy.

Client/server tip - inconsistent collation order betw. Access & Server

P: n/a
Hi all,

Frankly, this is such an off-beat thing, I don't know if anyone else here will
ever have the same issue. Just in case anyone does, though...

I needed to be able to search for the first string in a recordset that is the
same as a specified string value, or the one that would be right after that if
it doesn't exist. Several searches per second may occur, so I needed to
search within the recordset first, and only query the server again if the
desired record turned out not to be in the scope of the snapshot recordset.

The trouble is, to find the matching record, or the next record in sequence,
one would usually use a FindFirst with a ">=" expression, but this doesn't
always workd with a SQL Server back-end. The trouble is that the collation
order is different that what VBA/DAO use. For example, searching for the
first string >= "137" finds "13_8" first in the recordset, even though "137"
is available. SQL Server sorts "_" before numeric digits, but VBA and DAO
thing "_" comes after numeric digits.

So, this is tricky. We could write some function that hard-codes the sort
sequence, but that would be dependent upon a specific back-end with specific
settings. What we really want is a way to ask the SQL Server what its
collation order is, and somehow be able to make use of that in a DAO
FindFirst.

The clever idea I came up with is to use code to generate a long pass-through
batch with a series of statements as follows...
- SET NOCOUNT ON
- Create a temporary table with fields for char_code and char_value
- For each character code, 1-255, an insert statement to add the code and
character to the table.
- A SELECT statement that returns the character code, and usiing a subquery,
the count of how many records in the temp table have a lesser char_value.

The result is a recordset that has an association between each character code,
and the character's collation sequence according to SQL Server.

Finally, we use that recordset to populate an array (0-255) of the collation
sequences values (value+1, leave element 0 = 0), and have a function that can
be passed a string, and returns a string of concatentated 2-character
hexadecimal values of the collation priorities of each character in the
original string. Comparing the output of 2 of these strings gives the same
result as SQL Server would give comparing the 2 original strings.

Of course, we populate the array once, on the first call to the function, and
hold it in a static variable for reuse.

Since FindFist in DAO can call UDFs in Access, this new function can be used
within the FindFirst call to find the right record.
Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Have you tried / are you able to try within the constraints of you
application

SELECT testColumn FROM Table1 ORDER BY testColumn COLLATE
SQL_Latin1_General_Cp437_BIN

?

TTBOMK setting the collation to "SQL_Latin1_General_Cp437_BIN" will
give us the order we are accustomed to seeing in JET.

Nov 13 '05 #2

P: n/a
On 23 Oct 2005 06:08:56 -0700, "lylefair" <ly***********@aim.com> wrote:
Have you tried / are you able to try within the constraints of you
application

SELECT testColumn FROM Table1 ORDER BY testColumn COLLATE
SQL_Latin1_General_Cp437_BIN

?

TTBOMK setting the collation to "SQL_Latin1_General_Cp437_BIN" will
give us the order we are accustomed to seeing in JET.


Neat - I didn't know you could do that.

In this case, I think that's not quite practical, though, because the table is
large, and that expression would prevent using the index for finding/sorting.
If this were SQL Server 2000, I could possibly use an index on a computed
column to help, but this client is still on SQL Server 7.
Nov 13 '05 #3

P: n/a
I did this; perhaps it will effect what you want.
1. I opened the table definitionand removed the column index;
2. I right clicked the column and selected properties;
3. In the dialog I selected Collation and in the pulldown selected
Latin1_General;
3. From the radio boxes I selected BIN.
4. I then reindexed the column.
5. Now my Collation specification in the T-SQL was unnecessary;
6. I got the "normal" order with (just) ORDER BY testColumn.

(I did all this in Visual Web Developer 2005 Express Edition Beta's
DataBase Solution Window).

I'm assuming the index is used and that the index takes into account
the collation sequence specified for the column.

I don't know if this is applies to your situation, of course, and I
have no evidence that my supposition is correct..

Nov 13 '05 #4

P: n/a
On 23 Oct 2005 15:23:38 -0700, "lylefair" <ly***********@aim.com> wrote:
I did this; perhaps it will effect what you want.
1. I opened the table definitionand removed the column index;
2. I right clicked the column and selected properties;
3. In the dialog I selected Collation and in the pulldown selected
Latin1_General;
3. From the radio boxes I selected BIN.
4. I then reindexed the column.
5. Now my Collation specification in the T-SQL was unnecessary;
6. I got the "normal" order with (just) ORDER BY testColumn.

(I did all this in Visual Web Developer 2005 Express Edition Beta's
DataBase Solution Window).

I'm assuming the index is used and that the index takes into account
the collation sequence specified for the column.
No reason to think otherwise.
I don't know if this is applies to your situation, of course, and I
have no evidence that my supposition is correct..


It probably would work, and I had thought of it after reading your first
reply, but it would mean auditing the application for possible dependencies on
the older collation setting.

My way was harder to implement, but it uncouples my code from dependency on
the collation sequence used by the server. I think I'll stick with my first
solution.

Nov 13 '05 #5

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:8a********************************@4ax.com:
My way was harder to implement, but it uncouples my code from
dependency on the collation sequence used by the server. I think
I'll stick with my first solution.


But isn't it going to be a performance pig?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #6

P: n/a
Last try at this:

I think!!!! (but I have done only once)

1 a view of the table can be created
2 the collate property of the view can be set to Latin_General_BIN
3 the column or columns of the view can be indexed

perhaps this would allow one to use the collation order and an index
without messing with things previously created and depending on the
collation order of the table/column.

Nov 13 '05 #7

P: n/a
On Sun, 23 Oct 2005 20:48:00 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:8a********************************@4ax.com :
My way was harder to implement, but it uncouples my code from
dependency on the collation sequence used by the server. I think
I'll stick with my first solution.


But isn't it going to be a performance pig?


It is working now and not being a performance pig.

After the initial query and populating the array, it's just looping through
the characters, doing a lookup into the array by character code, and appending
hex strings to the result. The recordset that's acring as a buffer only holds
100 records, so the function is being called a maximum of 100 times per
FindFirst call.
Nov 13 '05 #8

P: n/a
On 23 Oct 2005 20:23:37 -0700, "lylefair" <ly***********@aim.com> wrote:
Last try at this:

I think!!!! (but I have done only once)

1 a view of the table can be created
2 the collate property of the view can be set to Latin_General_BIN
3 the column or columns of the view can be indexed

perhaps this would allow one to use the collation order and an index
without messing with things previously created and depending on the
collation order of the table/column.


Like the computed control idea, this requires SQL Server 2000. It looks like
I may be the last person on the planet to ever need to solve this problem any
other way than to use a server-side collation sequence somehow.
Nov 13 '05 #9

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:11********************************@4ax.com:
On Sun, 23 Oct 2005 20:48:00 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:8a********************************@4ax.co m:
My way was harder to implement, but it uncouples my code from
dependency on the collation sequence used by the server. I
think I'll stick with my first solution.


But isn't it going to be a performance pig?


It is working now and not being a performance pig.

After the initial query and populating the array, it's just
looping through the characters, doing a lookup into the array by
character code, and appending hex strings to the result. The
recordset that's acring as a buffer only holds 100 records, so the
function is being called a maximum of 100 times per FindFirst
call.


Ah. That's the part I missed -- I didn't realize you were pulling
small batches that weren't selected on the expression that was
causing the collation discrepancy.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.