Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    2

    Unanswered: stored procedure failing

    This stored procedure is failing with an error of
    Cannot resolve collation conflict for equal to operation.
    Code:
    CREATE    PROCEDURE myProcedure 
    @Customer_Name varchar(56)='',@Billing_Platform int=NULL,@Channel varchar(32)=NULL,@Sector varchar(5)=NULL, 
    @Segment_ID varchar(2000)=NULL,@Entity varchar(2000)=NULL,@Account_Number varchar(2000)=NULL,@Sale_ID varchar(2000)=NULL, 
    @Invoice_Debt decimal(17)=NULL,@Invoice_Days int=NULL,@Account_Debt decimal(17)=NULL 
    AS 
    
    SET NOCOUNT ON; 
    
    declare @substr varchar(50),@multAccount bit, @Pos int 
    set @substr = @Customer_Name + '%' 
    set @Pos = 0 
    set @multAccount=0 
    
    If Len(@Account_Number) > 0 --comma seperated list of accounts 
    Begin 
       set @multAccount = 1  --Flag to store the fact that there are Account selections 
    End 
    
    CREATE TABLE #TempAccount 
       ( 
          AccountNo varchar(20) COLLATE Latin1_General_CI_AS NOT NULL 
       ) 
    
    If @multAccount=1--strip out the segments from the comma seperated input string into a temporary table 
    Begin 
    
    declare @AccountNo varchar(20) 
    set @Account_Number = Ltrim(Rtrim(@Account_Number))+ ',' 
    set @Pos = Charindex(',', @Account_Number, 1) 
    
       If Replace(@Account_Number, ',', '') <> '' 
       Begin 
          While @Pos > 0 
          Begin 
             set @AccountNo = Ltrim(Rtrim(Left(@Account_Number, @Pos - 1))) 
             If @AccountNo <> '' 
             Begin 
                INSERT INTO #TempAccount (AccountNo) VALUES (CAST(@AccountNo As varchar(20))) 
    
             End 
             set @Account_Number = Right(@Account_Number, Len(@Account_Number) - @Pos) 
             set @Pos = Charindex(',', @Account_Number, 1) 
    
          End 
       End 
    End 
    
    SELECT ENTITY_NM, ENTITY_ID, SUM(RANGE01) AS RANGE01, SUM(RANGE02) AS RANGE02, SUM(RANGE03) AS RANGE03, SUM(RANGE04) AS RANGE04, SUM(RANGE05) AS RANGE05, SUM(RANGE06) AS RANGE06, SUM(DEBT90) AS DEBT90 ,MIN(MINDEBT) AS MINDEBT ,MAX(MAXDEBT) AS MAXDEBT ,MIN(MINOVER) AS MINOVER ,MAX(MAXOVER) AS MAXOVER ,SUM(ACCDEBT) AS ACCDEBT 
    FROM myTable 
    WHERE ENTITY_NM like @substr 
    AND SYS_ID = COALESCE(@Billing_Platform,SYS_ID) 
    AND CHNL_ID = COALESCE(@Channel,CHNL_ID) 
    AND SECT_ID = COALESCE(@Sector,SECT_ID) 
    AND ENTITY_DEBT >= COALESCE(@Invoice_Debt,ENTITY_DEBT) 
    AND MAXOVER >= COALESCE(@Invoice_Days,MAXOVER) 
    AND ACCDEBT >= COALESCE(@Account_Debt,ACCDEBT) 
    AND ((@multAccount=1 AND (ACC_NO IN (select AccountNo from #TempAccount))) OR (@multAccount=0 AND ACC_NO Like '%')) 
    GROUP BY ENTITY_ID, ENTITY_NM 
    ORDER BY DEBT90 DESC 
    GO
    Can anyone tell me what could be wrong?
    This has worked before and it appears that a server setting may have changed (although I don't know what).

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I would say take out the "COLLATE Latin1_General_CI_AS" and see how it runs then. It could be that Latin1_General_CI_AS is not the default collation of the server.

  3. #3
    Join Date
    Nov 2004
    Posts
    2
    I have already tried that. That was initially the problem when the database got moved from one machine to another. Now it still fails with the same error with or without the collation.

  4. #4
    Join Date
    Feb 2004
    Posts
    88
    I've had this...although the reasons may not be the same in your case.

    My application requires the server to be case-sensitive (which is not the default).

    Sometimes I ask for a new copy of my database, and the DBA's may create it on a default server (case insensitive).

    whatever method they use to copy over the schema etc from one server to another preserves the collations from the source server (including case -sensitivity). tempdb on the new server retains the default collation however...

    when I try and join from a table in my database to a table in tempdb.... I get the same error message you have reported.

    HTH,

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •