Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2005
    Posts
    71

    Unanswered: Stored procedure and a DB with collate that is CI...

    i have a sp that when i add to a CS database it doesnt give me an error but when i add to a CI database it errors out.
    what can i do to it to make it not error on the CI situtation?

    Code:
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go
    
    
    -- Accounts Payable
    ALTER    PROCEDURE [dbo].[isp_ap_calc_apt_totals]
    @p_comp         char(2),
    @p_vend         char(6),
    @p_asofdate char(8)
    as
    
    if (@p_asofdate <= '00000000') 
    begin
       set @p_asofdate = '99999999'
    end
    
    delete from XAPAPTTOT
    where xapt_comp = @p_comp and xapt_vend = @p_vend and xapt_asof_date = @p_asofdate
    
    insert into XAPAPTTOT
    select apph_comp, apph_vend, apph_type, apph_id, @p_asofdate,
        sum(apph_paymnts),
        sum(apph_discts),
        sum(apph_adjts),
        count(apph_paymnts),
        sum(apph_paymnts)+ sum(apph_discts) + sum(apph_adjts) + 
            (select apt_gross from APTRANF 
                where apt_comp = @p_comp and apt_vend = @p_vend 
                    and apt_type = apph_type and apt_id = apph_id),
        0,
        max(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0'))
    from APPHISTF
    where apph_comp = @p_comp and apph_vend = @p_vend
        and str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') <= @p_asofdate 
    group by apph_comp, apph_vend, apph_type, apph_id
    
    update XAPAPTTOT
    set xapt_last_payck = 
        (select max(apph_payck) from APPHISTF
        where apph_comp = xapt_comp and apph_vend = xapt_vend and apph_type = xapt_type
            and apph_id = xapt_id
            and str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') = xapt_last_paydt )
    where xapt_comp = @p_comp and xapt_vend = @p_vend  and xapt_asof_date = @p_asofdate

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    So.....what is the error?

  3. #3
    Join Date
    Mar 2005
    Posts
    71

    Sorry was suffering from a cranial / rectal inversion...

    The error info was relayed on a second hand bases and was "we got one of those error messages about collate mismatches" (which we have had in the past)
    but after i got online to get the actual error message of "Column name or number mismatch" then i knew where to fix it at.

    sub-question: in regards to the sp, in the past we have had ppl set up the APTRANF with CS and the APPHISTF with CI or vise versa. is there a way to override that issue within the sp. some command like the cast or something?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Yes. You can use the keyword COLLATE to force an operation (=. >, order by, etc.) to use a certain collation, no matter what the original collations of the operands are.
    Code:
    create table test1
    (col1 varchar(10) collate SQL_Latin1_General_CP1_CI_AS)
    
    create table test2
    (col1 varchar(10) collate SQL_Latin1_General_CP1_CS_AS)
    
    insert into test1 values ('hello')
    insert into test1 values ('hi')
    
    insert into test2 values ('hello')
    insert into test2 values ('HI')
    
    select *
    from test1 t1 join
    	 test2 t2 on t1.col1 = t2.col1
    
    select *
    from test1 t1 join
    	 test2 t2 on t1.col1 = t2.col1 COLLATE SQL_Latin1_General_CP850_BIN2
    
    
    select *
    from test1 t1 join
    	 test2 t2 on t1.col1 = t2.col1 COLLATE SQL_Latin1_General_CP1_CI_AS
    order by t2.col1 COLLATE SQL_Latin1_General_CP850_BIN2
    
    drop table test1
    drop table test2

Posting Permissions

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