none
Implicit conversion of char value to varchar cannot be performed because the collation of the value is unresolved..

    Question

  • I got this erorr when trying to create my stored proc,

     

    What do i need to fix, and how do i fix it?!!

    Msg 457, Level 16, State 1, Procedure PROC_DAILY_ACTIVITY, Line 13

    Implicit conversion of char value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

     

    Code Block

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

     

    -- =============================================

    -- Author: <Zaccheus,Tenchy>

    -- Create date: <NOVEMEBER,12,2007>

    -- Description: <Reporting stored procedure,DAILY ACTIVITY,>

    -- =============================================

    CREATE PROCEDURE [dbo].[PROC_DAILY_ACTIVITY]

    (@Region_Key int=null)

    AS

    BEGIN

    SELECT

    Region_Key,

    Null as Customer_Code,

    Non_Customer_Activities.Question_code,

    Non_Customer_Activities.Description,

    Region AS Region,

    Name AS Territory_Name,

    Non_Customer_Activities.Que_Desc AS Store_Name,

    Non_Customer_Activities.Logged_Time AS TheDate,

    Non_Customer_Activities.response AS Response,

    Null as is_Visit_Fg

    FROM [FSSRC].[dbo].Qry_Sales_Group Sales_Group

    INNER JOIN

    (Select QH.[question_code]

    ,Question_Header.Description

    ,CONVERT(datetime,DATEADD(day, (qh.cycle_day-1), p.start_date),6) Logged_Time

    ,SUBSTRING([entity_code],1,5) SR_Code

    ,[response]

    ,Territory_Code SR_Territory_Code

    ,'Not Customer Related' Que_Desc

    From question_history QH

    INNER JOIN Period P

    ON p.period_code = qh.period_code

    INNER JOIN [RC_DWDB_INSTANCE_1].[dbo].[Tbl_Territory_In_Sales_Responsible] as Territory_In_Sales_Responsible

    ON Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code=SUBSTRING([entity_code],1,5) COLLATE Latin1_General_CI_AS

    INNER JOIN dbo.questions Question_Header

    ON Question_Header.question_code = QH.question_code

    WHERE [entity_code] like '%.USER%'

    AND Question_Header.Question_Code IN('AME01','ASE01','ACO01','ALU01','AOS01','APH01','ATR01','ATE01','ACR06','ACR05','ACR02','ACR03','ACR08','ACR07')

    AND CONVERT(datetime,DATEADD(day, (qh.cycle_day-1), p.start_date),6) = '11/9/2007'

    ) Non_Customer_Activities

    ON Sales_Group.Code = Non_Customer_Activities.SR_Territory_Code

    UNION ALL

    SELECT

    Customer_Activities.Customer_Code,

    NULL,

    NULL,

    Region AS Region,

    Name AS Territory_Name,

    Customer_Activities.Customer_Name AS Store_Name,

    Customer_Activities.Logged_Time AS TheDate,

    NULL AS Response,

    is_Visit_Fg

    FROM [FSSRC].[dbo].Qry_Sales_Group Sales_Group

    INNER JOIN

    (Select distinct time_log Logged_Time

    ,[entity_code] Customer_Code

    ,[name] Customer_Name

    ,Territory_Code Cust_Territory_Code

    ,MAX(is_Visit_Fg) Is_Visit_Fg

    From question_history QH

    INNER JOIN Period P

    ON p.period_code = qh.period_code

    INNER JOIN dbo.questions Question_Header

    ON Question_Header.question_code = QH.question_code

    INNER JOIN [FSSRC].[dbo].[customer]

    ON Entity_Code = [customer_code]

    INNER JOIN [FSSRC].[dbo].[visit] V

    ON V.[customer_code] = QH.[entity_code]

    AND V.[period_code] = QH.[period_code]

    AND V.[cycle_day] = QH.[cycle_day]

    INNER JOIN [RC_DWDB_INSTANCE_1].[dbo].[Tbl_Territory_In_Sales_Responsible] as Territory_In_Sales_Responsible

    ON Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code=[sales_person_code] COLLATE Latin1_General_CI_AS

    WHERE [entity_code] NOT like '%.USER%'

    AND Convert(datetime,convert(Varchar,time_log,110)) = '11/9/2007'

    GROUP BY

    time_log

    ,[entity_code]

    ,[name]

    ,Territory_Code

    ) Customer_Activities

    ON Sales_Group.Code = Customer_Activities.Cust_Territory_Code

    WHERE @Region_Key=Region_Key

    order by 4

    END

     

     

    Monday, November 12, 2007 9:50 PM

