February 5, 2013 at 7:21 am
I'm getting this error with the following query.
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CS_AS" in the UNION operation.
select a.UnitNumber, Name as 'PatientName', ResultRW, convert(datetime, convert(varchar, ResultDateTime, 101),101) as 'ResultDate'
from [BRO-DR1].livedb.dbo.vINR a
left join (select distinct b.hmrn, a.AnswerWithoutUnitsNumeric, convert(datetime, convert(varchar, ResultDTTM, 101),101) as 'ResultDate'
from [SH-SQL01].DataWarehouse.dbo.xFerResults a
inner join [SH-SQL01].DataWarehouse.dbo.IDX_Patmap b on
a.IDXMRN COLLATE DATABASE_DEFAULT=b.mrn COLLATE DATABASE_DEFAULT
where ResultItemName='INR'
and ResultDTTM>=getdate()-4) b on
a.UnitNumber COLLATE DATABASE_DEFAULT=b.hmrn COLLATE DATABASE_DEFAULT
and convert(datetime, convert(varchar, ResultDateTime, 101),101)
=convert(datetime, convert(varchar, ResultDate, 101),101)
where a.ResultDateTime>= getdate()-4
and b.hmrn is null
and b.ResultDate is null
union
select distinct b.hmrn as 'UnitNumber', b.patlastname + ',' + b.patfirstname as 'PatientName', a.AnswerWithoutUnitsNumeric as 'Results',
convert(datetime, convert(varchar, ResultDTTM, 101),101) as 'ResultDate'
from [SH-SQL01].DataWarehouse.dbo.xFerResults a
inner join [SH-SQL01].DataWarehouse.dbo.IDX_Patmap b on
a.IDXMRN COLLATE DATABASE_DEFAULT=b.mrn COLLATE DATABASE_DEFAULT
left join (select AccountNumber, UnitNumber, Name, ResultRW, convert(datetime, convert(varchar, ResultDateTime, 101),101) as 'ResultDate'
from [BRO-DR1].livedb.dbo.vINR
where ResultDateTime>= getdate()-4) c on
b.hmrn COLLATE DATABASE_DEFAULT=c.UnitNumber COLLATE DATABASE_DEFAULT
and convert(datetime, convert(varchar, ResultDTTM, 101),101)
=convert(datetime, convert(varchar, c.ResultDate, 101),101)
where a.ResultDTTM>= getdate()-4
and a.ResultItemName='INR'
and c.UnitNumber is null
and c.ResultDate is null
February 5, 2013 at 7:51 am
You can read quite a bit about collation in BOL but basically your result sets from the two queries have at least one column with a different collation. In this particular case "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CS_AS". If you look near the end you will see that one of them is CI and the other is CS (case insensitive and case sensitive). Based on your queries it is probably that one of the databases you are referencing has a case sensitive collation and another doesn't.
My suggestion is to dump each of your queries (parts of the union) into separate tables, then run an sp_help on each one to get the collation of each of our columns. Or you can create a view temporarily from each of the queries and get the same effect.
so
select TOP 0 a.UnitNumber, Name as 'PatientName', ResultRW, convert(datetime, convert(varchar, ResultDateTime, 101),101) as 'ResultDate'
from [BRO-DR1].livedb.dbo.vINR a INTO UnionTable1
left join (select distinct b.hmrn, a.AnswerWithoutUnitsNumeric, convert(datetime, convert(varchar, ResultDTTM, 101),101) as 'ResultDate'
from [SH-SQL01].DataWarehouse.dbo.xFerResults a
inner join [SH-SQL01].DataWarehouse.dbo.IDX_Patmap b on
a.IDXMRN COLLATE DATABASE_DEFAULT=b.mrn COLLATE DATABASE_DEFAULT
where ResultItemName='INR'
and ResultDTTM>=getdate()-4) b on
a.UnitNumber COLLATE DATABASE_DEFAULT=b.hmrn COLLATE DATABASE_DEFAULT
and convert(datetime, convert(varchar, ResultDateTime, 101),101)
=convert(datetime, convert(varchar, ResultDate, 101),101)
where a.ResultDateTime>= getdate()-4
and b.hmrn is null
and b.ResultDate is null
-- union
select TOP 0 b.hmrn as 'UnitNumber', b.patlastname + ',' + b.patfirstname as 'PatientName', a.AnswerWithoutUnitsNumeric as 'Results',
convert(datetime, convert(varchar, ResultDTTM, 101),101) as 'ResultDate' INTO UnionTable2
from [SH-SQL01].DataWarehouse.dbo.xFerResults a
inner join [SH-SQL01].DataWarehouse.dbo.IDX_Patmap b on
a.IDXMRN COLLATE DATABASE_DEFAULT=b.mrn COLLATE DATABASE_DEFAULT
left join (select AccountNumber, UnitNumber, Name, ResultRW, convert(datetime, convert(varchar, ResultDateTime, 101),101) as 'ResultDate'
from [BRO-DR1].livedb.dbo.vINR
where ResultDateTime>= getdate()-4) c on
b.hmrn COLLATE DATABASE_DEFAULT=c.UnitNumber COLLATE DATABASE_DEFAULT
and convert(datetime, convert(varchar, ResultDTTM, 101),101)
=convert(datetime, convert(varchar, c.ResultDate, 101),101)
where a.ResultDTTM>= getdate()-4
and a.ResultItemName='INR'
and c.UnitNumber is null
and c.ResultDate is null
Then
EXEC sp_help UnionTable1
EXEC sp_help UnionTable2
Once you know which column(s) have the different collation then you can use the COLLATE keyword to force them to be one way or the other.
Simple example would be
SELECT name COLLATE SQL_Latin1_General_CP1_CS_AS
FROM sys.objects
UNION
SELECT name COLLATE SQL_Latin1_General_CP1_CS_AS
FROM sys.databases
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 5, 2013 at 8:03 am
Perfect! I admire you guys that know this stuff and are willing to share.
February 5, 2013 at 8:17 am
It worked on my test server but, not on the one I need.
exec sp_help xxDR1 --top query
Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation
UnitNumbervarcharno30 yesnoyesSQL_Latin1_General_CP1_CS_AS
PatientNamevarcharno50 yesnoyesSQL_Latin1_General_CP1_CS_AS
ResultRWvarcharno75 yesnoyesSQL_Latin1_General_CP1_CS_AS
ResultDatedatetimeno8 yes(n/a)(n/a)NULL
exec sp_help xxSH1 --bottom query
Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation
UnitNumbervarcharno50 yesnoyesSQL_Latin1_General_CP1_CI_AS
PatientNamevarcharno101 yesnoyesSQL_Latin1_General_CP1_CS_AS
Resultsvarcharno255 yesnoyesSQL_Latin1_General_CP1_CS_AS
ResultDatedatetimeno8 yes(n/a)(n/a)NULL
select a.UnitNumber, Name as 'PatientName', ResultRW, convert(datetime, convert(varchar, ResultDateTime, 101),101) as 'ResultDate'
from [BRO-DR1].livedb.dbo.vINR a
left join (select distinct b.hmrn, a.AnswerWithoutUnitsNumeric, convert(datetime, convert(varchar, ResultDTTM, 101),101) as 'ResultDate'
from [SH-SQL01].DataWarehouse.dbo.xFerResults a
inner join [SH-SQL01].DataWarehouse.dbo.IDX_Patmap b on
a.IDXMRN COLLATE DATABASE_DEFAULT=b.mrn COLLATE DATABASE_DEFAULT
where ResultItemName='INR'
and ResultDTTM>=getdate()-4) b on
a.UnitNumber COLLATE DATABASE_DEFAULT=b.hmrn COLLATE DATABASE_DEFAULT
and convert(datetime, convert(varchar, ResultDateTime, 101),101)
=convert(datetime, convert(varchar, ResultDate, 101),101)
where a.ResultDateTime>= getdate()-4
and b.hmrn is null
and b.ResultDate is null
union
select distinct b.hmrn collate SQL_Latin1_General_CP1_CS_AS as 'UnitNumber', b.patlastname + ',' + b.patfirstname as 'PatientName',
a.AnswerWithoutUnitsNumeric as 'Results',
convert(datetime, convert(varchar, ResultDTTM, 101),101) as 'ResultDate'
from [SH-SQL01].DataWarehouse.dbo.xFerResults a
inner join [SH-SQL01].DataWarehouse.dbo.IDX_Patmap b on
a.IDXMRN COLLATE DATABASE_DEFAULT=b.mrn COLLATE DATABASE_DEFAULT
left join (select AccountNumber, UnitNumber, Name, ResultRW, convert(datetime, convert(varchar, ResultDateTime, 101),101) as 'ResultDate'
from [BRO-DR1].livedb.dbo.vINR
where ResultDateTime>= getdate()-4) c on
b.hmrn COLLATE DATABASE_DEFAULT=c.UnitNumber COLLATE DATABASE_DEFAULT
and convert(datetime, convert(varchar, ResultDTTM, 101),101)
=convert(datetime, convert(varchar, c.ResultDate, 101),101)
where a.ResultDTTM>= getdate()-4
and a.ResultItemName='INR'
and c.UnitNumber is null
and c.ResultDate is null
February 5, 2013 at 8:25 am
I would make sure that your production collations are the same as your test ones. IE run the same test, dumping the top 0 into a table and running sp_help, on production. I've seen more than one case where the test system and the production system are using different collations. If that doesn't work then worst case set the collation on both parts of the union to the same thing. You can even go so far as to put the COLLATE keyword on each of the character columns on both parts of the union.
It would probably be best though to check your servers and databases in test and production and make sure the collations match.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 5, 2013 at 8:50 am
The code that I just posted is done on the production server. I made the point about both because one worked and the other didn't, even though I followed the same steps.
Where there are only four columns on each, I think I'll use your suggestion and match the collation on each column.
February 5, 2013 at 8:55 am
Just remember that COLLATE only works on character columns.
I would still double check that your servers & databases have the same collation across dev/test/prod. Otherwise you are libel to run into other problems in the future.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 5, 2013 at 9:02 am
Thanx.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply