SQL Collation Conundrums

Discussion in 'ASP General' started by Andrew Butchart, Mar 18, 2005.

  1. I have had some problems with a web site that I am working on. The
    DBA set up all of the tables using the default collation sequence of
    SQL_Latin1_General_CP1_CI_AS HOWEVER, the recordset that is being
    returned is "out of order" according to my ASP code. For example, ASP
    believes that the string "6\" is greater than "6001001" whereas SQL
    returns the "6\" record first.

    Is there any way to set the default comparison between string values
    in ASP to match the order that SQL is returning, or can I update my
    query to give me a collation sequence that ASP will like? Changing
    the collation sequence in the database isn't a viable option.

    Thanks

    Andrew Butchar
     
    Andrew Butchart, Mar 18, 2005
    #1
    1. Advertising

  2. Andrew Butchart

    John Bell Guest

    Hi

    You should be able to use a binary collation to get SQL Server to work like
    your ASP

    SELECT col1
    FROM (
    SELECT '6\' COLLATE Latin1_General_BIN AS Col1
    UNION ALL SELECT '6001001' ) A
    order by col1

    To do the opposite in your ASP code I think you would have to write a
    function that does character by character comparisons.


    John

    "Andrew Butchart" <> wrote in message
    news:...
    >I have had some problems with a web site that I am working on. The
    > DBA set up all of the tables using the default collation sequence of
    > SQL_Latin1_General_CP1_CI_AS HOWEVER, the recordset that is being
    > returned is "out of order" according to my ASP code. For example, ASP
    > believes that the string "6\" is greater than "6001001" whereas SQL
    > returns the "6\" record first.
    >
    > Is there any way to set the default comparison between string values
    > in ASP to match the order that SQL is returning, or can I update my
    > query to give me a collation sequence that ASP will like? Changing
    > the collation sequence in the database isn't a viable option.
    >
    > Thanks
    >
    > Andrew Butchar
    >
     
    John Bell, Mar 19, 2005
    #2
    1. Advertising

  3. Dandy - that's it! I just used the COLLATE on my Order By statement
    and it's exactly what I needed.

    Many thanks John

    Andrew Butchart


    "John Bell" <> wrote in message news:<>...
    > Hi
    >
    > You should be able to use a binary collation to get SQL Server to work like
    > your ASP
    >
    > SELECT col1
    > FROM (
    > SELECT '6\' COLLATE Latin1_General_BIN AS Col1
    > UNION ALL SELECT '6001001' ) A
    > order by col1
    >
    > To do the opposite in your ASP code I think you would have to write a
    > function that does character by character comparisons.
    >
    >
    > John
    >
    > "Andrew Butchart" <> wrote in message
    > news:...
    > >I have had some problems with a web site that I am working on. The
    > > DBA set up all of the tables using the default collation sequence of
    > > SQL_Latin1_General_CP1_CI_AS HOWEVER, the recordset that is being
    > > returned is "out of order" according to my ASP code. For example, ASP
    > > believes that the string "6\" is greater than "6001001" whereas SQL
    > > returns the "6\" record first.
    > >
    > > Is there any way to set the default comparison between string values
    > > in ASP to match the order that SQL is returning, or can I update my
    > > query to give me a collation sequence that ASP will like? Changing
    > > the collation sequence in the database isn't a viable option.
    > >
    > > Thanks
    > >
    > > Andrew Butchar
    > >
     
    Andrew Butchart, Mar 20, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    1
    Views:
    383
    Boudewijn Dijkstra
    Aug 13, 2005
  2. papaja
    Replies:
    0
    Views:
    442
    papaja
    Jul 13, 2006
  3. Richard Hallgren

    Collation error in aspnet_regsql.exe

    Richard Hallgren, Aug 10, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    1,024
    Richard Hallgren
    Aug 10, 2006
  4. Soso
    Replies:
    1
    Views:
    288
    Victor Bazarov
    Sep 26, 2007
  5. ecoolone
    Replies:
    0
    Views:
    775
    ecoolone
    Jan 3, 2008
Loading...

Share This Page