Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


sql 2008

Posted on 2014-12-25
Medium Priority
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 79

Assisted Solution

arnold earned 800 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 1200 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.
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

721 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