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.
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What is this datetime? 1 33
Parse this column 6 38
how to make geography query faster?  SQL 7 60
Applying Roles in Common Scenarios 3 42
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…

739 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