Problems with sort records

[MeGGaN]

Hello, I have a DB in SQL Server and one in Access. My problem is that when I sort records in Access, Access uses a diferent way to sort in a manner that the records look like this:

ACEIMPVERLASERCTA
ACO000OFSM553
ACO000OFSM-553
ACUC06BACCARTON

When I sort the same records using SQL Server, the records look like this:

ACEIMPVERLASERCTA
ACO000OFSM-553
ACO000OFSM553
ACUC06BACCARTON

That affects the process we excecute and the problem occurs also in words with accents (á, é, í, ó, ú).

If anyone knows a way to solve this problem please let me know!!

Thanks a lot

 

[SonOfEmidec1100]

Ive come across the same issue after upgrading from 7.0 to 2000 and was to do with the collating sequence used for SQL server. In the end I decided to rebuild sql server with the collating seq set to match sql70.

But I think you can specify the collating sequence to use for a column within the sql query. Check out collating seq in BOL, or maybe someone else has more info.


cjw

 

[grahams]

Yes indeed - in SQL 2000 one can specify collation down to individual column scope. So for this instance you might want to change the collation for the whole database (if it's not going to adversely affect anything else), or just for that column. Either way look up Collation (changing) in BOL - it's a straightforward ALTER statement but beware dependent objects, which may also be affected and/or stop you from making the change without some other changes as well.

If you need more info, drop a line back

Graham

 

[MeGGaN]

Well thank you both very much, however, my problem is more with Access and I found out that this problem comes with the design of the new Jet DB 4.0 and I cannot change it. If I know anything else I'll let you know ;-)