Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    2

    Unhappy Unanswered: Collation Sequence Incompatibilities in ODBC

    We are getting the following errors when running our application on a new database server that has a default collation sequence different from the server we developed the application on.

    We are using SQL_Latin1_General_CP1_Cl_AS on our dev box, but some production boxes may not have this collation sequence since some of our clients are international.

    The database itself uses this sequence and show this sequence even on the other server. If we restore the database on a server with this sequence the problem disappears, but that is not always an option.

    I want to figure out how to bypass it, by either altering the collation sequence of the database or something else. In short I have to figure out how to make it work in their environment. Any ideas are much appreciated. Thanks. -- Henry :-)

    P.S. I checked Microsoft and know that the issue exists. The only article I could find had to do with SMS and their recommendation of installing on a server with a different collation sequence does not resolve my problem. I can't always do that.
    Our application is written in Cold Fusion 5.0

    ============ Error Messages ============

    Error Occurred While Processing Request
    Error Diagnostic Information
    ODBC Error Code = 37000 (Syntax error or access violation)


    [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve
    collation conflict for equal to operation.

    Error Occurred While Processing Request
    Error Diagnostic Information

    System Error &H80004005 (-2147467259).

    ===================================

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Question Re: Collation Sequence Incompatibilities in ODBC

    While this does not directly address the issue (My guess is both installs are 7.0?), perhaps your issue would be addressed by Sql Server 2k features. Sql Server 2k supports collation configurations somewhat differently from 7.0 i.e.(Sql Server 2K should support installing a server instance with a given collation and sucessfully hosting DBs with a variety of different collation configurations). It may be worth your while to test Sql Server 2k against your specific application(s) collation requirements.

  3. #3
    Join Date
    Nov 2002
    Posts
    2
    No. Both installs are SQL Server 2000. At least one is service pack 2.

  4. #4
    Join Date
    Oct 2002
    Posts
    369

    Question


    No. Both installs are SQL Server 2000. At least one is service pack 2.
    S1 Then you must know about / have attempted using the tsql collation clause in queries (to force character string expressions which may include columns to apply a particular collation cast)? While one might have to add the clause to numerous queries and tsql statements in application code or withing stored procs, views, etc., the advantage of this approach is that the DB itself is not changed. You might want to isolate some problem code on a test restore configuration to get a better idea of how involved the approach may be.

    S2 Another option may be (as you mentioned) to use Alter Table on the tables of the restored DB (on the target) to make it compatible with the applications in the locale where it is restored. Have you tested the results with this approach using a development test install / DB restore setup?

    S3 I suggest keeping service packs and hotfixes the same accross installations (if for nothing else, to eliminate added vairables in addressing possible issues).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •