Collation Conflict

6
votes

i always got the message "Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation." when i run the script that came from MSSQL 2005 server. btw i used MSSQL 2008.

here is the script

USE [database1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec rsp_SOA '', '', '', '', '', '', '40050000', '40050000', '05/01/2010', '05/31/2010', '000-00','Dont Show Note'

ALTER procedure [dbo].[rsp_SOA]
    @cCompName VARCHAR(100) ,
    @cAddress1 VARCHAR(200) ,
    @cAddress2 VARCHAR(200),
    @cReportTitle VARCHAR(200),
    @cCriteria1 VARCHAR(200),
    @cCriteria VARCHAR(200),

    @cFrom VARCHAR(25),
    @cTo VARCHAR(25),
    @dDateFrom VARCHAR(10),
    @dDateTo VARCHAR(10),
    @cCompID VARCHAR(10),
    @cFilter VARCHAR(30)
as

declare @cSQL VARCHAR(200),
        @cSQL1 VARCHAR(200),
        @cmd VARCHAR(8000),
        @cmd1 VARCHAR(8000),
        @cTempTable varchar (50),
        @cTempTable1 varchar (50),
        @nInterval VARCHAR(3),
        @nCurrent INTEGER, 
        @nInterval1 varchar(3), 
        @nInterval2 varchar(3), 
        @nInterval3 varchar(3), 
        @nInterval4 varchar(3), 
        @nInterval5 varchar(3), 
        @dd INTEGER,
        @cValue1 VARCHAR(100),
        @cValue2 VARCHAR(100),
        @cValue3 VARCHAR(100),
        @cValue4 VARCHAR(100),
        @cValue5 VARCHAR(100)

set @nInterval = 30
set @nInterval1 = cast(@nInterval * 1 as varchar(3))
set @nInterval2 = cast(@nInterval * 2 as varchar(3))
set @nInterval3 = cast(@nInterval * 3 as varchar(3))
set @nInterval4 = cast(@nInterval * 4 as varchar(3))
set @nInterval5 = cast(@nInterval * 5  as varchar(3))

SET @cValue1 = CONVERT(VARCHAR(10),1,110) + ' - ' + CONVERT(VARCHAR(10),@nInterval1,110) + ' Days'
SET @cValue2 = CONVERT(VARCHAR(10),@nInterval1 + 1,110) + ' - ' + CONVERT(VARCHAR(10),@nInterval2,110) + ' Days'
SET @cValue3 = CONVERT(VARCHAR(10),@nInterval2 + 1,110) + ' - ' + CONVERT(VARCHAR(10),@nInterval3,110) + ' Days'
SET @cValue4 = CONVERT(VARCHAR(10),@nInterval3 + 1,110) + ' - ' + CONVERT(VARCHAR(10),@nInterval4,110) + ' Days'
SET @cValue5 = 'Above ' + CONVERT(VARCHAR(10),@nInterval4,110) + ' Days'

create table ##Interval
(
    cCompID varchar(20),
    nOrder int,
    cInterval varchar(20),
    cCode varchar(20)
)


---------------------------
--get all the clients
---------------------------
SELECT * INTO ##temp1 FROM 
(
    select cCode from client_customer where cCode = @cFrom union all
    select cGroupCode from client_customer where cCode = @cFrom union all
    select cBillingCompany from client_customer where cCode = @cFrom union all
    select cArea from client_customer where cCode = @cFrom
)a

---------------------------
--determining the balance of the invoices
---------------------------
SELECT * INTO ##temp2 FROM 
(
    (
        select a.cCompanyID, a.cInvNo, sum(a.nGross) as nSales, 0 as nPaid, 0 as nDebit, 0 as nCredit, 0 as nReturns 
        from sales a 
        where a.lCancelled = 0 and a.cPayType<>'Cash' 
            and a.cCode in (select * FROM ##temp1) --get all the clients
            and a.cCompanyID = @cCompID
        group by a.cCompanyID, a.cInvNo
    )
    union all
    (
        select a.cCompanyID, a.cInvNo, 0 as nSales, sum(a.nApplied) as nPaid, 0 as nDebit, 0 as nCredit, 0 as nReturns
        from pr_t a 
        left outer join pr b on a.cTranNo = b.cTranNo and a.cCompanyID = b.cCompanyID
        left outer join sales c on a.cInvNo = c.cInvNo and a.cCompanyID = c.cCompanyID
        where b.lCancelled = 0 
            and c.cCode  in (select * FROM ##temp1) --get all the clients
            and a.cCompanyID = @cCompID
        group by a.cCompanyID, a.cInvNo
    )
    union all
    (
        select a.cCompanyID, a.cInvNo, 0 as nSales, 0 as nPaid, sum(a.nDebit) as nDebit, 0 as nCredit, 0 as nReturns
        from ar_t a 
        left outer join ar b on a.cTranNo = b.cTranNo and a.cCompanyID = b.cCompanyID
        where b.cType = 'Debit' and b.lCancelled = 0 and b.lApproved = 1
            and b.cCode  in (select * FROM ##temp1) --get all the clients
            and a.cCompanyID = @cCompID
        group by a.cCompanyID, a.cInvNo
    )
    union all
    (
        select a.cCompanyID, a.cInvNo, 0 as nSales, 0 as nPaid, 0 as nDebit, sum(a.nCredit) as nCredit, 0 as nReturns
        from ar_t a 
        left outer join ar b on a.cTranNo = b.cTranNo and a.cCompanyID = b.cCompanyID
        where b.cType = 'Credit' and b.lSalesReturn = 0 and b.lCancelled = 0 and b.lApproved = 1
            and b.cCode  in (select * FROM ##temp1) --get all the clients
            and a.cCompanyID = @cCompID
        group by a.cCompanyID, a.cInvNo
    )
    union all
    (
        select a.cCompanyID, a.cInvNo, 
        0 as nSales, 0 as nPaid, 0 as nDebit, 0 as nCredit, sum(a.nCredit) as nReturns
        from ar_t a 
        left outer join ar b on a.cTranNo = b.cTranNo and a.cCompanyID = b.cCompanyID
        where b.cType = 'Credit' and b.lSalesReturn = 1 and b.lCancelled = 0 and b.lApproved = 1 
            and b.cCode  in (select * FROM ##temp1) --get all the clients
            and a.cCompanyID = @cCompID
        group by a.cCompanyID, a.cInvNo
    )
)a


-----------------------
--main script for creating the temp ##AR
-----------------------
select a.*, b.cRefNo, b.dRefDate, b.nBeg, b.nEnd, b.nConsumed, b.nConsumedKG, b.nPrice, b.nAmount, b.nVat, b.nWHT, b.nNet, 
            case when a.nDue <=0 then 'Current'
                 when a.nDue > 0 and a.nDue <= @nInterval1 then @cValue1
                 when a.nDue > @nInterval1 and a.nDue <= @nInterval2 then @cValue2
                 when a.nDue > @nInterval2 and a.nDue <= @nInterval3 then @cValue3
                 when a.nDue > @nInterval3 and a.nDue <= @nInterval4 then @cValue4
                 when a.nDue > @nInterval4 then @cValue5
            end as cInterval,b.cTenantName into ##AR
    from
    (
        select a.cCompanyID, b.cCode, a.cInvNo, (isnull(sum(a.nSales), 0) - isnull(sum(a.nPaid), 0) + isnull(sum(a.nDebit), 0) - isnull(sum(a.nCredit), 0) - isnull(sum(a.nReturns), 0)) as nBalance,
                b.dDate, b.cSMan, b.nGross, b.cTerm, c.cValue, (cast((cast(convert(varchar(20),getdate(),101) as datetime) - b.dDate) as integer) - cast(c.cValue as integer)) as nDue, dateadd(d,cast(c.cValue as integer),b.dDate) as dDueDate
        from 
        (
            select * FROM ##temp2 --determining the balance of the invoices
        ) a 
        left outer join sales b on a.cInvNo = b.cInvNo and  a.cCompanyID = b.cCompanyID
        left outer join parameter_user c on b.cTerm = c.cParamName and c.cType = 'TERMS'
        where b.cCode  in (select * FROM ##temp1) --get all the clients
            and a.cCompanyID = @cCompID
        group by a.cCompanyID, b.cCode, a.cInvNo, b.dDate, b.cSMan, b.nGross, b.cTerm, c.cValue
        having (isnull(sum(a.nSales), 0) - isnull(sum(a.nPaid), 0) + isnull(sum(a.nDebit), 0) - isnull(sum(a.nCredit), 0) - isnull(sum(a.nReturns), 0)) > 0
    )a
    left outer join 
    (
        select a.cCompanyID, a.cInvNo, b.cRefNo, d.dDate as dRefDate, 
            case when a.cType = 'Meter' then b.nMeterIn
                 when a.cType = 'Weight' then b.nWeightOut
            else 0 end as nBeg,
            case when a.cType = 'Meter' then b.nMeterOut
                 when a.cType = 'Weight' then b.nWeightIn
            else 0 end as nEnd, b.nConsumed, 
            case when a.cType = 'Meter' then b.nConsumedKG
                 when a.cType = 'Weight' then (b.nWeightOut - b.nWeightIn)
            else 0 end as nConsumedKG, b.nPrice, (b.nAmount / 1.12) as nAmount,
                 (b.nAmount - (b.nAmount / 1.12)) as nVat,
                 (CASE WHEN e.lEWT=1 THEN ((b.nAmount / 1.12) * e.nEWT/100) ELSE 0 END ) as nWHT, b.nAmount as nNet,f.cTenantName


        from sales a left outer join sales_t b on a.cInvNo = b.cInvNo and a.cCompanyID = b.cCompanyID
            left outer join item c on b.cItemNo = c.cItemNo and a.cCompanyID = c.cCompanyID
            left outer join dr d on d.cDRNo = b.cRefNo  
            left outer JOIN CLIENT_CUSTOMER e ON a.cCode = e.cCode
            left outer JOIN METER_READING_T f ON b.cMRNo = f.cTransNo AND b.nMeterIn = f.nMeterIn
        where c.cType <> 'CYLINDER' and a.cType <> 'Invoice' and a.cCode  in (select * FROM ##temp1) --get all the clients
            and a.cCompanyID = @cCompID

        union all

        select a.cCompanyID, a.cInvNo, d.cDRNo as cRefNo, d.dDate as dRefDate, 0 as nBeg,
            0 as nEnd, b.nConsumed, 0 as nConsumedKG, b.nPrice, b.nAmount, 0 as nVat, 0 as nWHT, 0 as nNet,'' as cTenantName
        from sales a left outer join sales_t b on a.cInvNo = b.cInvNo and a.cCompanyID = b.cCompanyID
            left outer join item c on b.cItemNo = c.cItemNo and a.cCompanyID = c.cCompanyID
            left outer join dr d on a.cInvNo = d.cInvNo         
        where c.cType <> 'CYLINDER' and a.cType = 'Invoice' and d.cDRNo is null and a.cCode  in (select * FROM ##temp1) --get all the clients
            and a.cCompanyID = @cCompID
    )b on a.cInvNo = b.cInvNo and a.cCompanyID = b.cCompanyID


-----------------------
--main script for creating the temp ##Interval
-----------------------
insert into ##Interval
select distinct @cCompID, 1, 'Above 120 Days', cCode from ##AR
insert into ##Interval
select distinct @cCompID, 2, '91 - 120 Days', cCode from ##AR
insert into ##Interval
select distinct @cCompID, 3, '61 - 90 Days', cCode from ##AR
insert into ##Interval
select distinct @cCompID, 4, '31 - 60 Days', cCode from ##AR
insert into ##Interval
select distinct @cCompID, 5, '1 - 30 Days', cCode from ##AR
insert into ##Interval
select distinct @cCompID, 6, 'Current', cCode from ##AR




--------------------------------------
--displaying the result
--------------------------------------

select a.nOrder, a.cInterval, a.cCode, c.cName, (c.cFirstName + ' ' + case when isnull(c.cMiddleInitial,'')='' then '' else c.cMiddleInitial + ' ' end + c.cLastName) as cCustomerName, (case when isnull(c.cBusinessName,'')='' then c.cName else c.cBusinessName end) as cBusinessName,
        c.cAddress, c.cLastName, c.cJobTitle, d.cCompanyName, d.cAddress1, d.cAddress2, d.cPhone, d.cfax, d.cEmail, c.cTerm, b.cInvNo, b.nBalance, b.dDate, b.nGross, b.cRefNo, b.dRefDate,  b.nBeg, b.nEnd, b.nConsumed, b.nCOnsumedKG, 
        b.nPrice, b.nAmount, b.nVat, b.nWHT, b.nNet, isnull(e.nTotalPDC,0) as nTotalPDC, f.nTotalAR, f.nTotalPastDue, c.nLimit, @dDateFrom as dStartDate, @dDateTo as dEndDate,--g.cTenantName,
        b.cTenantName,(CASE WHEN @cFilter = 'Show Note' THEN 1 ELSE 0 END) AS lNote
from ##Interval a
left outer join ##AR b on a.cInterval = b.cInterval and a.cCode = b.cCode
left outer join client_customer c on a.cCode = c.cCode
left outer join company d on a.cCompID = d.cCompanyID
left outer join
(
    select a.cCode, sum(a.nAmount) as nTotalPDC 
    from checks a
    where a.lDeposited=0 and a.cTransType = 'COL' and a.cCode = @cFrom and a.cCompanyID = @cCompID
    group by a.cCode
)e on a.cCode=e.cCode
left outer join
(
    --select a.cCode, sum(a.nBalance) as nTotalAR, sum(case when a.cInterval <> 'Current' then isnull(a.nBalance,0) else 0 end) as nTotalPastDue
    --from ##AR a
    --group by a.cCode

    SELECT a.cCode,SUM(nTotalAR) AS nTotalAR,SUM(nTotalPastDue) AS nTotalPastDue
    FROM 
        (select distinct a.cCode, (a.nBalance) as nTotalAR, 
            (case when a.cInterval <> 'Current' then isnull(a.nBalance,0) else 0 end) as nTotalPastDue
        from ##AR a) a
    GROUP BY a.cCode

)f on a.cCode=f.cCode
order by a.nOrder, a.cCode


 drop table ##temp1
 drop table ##temp2
 drop table ##Interval
 drop table ##AR


/*
  select * from ##temp1
  select * from ##temp2
  select * from ##Interval
  select * from ##AR
*/

the sql server always pointing the error to this line

--------------------------------------
--displaying the result
--------------------------------------

select a.nOrder, a.cInterval, a.cCode, c.cName, (c.cFirstName + ' ' + case when isnull(c.cMiddleInitial,'')='' then '' else c.cMiddleInitial + ' ' end + c.cLastName) as cCustomerName, (case when isnull(c.cBusinessName,'')='' then c.cName else c.cBusinessName end) as cBusinessName,
        c.cAddress, c.cLastName, c.cJobTitle, d.cCompanyName, d.cAddress1, d.cAddress2, d.cPhone, d.cfax, d.cEmail, c.cTerm, b.cInvNo, b.nBalance, b.dDate, b.nGross, b.cRefNo, b.dRefDate,  b.nBeg, b.nEnd, b.nConsumed, b.nCOnsumedKG, 
        b.nPrice, b.nAmount, b.nVat, b.nWHT, b.nNet, isnull(e.nTotalPDC,0) as nTotalPDC, f.nTotalAR, f.nTotalPastDue, c.nLimit, @dDateFrom as dStartDate, @dDateTo as dEndDate,--g.cTenantName,
        b.cTenantName,(CASE WHEN @cFilter = 'Show Note' THEN 1 ELSE 0 END) AS lNote
3
sqlsql-serversql-server-2005sql-server-2008collation
What does your script look like? - LittleBobbyTables - Au Revoir
So which statement does it actually errors? And which column in your tables have the accent sensitive collation, and which have the one insensitive? what is the default collation of your database, and what is the one of your server? - Remus Rusanu

3 Answers

6
votes

When you compare/aggregate columns with different collations you must coherence at least one of them to the collation you want, using the COLLATE expression. Eg, say you have a column c1 with collation Latin1_General_CI_AI in table Table1 and a column c2 with collation SQL_Latin1_General_CP1_CI_AS in table Table2 and you want to union the two:

SELECT c1 COLLATE SQL_Latin1_General_CP1_CI_AS
FROM Table1
UNION ALL
SELECT c2 
FROM Table2;
4
votes

It's probably either:

  1. There are some specific character-typed columns or tables created with a different COLLATION, then you're doing a comparison on columns of different collation.

  2. You're running a script against a database that's a different collation to your server, and the script uses TEMP TABLEs. Temp tables are created in tempdb database, therefore they get the COLLATION of tempdb (ie the server collation). Most scripts don't expect this, so comparisons against temp table columns don't specify a collation.

You can fix this either by changing your tables/columns so the collations match, or changing your SQL so that you specify which collation to use in each string comparison.

e.g. change this: 

select * 
from someTable 
inner join #t on someTable.col1 = #t.col1
where #t.col2 = 'blah' collate database default

to this: 

select * 
from someTable 
inner join #t on someTable.col1 = #t.col1 collate database default
where #t.col2 = 'blah' collate database default

UPDATE:

Based on the script you've posted, I'd guess it's my second suggestion above.

SQL Server is saying the problem is in your last massive query. I suggest one or more of the following:

  • To identify the particular query that's a problem I'd suggest you break out the subqueries and have the script execute them separately. Just for debugging.

  • Try calling sp_help '##temp1' and for the other tables after they're created, and compare the column collations with those in your main tables.

  • Add collate database default to the end of each of your comparisons (where needed if you use the sp_help suggestion)

  • Create your temp tables explicitly instead of via SELECT INTO. That way you can specify the columns' collation. Or use table variables instead.

2
votes

It looks like this question is an old question but I'll supply answer anyway, because I have fairly recently had a similar problem, and thought it may save someone else some grief!

Check your table create scripts for the database because they may be setting the collations on the columns at create time. Which you may not necessarily cause an error till later in the development cycle. i.e like the instance you identify here in that you are trying to preform an equality comparison 

select a.nOrder, a.cInterval, a.cCode, c.cName, (c.cFirstName + ' ' + case when isnull(c.cMiddleInitial,'')='' then '' else c.cMiddleInitial + ' ' end + c.cLastName) as cCustomerName, (case when **isnull(c.cBusinessName,'')=''** then c.cName else c.cBusinessName end) as cBusinessName,         c.cAddress, c.cLastName, c.cJobTitle, d.cCompanyName, d.cAddress1, d.cAddress2, d.cPhone, d.cfax, d.cEmail, c.cTerm, b.cInvNo, b.nBalance, b.dDate, b.nGross, b.cRefNo, b.dRefDate,  b.nBeg, b.nEnd, b.nConsumed, b.nCOnsumedKG,          b.nPrice, b.nAmount, b.nVat, b.nWHT, b.nNet, isnull(e.nTotalPDC,0) as nTotalPDC, f.nTotalAR, f.nTotalPastDue, c.nLimit, @dDateFrom as dStartDate, @dDateTo as dEndDate,--g.cTenantName,         b.cTenantName,(CASE WHEN @cFilter = 'Show Note' THEN 1 ELSE 0 END) AS lNot

I have highlighted it in bold. My problem seem to arise from the fact that some of our tables were built in SQL management studio and some tables were built in Visual studio, which had different collation settings enabled. It took quite some time to figure this out too. It was only after reviewing the Database scripts that I picked it up. We have now moved on to using Database developer edition in visual studio premium which has now simplified our database confiuration management and unit testing