[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation

Posted on 2009-12-21
15
Medium Priority
?
639 Views
Last Modified: 2012-05-08
Hi ,

I get this problem in the following code

The default collact of Database and table is : SQL_Latin1_General_CP1_CI_AS

What is the Latin1_General_CI_AS ?

This procedure is called from another procedure and the data of #temprpt037Monthqi is generate from caller.

If I craete the table with name [temprpt037Monthqi] , it works well, and if I create the table with name #temprpt037Monthqi so that the procedure can be called concurrently . I get this error.

ALTER PROCEDURE [dbo].[Report_RPT037_grpKey_Indicator_Performance]
@QcatName nvarchar(50),@QReportType nvarchar(50),@StartDate Datetime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

declare @NumberOfQuestion int,@ChartType char

select @NumberOfQuestion=count(*) from RegionalReportQI 
	where ReportType=@QReportType and QuestionCategory=@QcatName

select @ChartType=ChartType from RegionalReportQI 
	where ReportType=@QReportType and QuestionCategory=@QcatName
-- Insert statements for procedure here
	
--Get Beddays , Sum of answer for each facility
declare @FacilityValues table
	(
		QuestionCategory varchar(50),
		CareLevel varchar(100), 
		Region varchar(100),
		MemberName varchar(100),
		SumAnswer int,
		BedDays	int,
		Beddays000 float,
		CountOfAnswer int,
		MonthReported float,
		UCAMeans float,
		Expected int,
		ExpectedReal float,
		Lower2s float,
		Lower3s float,
		Upper2s float,
		Upper3s float,
		Flag varchar(20),
		ReportStandard int, --int of Month Reported
		fDementia nvarchar(50),
		avgBeds int,	--avg beds for member
		avgAnswer int	--avg answer for member work with avgbeds to get the new expected value
	);

insert into @FacilityValues (QuestionCategory,CareLevel,Region,MemberName,SumAnswer,BedDays,
CountOfAnswer,MonthReported,fDementia,avgBeds,avgAnswer)
select @QcatName,min([membertype]) as CareLevel,BGName,[Member Name] as MemberName,sum(answer) SumAnswer ,
sum((datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year([monthdate]) as varchar)+'-'+cast(month([monthdate]) as varchar)+'-01' as datetime)))))*beds) AS BedDays,
count(*) As CountofAnswer , cast(count(*) as Decimal)/ @NumberOfQuestion AS [MonthReported],
min(fDementia),
avg(beds),avg(answer)
from #temprpt037Monthqi
where  EQBID in (select qid from regionalreportqi where ReportType =@QReportType and QuestionCategory=@QcatName)
and monthdate>=@StartDate
group by BGName,[member name]  --Line 69
order by BGName,[member name]

--Get the Benchmark Value value  sum(expected value for each question category
if @ChartType='U'
	update TA set TA.UCAMeans=
	(
		select sum(ExpectURate) from #temprpt037BenchMeanQI TB where TB.fmembertype=TA.CareLevel
			and TB.fDementia =TA.fDementia and  --Line 77
			TB.QBID in (select qid from regionalreportqi where ReportType=@QReportType and QuestionCategory=@QcatName)
	)
	From @FacilityValues AS TA

Open in new window

0
Comment
Question by:GordonLiq
  • 8
  • 6
15 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26099984
not sure which line .. change your syntax to this
Col1  = col2 collate SQL_Latin1_General_CP1_CI_AS
0
 

Author Comment

by:GordonLiq
ID: 26100203
I changed as your guide, but it does not help. it still get this error on line 69 and 77.
insert into @FacilityValues (QuestionCategory,CareLevel,Region,MemberName,SumAnswer,BedDays,
CountOfAnswer,MonthReported,fDementia,avgBeds,avgAnswer)
select @QcatName,min([membertype]) as CareLevel,BGName,[Member Name] as MemberName,sum(answer) SumAnswer ,
sum((datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year([monthdate]) as varchar)+'-'+cast(month([monthdate]) as varchar)+'-01' as datetime)))))*beds) AS BedDays,
count(*) As CountofAnswer , cast(count(*) as Decimal)/ @NumberOfQuestion AS [MonthReported],
min(fDementia),
avg(beds),avg(answer)
from #temprpt037Monthqi
where  EQBID in (select qid from regionalreportqi where ReportType=@QReportType collate SQL_Latin1_General_CP1_CI_AS
and QuestionCategory=@QcatName collate SQL_Latin1_General_CP1_CI_AS)
and monthdate>=@StartDate
group by BGName,[member name]
order by BGName,[member name]

