Implicit conversion of char value to varchar cannot be performed because the collation of the value is unresolved..
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
-- Author: <Zaccheus,Tenchy>
-- Create date: <NOVEMEBER,12,2007>
-- Description: <Reporting stored procedure,DAILY ACTIVITY,>
Non_Customer_Activities.Que_Desc AS Store_Name,
Non_Customer_Activities.Logged_Time AS TheDate,
Non_Customer_Activities.response AS Response,
INNERJOIN (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
Customer_Activities.Customer_Name AS Store_Name,
Customer_Activities.Logged_Time AS TheDate,
INNERJOIN (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
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.
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:
selectA .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
leftjoin USFIPS.dbo.usstates B
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
createtable #countries(country varchar(60),abbr varchar(2))
insertinto #countries(country,abbr)values('UNITED STATES','US')
insertinto #countries(country,abbr)values('ANTIGUA AND BARBUDA','AC')
--use usfips --has collation Latin1_General_CI_AS
createtable #usstates([state] varchar(60),abbr varchar(2))
selectc.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
leftjoin #usstates u
wherenot c.abbr in ('AY','OS','UF')
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.