排序规则冲突

问题描述：

我总是收到消息“在等于操作中无法解决”SQL_Latin1_General_CP1_CI_AS“和”Latin1_General_CI_AI“之间的排序规则冲突。” 当我运行来自MSSQL 2005服务器的脚本。顺便说一句，我用MSSQL 2008排序规则冲突

这里是脚本

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 
*/

总是指向错误这条线在SQL Server

-------------------------------------- 
--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

您的脚本是什么样的？ – LittleBobbyTables 2010-09-27 01:11:52

那么哪个语句实际上是错误的？你的表中哪一列具有重音敏感归类，哪一个不敏感？什么是你的数据库的默认排序规则，你的服务器是什么？ – 2010-09-27 04:54:41

答

这可能是两种：

有是用一个不同的COLLATION创建的一些特定的字符类型的列或表，那么你正在做一个比较在不同排序规则的列上。
您正在针对与您的服务器不同的数据库的数据库运行脚本，并且该脚本使用TEMP TABLE。临时表在tempdb数据库中创建，因此他们获得tempdb的COLLATION（即服务器整理）。大多数脚本不期望这样做，所以与临时表列的比较不会指定排序规则。

可以解决这个问题，通过更改你的表/列，以便排序规则匹配，或者改变您的SQL，让您指定每个字符串比较中使用哪个归类。

例如更改此设置：

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

这样：

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

UPDATE：

根据您所张贴的剧本，我想这是我第二次以上的建议。

SQL Server说这个问题出现在你上次的大规模查询中。我建议以下的一种或多种：

识别特定的查询，这是一个问题，我建议你打出来的子查询，并有脚本分别执行他们。只是为了调试。
尝试在创建后调用sp_help '##temp1'和其他表，并将列归类与主表中的列归类进行比较。
添加collate database default到每个比较的结束（在需要的地方，如果你使用的sp_help建议）
通过SELECT INTO创建临时表显式代替。这样你可以指定列的排序规则。或者改用表格变量。

请参阅我的脚本。谢谢 – illumi 2010-09-27 01:38:31

@dave，他给了你答案。从脚本中，没有人能告诉你你需要COLLATE语句。归类冲突发生在某个表的列被设置为与另一个表不同的排序规则或不同于服务器缺省值或数据库缺省值的地方。 – ErikE 2010-09-27 06:09:13

答

当你比较/聚合列与您必须相干其中至少有一个你想要的整理，使用COLLATE表达不同的排序规则。例如，假设你有一个列c1在表Table1整理Latin1_General_CI_AI和表Table2与整理SQL_Latin1_General_CP1_CI_AS列c2，并要联合两个：

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

答

它看起来像这个问题是一个老问题，但我无论如何，我会提供答案，因为我最近有一个类似的问题，并认为它可能会拯救别人一些悲伤！

检查您的表是否为数据库创建脚本，因为它们可能会在创建时设置列上的排序规则。在开发周期的后期，您可能不一定会导致错误。即像比如你确定在这里，你正试图瓶坯的相等比较

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

我已经用粗体突出显示它。我的问题似乎来自这样的事实，即我们的一些表是在SQL管理工作室中构建的，并且某些表是在Visual Studio中构建的，该工作室启用了不同的排序规则设置。花了相当长的时间才弄明白这一点。只是在审查了我选择的数据库脚本之后。我们现在开始在Visual Studio Premium中使用数据库开发者版本，该版本现在简化了我们的数据库配置管理和单元测试。

相关推荐