473,323 Members | 1,589 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

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

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
9 3729
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Robert Mazur | last post by:
Solaris 9 - sparc 64bit MySQL 5.0 alpha (installed using binary from MySQL) --------------------------------- Has anyone expereinced this? The client will launch locally and process SQL...
2
by: Sue Swanson | last post by:
Yesterday I received a response to my CI/CS Collation problem and the recommendation was to try and restore a CI Collation database to a CS Collation database. After creating a blank CS database a...
10
by: Thomas Richards | last post by:
I have two SQL Server 2000 machines. The same file is sent nightly to each server and a stored proc uses BULK INSERT to load it into a staging table for processing. Once I've bcp'ed it in, I put...
67
by: Mike MacSween | last post by:
I've got a SQL Server database. Nearly finished. It's going to go on a single non networked machine. One day somebody might get access to it over ADSL (probably TS), but for now it's a single user...
11
by: Timothy Shih | last post by:
Hi, I am having a freezing issue with my application. My application serves several remotable objects, all of which must be initialized before their use. Furthermore, some of them depend on each...
6
by: Bob | last post by:
Having trouble getting started. I created an instance of MSDE called VSDOTNET on a computer with the name of sysdev. Using MS SQL Web Adminstrator I created a database called temp with a...
0
by: jacobmarble | last post by:
Hey NG- I'm trying to order a column such that it's in order by pinyin, hence an ORDER BY clause would change this: ma mà má ma ma
21
by: Peter Nurse | last post by:
I have just upgraded to SQL Server 2005 from SQL Server 2000. In Microsoft SQL Server Management Studio, when I click on database properties, I receive the following error:- Cannot resolve the...
3
by: aj | last post by:
A few collation questions on SQL Server 2005 SP2, which I'll call SQLS. The default collation for SQLS is apparently SQL_Latin1_General_CP1_CI_AS. I wish to use a variation of this,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.