sql 2008

Posted on 2014-12-25
Last Modified: 2014-12-26
collation conflict between "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_CS" in the equal to operation

How to resolve the issue

Question by:nitin_s_shah
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 78

Assisted Solution

arnold earned 200 total points
ID: 40517657
do you have two columns that have different collation types?

Add the follwoing to the comparison clause for each to have them at the same collation.

LVL 18

Accepted Solution

Simon earned 300 total points
ID: 40517846
@nitin: I presume you meant "SQL_Latin1_General_CP1_CI_AS" when you wrote "SQL_Latin1_General_CP1_CI_CS"? Arnold's suggestion will work for most cases, at least in terms of the query completing without error, but you may need to think about why an AS (Accent sensitive) collation was used for one of the objects you're working with. You may need to set both collations to "SQL_Latin1_General_CP1_CI_AS" to get the CORRECT result if you're working with text values in a language that uses accents and diacritical marks.

Author Comment

ID: 40518175
Dear All

I have created database with default parameter.  I have same table name in both database. while comparing both value alpha/numeric and numeric it give this error.
This is for your info.

Dear Arnold
I will try and get back to you.
Thanks for prompt reply.
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

LVL 18

Expert Comment

ID: 40518302
It would help if you could post the SQL statement of the query that is causing the problem. The problem may be in the JOIN or the WHERE clauses. You can specify collation wherever you are using a column from each database to compare character values.

Author Comment

ID: 40518545
UPDATE   EasyJVDPReport.DBO.ZD1OLORDT20141226210330    
    SET   EasyJVDPReport.DBO.ZD1OLORDT20141226210330.DTPOID           =   A.DTSBID,              
          EasyJVDPReport.DBO.ZD1OLORDT20141226210330.DTPODTID         =   A.DTID,                
          EasyJVDPReport.DBO.ZD1OLORDT20141226210330.DTPOTRNYR        =   A.DTTRNYR,              
          EasyJVDPReport.DBO.ZD1OLORDT20141226210330.DTPOORGYR        =   A.DTORGYR,              
          EasyJVDPReport.DBO.ZD1OLORDT20141226210330.DTPOTRNID        =   A.DTTRNID,              
          EasyJVDPReport.DBO.ZD1OLORDT20141226210330.DTPOTRNSUBTYPE   =   A.DTTRNSUBTYPE,              
          EasyJVDPReport.DBO.ZD1OLORDT20141226210330.DTPOTRNNO        =   A.DTTRNNO,              
          EasyJVDPReport.DBO.ZD1OLORDT20141226210330.DTPOTRNSRNO      =   A.DTTRNSRNO      
   WHERE  EasyJVDPReport.DBO.ZD1OLORDT20141226210330.DTSBID         =   A.DTPOID            AND              
          EasyJVDPReport.DBO.ZD1OLORDT20141226210330.DTID           =   A.DTPODTID          AND        
          EasyJVDPReport.DBO.ZD1OLORDT20141226210330.DTTRNYR        =   A.DTPOTRNYR         AND              
          EasyJVDPReport.DBO.ZD1OLORDT20141226210330.DTORGYR        =   A.DTPOORGYR         AND              
          EasyJVDPReport.DBO.ZD1OLORDT20141226210330.DTTRNID        =   A.DTPOTRNID         AND              
          EasyJVDPReport.DBO.ZD1OLORDT20141226210330.DTTRNSUBTYPE   =   A.DTPOTRNSUBTYPE    AND              
          EasyJVDPReport.DBO.ZD1OLORDT20141226210330.DTTRNNO        =   A.DTPOTRNNO         AND              
          EasyJVDPReport.DBO.ZD1OLORDT20141226210330.DTTRNSRNO      =   A.DTPOTRNSRNO       AND              
          EasyJVDPReport.DBO.ZD1OLORDT20141226210330.DTMSID         =  33015  AND                                                  
          A.DTMSID          =  33015

This is sql use in application.


Author Closing Comment

ID: 40519360
answer given by easy to follow.

Nitin Shah

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question