--Get the Benchmark Value value  sum(expected value for each question category
if @ChartType='U'
	update TA set TA.UCAMeans=
	(
		select sum(ExpectURate) from #temprpt037BenchMeanQI TB where TB.fmembertype=TA.CareLevel
			and TB.fDementia collate SQL_Latin1_General_CP1_CI_AS =TA.fDementia collate SQL_Latin1_General_CP1_CI_AS and 
			TB.QBID in (select qid from regionalreportqi where ReportType=@QReportType and QuestionCategory=@QcatName)
	)
	From @FacilityValues AS TA

Open in new window

0
 
LVL 43

Expert Comment

by:pcelba
ID: 26100381
Latin1_General_CI_AS is Windows collation sequence which was probably assigned to your TempDB database and #temprpt037Monthqi  inherits TempDB collation.

If you would like to create temp tables with current database collation you have to add COLLATE database_default clause to each character (char, varchar, text) column definition.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

Author Comment

by:GordonLiq
ID: 26100445
But I create #temptable by

select * into #temptable from realtable

in this way , I cannot change the collate when create.  

0
 
LVL 43

Expert Comment

by:pcelba
ID: 26100693
This way it inherits collation from TempDB. You have to list all columns and add default collation to character columns...

BTW, your change in SP:
QuestionCategory=@QcatName collate SQL_Latin1_General_CP1_CI_AS
could not help because @QcatName already has collation SQL_Latin1_General_CP1_CI_AS

Correct expressions are:
QuestionCategory  collate SQL_Latin1_General_CP1_CI_AS = @QcatName
or
QuestionCategory=@QcatName collate Latin1_General_CI_AS
0
 

Author Comment

by:GordonLiq
ID: 26100953
I changed again but it still does not work.

select qid from regionalreportqi where ReportType (this comes from real table ) =@QReportType  (this is procedure parameter)
and QuestionCategory (this comes from real table ) =@QcatName (this is procedure parameter))
and monthdate>=@StartDate
insert into @FacilityValues (QuestionCategory,CareLevel,Region,MemberName,SumAnswer,BedDays,
CountOfAnswer,MonthReported,fDementia,avgBeds,avgAnswer)
select @QcatName,min([membertype]) as CareLevel,BGName,[Member Name] as MemberName,sum(answer) SumAnswer ,
sum((datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year([monthdate]) as varchar)+'-'+cast(month([monthdate]) as varchar)+'-01' as datetime)))))*beds) AS BedDays,
count(*) As CountofAnswer , cast(count(*) as Decimal)/ @NumberOfQuestion AS [MonthReported],
min(fDementia),
avg(beds),avg(answer)
from #temprpt037Monthqi
where  EQBID in (select qid from regionalreportqi where ReportType =@QReportType 
and QuestionCategory =@QcatName )
and monthdate>=@StartDate
group by BGName collate SQL_Latin1_General_CP1_CI_AS,[member name] collate SQL_Latin1_General_CP1_CI_AS
order by BGName collate SQL_Latin1_General_CP1_CI_AS,[member name] collate SQL_Latin1_General_CP1_CI_AS

Open in new window

0
 
LVL 43

Expert Comment

by:pcelba
ID: 26101139
What is the data type of #temprpt037Monthqi.EQBID ?
0
 
LVL 43

Expert Comment

by:pcelba
ID: 26101168
BTW, isn't the error  "Column '#temprpt037Monthqi.BGName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." now?
insert into @FacilityValues (QuestionCategory,CareLevel,Region,MemberName,SumAnswer,BedDays, 
CountOfAnswer,MonthReported,fDementia,avgBeds,avgAnswer) 
select @QcatName,min([membertype]) as CareLevel,BGName collate SQL_Latin1_General_CP1_CI_AS,[Member Name] as MemberName collate SQL_Latin1_General_CP1_CI_AS, sum(answer) SumAnswer , 
sum((datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year([monthdate]) as varchar)+'-'+cast(month([monthdate]) as varchar)+'-01' as datetime)))))*beds) AS BedDays, 
count(*) As CountofAnswer , cast(count(*) as Decimal)/ @NumberOfQuestion AS [MonthReported], 
min(fDementia), 
avg(beds),avg(answer) 
from #temprpt037Monthqi 
where  EQBID in (select qid from regionalreportqi where ReportType =@QReportType  
and QuestionCategory =@QcatName ) 
and monthdate>=@StartDate 
group by BGName collate SQL_Latin1_General_CP1_CI_AS,[member name] collate SQL_Latin1_General_CP1_CI_AS 
order by BGName collate SQL_Latin1_General_CP1_CI_AS,[member name] collate SQL_Latin1_General_CP1_CI_AS

Open in new window

0
 

Author Comment

