Solved

sql 2008

Posted on 2014-12-25
6
106 Views
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

Thanks
0
Comment
Question by:nitin_s_shah
  • 3
  • 2
6 Comments
 
LVL 77

Assisted Solution

by:arnold
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.
COLLATE DATABASE_DEFAULT

column1 COLLATE DATABASE_DEFAULT = column2 COLLATE DATABASE_DEFAULT
0
 
LVL 18

Accepted Solution

by:
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.
0
 

Author Comment

by:nitin_s_shah
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.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 18

Expert Comment

by:Simon
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.
0
 

Author Comment

by:nitin_s_shah
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      
    FROM  EASYJVDP011516.DBO.EBT_SBDTDATA    A      
   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.

thanks
0
 

Author Closing Comment

by:nitin_s_shah
ID: 40519360
answer given by easy to follow.

Thanks
Nitin Shah
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

828 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