All replies

  • What's the collation of Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code column,
    is it Latin1_General_CI_AS compatible?

    Could you list your table DDL for trouble shooting?

    Thanks,
    Zuomin
    Tuesday, November 13, 2007 1:35 AM
  • Managed to resolve this but my problem was a bit different.  I have two databases one with a Latin1_General_CI_AI collation and the other with a Latin1_General_CI_AS.   Seems no matter how I cut it, adding the collation Latin1_General_CI_AI statement to the join would not change the nature of this error.

     

    However, as the table was small in one of the databases, I copied the table over to the database where the bigger table was located and this allowed the join to work without a hitch.  Having both tables in the same database gives me close to the condition you created above but I could not recreate your error while the tables were in separate databases.  It could only occur from two separate databases.

     

    Normally adding the collation statement fixes the issue.  It seems strange that it would not work in this case.

     

    What I accomplish below is to get a list of states to act like country names using a case statement, a left join, and a hard-coded value on the joined field that exsts only in TABLE A and not in TABLE B.  Be aware that there are no special characters used in these fields, but that the databases contain table with values in the fields that use the extended character set thus making collation a must when joining tables together.

     

    Example

    Database 1 Table A has a list of countries with abbreviations

    Database 2 Table B has a list of US states with abbreviation but without a country abbr containing the value "US"

     

    TableB.dbo.CountryAbbr=TableA.dbo.CountryAbbr but only where country = "US" in Table A

    This means that there is not actually a field that is left joined.  Nevertheless, the TABLEA will join to TABLEB when the abbr value in TABLEA is "US"

     

    Looks like this:

     

    select A .abbr+','+case when A .abbr='US' then U.[fips] else A .abbr end+','+case when A .abbr='US' then B.fips else A .abbr end +'_'+A .uniqueid

    from FIPS.dbo.countries A 

    left join USFIPS.dbo.usstates B

    on A.abbr ='US'

    where not A.abbr in ('AY','OS','UF')order by A.uniqueid  --do not need, antarctica, undersea features, etc...

     

    If you note above, you would expect the join to say on A.abbr=B.abbr

     

    Table B does not have an ABBR since all data in Table B belongs to the single abbr category in TABLE B where abbr='US'

     

    Again, I'm not certain why the collation error occurs despite the addition of the collation value but I suspect it is due to the way the join is created.

     

    Sample data - not sure how you can recreate the collation values in separate databases unless you create these tables in each database

     

    --use fips--has collation Latin1_General_CI_AI

    create table #countries(country varchar(60),abbr varchar(2))

    insert into #countries(country,abbr)values('SERBIA','RB')

    insert into #countries(country,abbr)values('UNITED STATES','US')

    insert into #countries(country,abbr)values('ANTIGUA AND BARBUDA','AC')

    --use usfips --has collation Latin1_General_CI_AS

    create table #usstates([state] varchar(60),abbr varchar(2))

    insert into #usstates([state],abbr)values('Massachusetts','MA')

    insert into #usstates([state],abbr)values('Texas','TX')

    insert into #usstates([state],abbr)values('California','CA')

    select c.abbr+','+case when c.abbr='US' then U.[abbr] else c.abbr end+','+case when c.abbr='US' then u.abbr else c.abbr end

    from #countries c

    left join #usstates u

    on c.abbr='US'

    where not c.abbr in ('AY','OS','UF')

     

    Results:

    RB,RB,RB
    US,MA,MA
    US,TX,TX
    US,CA,CA
    AC,AC,AC

     

    Or, if collation issue can be recreated:

    Msg 457, Level 16, State 1, Line 1

    Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

     

     

    Sunday, August 03, 2008 5:44 PM