by:GordonLiq
ID: 26101199
What is the data type of #temprpt037Monthqi.EQBID ?
uniqueidentifier

BTW, isn't the error  "Column '#temprpt037Monthqi.BGName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." now?
No,   if I change ti from temprpt037Monthqi  and change the create statement to
select * into temprpt037Monthqi  from tableorg.
it works well.
0
 
LVL 43

Expert Comment

by:pcelba
ID: 26101246
How many columns do you have in #temprpt037Monthqi ? Is it really problematic to create this temp table with correct collations?
0
 

Author Comment

by:GordonLiq
ID: 26101291
this is the statement to craete the temprpt037Monthqi
declare @tqi2 table
(
	mdate datetime,
	fmembernumber int,
	fname varchar(100),
	fid uniqueidentifier,
	qnum int,
	answer int,
	fbeds int,
	qcid uniqueidentifier,
	fmembertype varchar(100),
	aauditName varchar(100),
	qid uniqueidentifier,
	BGName varchar(100),
	fDementia varchar(20),
	qbid uniqueidentifier,
	EQBID uniqueidentifier
)

insert into @tqi2
select mdate, fmembernumber, fname, f.fid,  qnum,  ananswer, fbeds, qcid,fmembertype,al.alauditname,ql.qid,BGName,f.fDementia,qb.qbid,ql.EQBID
from
	answer an
	inner join 
	@flist f
	on anfacilityid=f.fid
	inner join
	(
		@QIQuestionList ql
		inner join	part p	on ql.pid=p.pid
		inner join	audit a	on a.aid=ql.aid
		inner join auditlanguage al on al.alauditid=a.aid
		inner join	month m	on m.mid=ql.mid
		inner join
		(
			question q
			inner join
			(
				questionbank qb
				left join 
				questionbanklanguage qbl
				on qbl.qblquestionbankid=qb.qbid and qbl.qbllanguageid=@userlanguage
			)
			on qb.qbid=q.qquestionbankid
		)
		on ql.qid=q.qid
	)
	on ql.qid=an.anquestionid
	order by mdate, fmembernumber, qnum

update @tqi2 set fbeds=(select top 1 hfbeds from hfacility where hfxid=fid and hfdate<=mdate order by hfdate desc)

select distinct mdate as [Monthdate], fmembernumber as [Member Number],fname as [Member Name], 	
	case 
		when mdate<'2008-12-31' then 'Q08-'+cast(qnum as varchar(10))
		when mdate>'2008-12-31' then 'Q09-'+cast(qnum as varchar(10))
	end as [QI Version],
	fmembertype as MemberType,
	case
		when aauditName like '%quarterly%'  then 'Q'
	else 'M'
	end as [Question Category],
	qnum as [Question Number] , 
	answer as [Answer], fbeds as [Beds] , BGName,aauditname,qbid,fDementia,EQBID
into [#temprpt037Monthqi]
from @tqi2
where mdate>=@StartDate and mdate<=@EndDate
--and aauditName like '%quarterly%'
order by [Question Category],mdate, fname, qnum

Open in new window

0
 
LVL 43

Expert Comment

by:pcelba
ID: 26101298
Looking at some previous code you have collate conversion in more columns, e.g. fDementia...

Are you sure the error is raised on given statement? You've mentioned  error on line 69 and 77 but there is no such line in attached code snippets.
0
 
LVL 43

Accepted Solution

by:
pcelba earned 2000 total points
ID: 26101342
If this does not help then I don't know...
select distinct mdate as [Monthdate], fmembernumber as [Member Number],fname collate database_default as [Member Name],   
        case  
                when mdate<'2008-12-31' then 'Q08-'+cast(qnum as varchar(10)) 
                when mdate>'2008-12-31' then 'Q09-'+cast(qnum as varchar(10)) 
        end collate database_default as [QI Version], 
        fmembertype collate database_default as MemberType, 
        case 
                when aauditName like '%quarterly%'  then 'Q' 
        else 'M' 
        end collate database_default as [Question Category], 
        qnum as [Question Number] ,  
        answer as [Answer], fbeds as [Beds] , 
        BGName collate database_default,
        aauditname collate database_default,
        qbid,
        fDementia collate database_default,
        EQBID 
into [#temprpt037Monthqi] 
from @tqi2 
where mdate>=@StartDate and mdate<=@EndDate 
--and aauditName like '%quarterly%' 
order by [Question Category],mdate, fname, qnum

Open in new window

0
 

Author Closing Comment

by:GordonLiq
ID: 31668721
I change the table from select into #table
to create table #table(....) and it works
0
 
LVL 43

Expert Comment

by:pcelba
ID: 26102923
Yes, this is also possible way.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question