Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 112
  • Last Modified:

sql 2008

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
nitin_s_shah
Asked:
nitin_s_shah
  • 3
  • 2
2 Solutions
 
arnoldCommented:
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
 
SimonCommented:
@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
 
nitin_s_shahAuthor Commented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
SimonCommented:
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
 
nitin_s_shahAuthor Commented:
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
 
nitin_s_shahAuthor Commented:
answer given by easy to follow.

Thanks
Nitin Shah
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now