Cannot resolve collation conflict for UNION operation

Hi
I am getting the above error with my SP. Why would I get this error?
Many Thanks
Jaco
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[qfm_RptgetAsbLevelGeog]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[qfm_RptgetAsbLevelGeog]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[qfm_RptgetAsbLevelGeog]
@.EventID varchar(10),
@.Config int
AS
SET NOCOUNT ON
--If we want to add the main event as a row in the sql below (to make it
have a 'from event' equivalent to QFM3),
--then only need to add the @.eventid to the select.
Declare @.parID int
Declare @.GeogID varchar(5)
declare @.sql nvarchar(4000)
set @.sql=' '
if @.Config = 0 or @.Config = 1 or @.Config = 3 or @.Config = 5 or @.Config = 7
or @.Config = 9 or @.Config = 11 or @.Config = 13 or @.Config = 15
--1. find all other details of asb found to the exact same geog (so if found
at site/locn/unit, then exactly this level), including current event.
begin
set @.sql="
select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Found'
as asbestoststaus, es.surveydate, esd.samplenumber,
esd.materialdescription, esda.description + '[' +
ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
ac.description as classification, esd.risk as hazard, esd.remarks,
g.geographyid, g.parentid, g.siteid, g.level, g.region,
ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
(select site + ' ' + location + ' ' + unit + ' ' + partition from geography
where geography.geographyid = es.geographyid) as surveygeog --gja 5.12.04
from geography g, event e, eventsurvey es, eventsurveydetail esd,
asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
where g.geographyid = e.geographyid --gja5.12.04
-- where g.geographyid = es.geographyid --gja5.12.04
and e.eventid = es.currenteventid
and es.eventsurveyid = esd.eventsurveyid
and ac.asbestosclassificationid = esd.classificationid
and esd.analysisid = esda.EventSurveyDetailAnalysisid
and e.geographyid in
(select geographyid from event where eventid = " + rtrim(@.EventID) + ")
-- select geographyid from event where eventid = 38590)
and c.uniqueid =* esd.materialareaunitid "
end
if @.Config = 2 or @.Config = 3 or @.Config = 6 or @.Config = 7 or @.Config = 10
or @.Config = 11 or @.Config = 14 or @.Config = 15
--2. if asb was found at at ie bridge court/2s/test, then include all for
bridge court/2s/anything, but not bridge court/2s/nothing
--ie if found at a room on a floor, report on other rooms on same floor
(with the same parent and level)
--Get all geogs for the same level (ie rooms on the same floor) as the geog
of a specific event.
begin
select @.parID = parentid from geography g, event e where g.geographyid =
e.geographyid and e.eventid = rtrim(@.EventID)
if @.parID is not null
begin
--If we are not at the highest level (site) then we can check for a parent
set @.sql = @.sql + " union
select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Found'
as asbestoststaus, es.surveydate, esd.samplenumber,
esd.materialdescription, esda.description + '[' +
ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
ac.description as classification, esd.risk as hazard, esd.remarks,
g.geographyid, g.parentid, g.siteid, g.level, g.region,
ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
(select site + ' ' + location + ' ' + unit + ' ' + partition from
geography where geography.geographyid = es.geographyid) as surveygeog --gja
5.12.04
from geography g, event e, eventsurvey es, eventsurveydetail esd,
asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
where g.geographyid = e.geographyid --gja5.12.04
-- where g.geographyid = es.geographyid --gja5.12.04
and e.geographyid in
(select g2.geographyid
from geography g2, geography g
where g.geographyid in (select geographyid from event where eventid = " +
rtrim(@.EventID) + ")
and g.parentid = g2.parentid
and g.level = g2.level) and e.eventid = es.currenteventid
and es.eventsurveyid = esd.eventsurveyid
and ac.asbestosclassificationid = esd.classificationid
and esd.analysisid = esda.EventSurveyDetailAnalysisid
and c.uniqueid =* esd.materialareaunitid "
-- print 'first'
-- print 'Config=2'
end
else
begin
set @.sql = @.sql + " union
select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Found'
as asbestoststaus, es.surveydate, esd.samplenumber,
esd.materialdescription, esda.description + '[' +
ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
ac.description as classification, esd.risk as hazard, esd.remarks,
g.geographyid, g.parentid, g.siteid, g.level, g.region,
ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
(select site + ' ' + location + ' ' + unit + ' ' + partition from
geography where geography.geographyid = es.geographyid) as surveygeog --gja
5.12.04
from geography g, event e, eventsurvey es, eventsurveydetail esd,
asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
where g.geographyid = e.geographyid --gja5.12.04
-- where g.geographyid = es.geographyid --gja5.12.04
and e.eventid = es.currenteventid
and es.eventsurveyid = esd.eventsurveyid
and ac.asbestosclassificationid = esd.classificationid
and esd.analysisid = esda.EventSurveyDetailAnalysisid
and g.parentid is null
and g.geographyid = (select geographyid from event where eventid = " +
rtrim(@.EventID) + ")
and c.uniqueid =* esd.materialareaunitid "
end
end
if @.Config = 4 or @.Config = 5 or @.Config = 6 or @.Config = 7 or @.Config = 12
or @.Config = 13 or @.Config = 14 or @.Config = 15
--3. if asb was found at at ie red lion court/3rd/, then include all for red
lion court/3rd/anything below.
--Do we not then include the current event? (as it is obviously not below
the level of geog for the current event)?
--JF says maybe, maybe not, so does.
--Use the eventid to get the geography level
begin
select @.GeogID = GeographyID from event where eventid = rtrim(@.EventID)
set @.sql = @.sql + " union
select eventid ,
site ,
location ,
unit ,
partition ,
asbestoststaus ,
surveydate datetime,
samplenumber ,
materialdescription ,
analysis ,
classification ,
hazard ,
remarks ,
geographyid ,
parentid ,
siteid ,
level ,
region ,
extent
,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail,
AsbestosImagePath,
surveygeog --gja5.12.04
from AsbLowestLevel "+ "(" +rtrim( @.EventID) + "," + @.GeogID +") "
--print 'Config=3'
end
if @.Config = 8 or @.Config = 9 or @.Config = 10 or @.Config = 11 or @.Config =
12 or @.Config = 13 or @.Config = 14 or @.Config = 15
--4. as 3 only cant have anything below 2s (if unit or part then dont
include)
--ie if asb was found at at ie bridge court/2s/test, then include all geogs
at bridge court/2s/nothing.
--Do we not then include the current event? (as it is obviously not below
the level of geog for the current event)?
--JF says maybe, maybe not, so does.
begin
select @.GeogID = GeographyID from event where eventid = rtrim(@.EventID)
set @.sql = @.sql + " union
select eventid ,
site ,
location ,
unit ,
partition ,
asbestoststaus ,
surveydate datetime,
samplenumber ,
materialdescription ,
analysis ,
classification ,
hazard ,
remarks ,
geographyid ,
parentid ,
siteid ,
level ,
region ,
extent
,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail,
AsbestosImagePath,
surveygeog --gja5.12.04
from AsbHighestLevel "+ "(" +rtrim( @.EventID) + "," + @.GeogID +") "
end
if left(@.sql, 7) = ' union'
set @.sql = substring(@.sql, 8, len(@.sql) - 7)
set @.sql = @.sql + " order by 1"
exec sp_executesql @.sql
--end
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOJaco,
The UNION operator will scan the inputs to remove duplicates (comparing all
members of the select list). There should be columns, in the tables involved
,
with different collation. You can use COLLATE apply a collation cast to the
character expression. See this example:
Example:
use northwind
go
create table t1 (
colA varchar(25) collate SQL_Latin1_General_CP1_CI_AS
)
go
create table t2 (
colA varchar(25) collate SQL_Latin1_General_CP1_CS_AS
)
go
insert into t1 values('microsoft')
insert into t1 values('Microsoft')
go
-- this will fail with the error mentioned
select colA from t1
union
select colA from t2
go
select colA from t1
union
select colA collate SQL_Latin1_General_CP1_CI_AS from t2
go
drop table t1, t2
go
AMB
"Jaco" wrote:

> Hi
> I am getting the above error with my SP. Why would I get this error?
> Many Thanks
> Jaco
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[qfm_RptgetAsbLevelGeog]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[qfm_RptgetAsbLevelGeog]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
>
> CREATE PROCEDURE [dbo].[qfm_RptgetAsbLevelGeog]
> @.EventID varchar(10),
> @.Config int
> AS
>
> SET NOCOUNT ON
> --If we want to add the main event as a row in the sql below (to make it
> have a 'from event' equivalent to QFM3),
> --then only need to add the @.eventid to the select.
> Declare @.parID int
> Declare @.GeogID varchar(5)
> declare @.sql nvarchar(4000)
> set @.sql=' '
> if @.Config = 0 or @.Config = 1 or @.Config = 3 or @.Config = 5 or @.Config = 7
> or @.Config = 9 or @.Config = 11 or @.Config = 13 or @.Config = 15
> --1. find all other details of asb found to the exact same geog (so if fou
nd
> at site/locn/unit, then exactly this level), including current event.
> begin
> set @.sql="
> select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Found
'
> as asbestoststaus, es.surveydate, esd.samplenumber,
> esd.materialdescription, esda.description + '[' +
> ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
> ac.description as classification, esd.risk as hazard, esd.remarks,
> g.geographyid, g.parentid, g.siteid, g.level, g.region,
> ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
> ,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
> (select site + ' ' + location + ' ' + unit + ' ' + partition from geograp
hy
> where geography.geographyid = es.geographyid) as surveygeog --gja 5.12.04
> from geography g, event e, eventsurvey es, eventsurveydetail esd,
> asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
> where g.geographyid = e.geographyid --gja5.12.04
> -- where g.geographyid = es.geographyid --gja5.12.04
> and e.eventid = es.currenteventid
> and es.eventsurveyid = esd.eventsurveyid
> and ac.asbestosclassificationid = esd.classificationid
> and esd.analysisid = esda.EventSurveyDetailAnalysisid
> and e.geographyid in
> (select geographyid from event where eventid = " + rtrim(@.EventID) + ")
> -- select geographyid from event where eventid = 38590)
> and c.uniqueid =* esd.materialareaunitid "
>
> end
>
> if @.Config = 2 or @.Config = 3 or @.Config = 6 or @.Config = 7 or @.Config =
10
> or @.Config = 11 or @.Config = 14 or @.Config = 15
> --2. if asb was found at at ie bridge court/2s/test, then include all for
> bridge court/2s/anything, but not bridge court/2s/nothing
> --ie if found at a room on a floor, report on other rooms on same floor
> (with the same parent and level)
> --Get all geogs for the same level (ie rooms on the same floor) as the geo
g
> of a specific event.
> begin
> select @.parID = parentid from geography g, event e where g.geographyid =
> e.geographyid and e.eventid = rtrim(@.EventID)
> if @.parID is not null
> begin
> --If we are not at the highest level (site) then we can check for a paren
t
> set @.sql = @.sql + " union
> select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Foun
d'
> as asbestoststaus, es.surveydate, esd.samplenumber,
> esd.materialdescription, esda.description + '[' +
> ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
> ac.description as classification, esd.risk as hazard, esd.remarks,
> g.geographyid, g.parentid, g.siteid, g.level, g.region,
> ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
> ,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
> (select site + ' ' + location + ' ' + unit + ' ' + partition from
> geography where geography.geographyid = es.geographyid) as surveygeog --gj
a
> 5.12.04
> from geography g, event e, eventsurvey es, eventsurveydetail esd,
> asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
> where g.geographyid = e.geographyid --gja5.12.04
> -- where g.geographyid = es.geographyid --gja5.12.04
> and e.geographyid in
> (select g2.geographyid
> from geography g2, geography g
> where g.geographyid in (select geographyid from event where eventid = "
+
> rtrim(@.EventID) + ")
> and g.parentid = g2.parentid
> and g.level = g2.level) and e.eventid = es.currenteventid
> and es.eventsurveyid = esd.eventsurveyid
> and ac.asbestosclassificationid = esd.classificationid
> and esd.analysisid = esda.EventSurveyDetailAnalysisid
> and c.uniqueid =* esd.materialareaunitid "
>
> -- print 'first'
> -- print 'Config=2'
> end
> else
> begin
> set @.sql = @.sql + " union
> select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Foun
d'
> as asbestoststaus, es.surveydate, esd.samplenumber,
> esd.materialdescription, esda.description + '[' +
> ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
> ac.description as classification, esd.risk as hazard, esd.remarks,
> g.geographyid, g.parentid, g.siteid, g.level, g.region,
> ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
> ,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
> (select site + ' ' + location + ' ' + unit + ' ' + partition from
> geography where geography.geographyid = es.geographyid) as surveygeog --gj
a
> 5.12.04
> from geography g, event e, eventsurvey es, eventsurveydetail esd,
> asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
> where g.geographyid = e.geographyid --gja5.12.04
> -- where g.geographyid = es.geographyid --gja5.12.04
> and e.eventid = es.currenteventid
> and es.eventsurveyid = esd.eventsurveyid
> and ac.asbestosclassificationid = esd.classificationid
> and esd.analysisid = esda.EventSurveyDetailAnalysisid
> and g.parentid is null
> and g.geographyid = (select geographyid from event where eventid = " +
> rtrim(@.EventID) + ")
> and c.uniqueid =* esd.materialareaunitid "
>
> end
> end
>
> if @.Config = 4 or @.Config = 5 or @.Config = 6 or @.Config = 7 or @.Config =
12
> or @.Config = 13 or @.Config = 14 or @.Config = 15
> --3. if asb was found at at ie red lion court/3rd/, then include all for r
ed
> lion court/3rd/anything below.
> --Do we not then include the current event? (as it is obviously not below
> the level of geog for the current event)?
> --JF says maybe, maybe not, so does.
> --Use the eventid to get the geography level
> begin
> select @.GeogID = GeographyID from event where eventid = rtrim(@.EventID)
> set @.sql = @.sql + " union
> select eventid ,
> site ,
> location ,
> unit ,
> partition ,
> asbestoststaus ,
> surveydate datetime,
> samplenumber ,
> materialdescription ,
> analysis ,
> classification ,
> hazard ,
> remarks ,
> geographyid ,
> parentid ,
> siteid ,
> level ,
> region ,
> extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail,
> AsbestosImagePath,
> surveygeog --gja5.12.04
> from AsbLowestLevel "+ "(" +rtrim( @.EventID) + "," + @.GeogID +") "
>
> --print 'Config=3'
> end
>
> if @.Config = 8 or @.Config = 9 or @.Config = 10 or @.Config = 11 or @.Config
=
> 12 or @.Config = 13 or @.Config = 14 or @.Config = 15
> --4. as 3 only cant have anything below 2s (if unit or part then dont
> include)
> --ie if asb was found at at ie bridge court/2s/test, then include all geo
gs
> at bridge court/2s/nothing.
> --Do we not then include the current event? (as it is obviously not below
> the level of geog for the current event)?
> --JF says maybe, maybe not, so does.
> begin
> select @.GeogID = GeographyID from event where eventid = rtrim(@.EventID)
> set @.sql = @.sql + " union
> select eventid ,
> site ,
> location ,
> unit ,
> partition ,
> asbestoststaus ,
> surveydate datetime,
> samplenumber ,
> materialdescription ,
> analysis ,
> classification ,
> hazard ,
> remarks ,
> geographyid ,
> parentid ,
> siteid ,
> level ,
> region ,
> extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail,
> AsbestosImagePath,
> surveygeog --gja5.12.04
>
> from AsbHighestLevel "+ "(" +rtrim( @.EventID) + "," + @.GeogID +") "
>
> end
>
> if left(@.sql, 7) = ' union'
> set @.sql = substring(@.sql, 8, len(@.sql) - 7)
> set @.sql = @.sql + " order by 1"
>
> exec sp_executesql @.sql
>
>
> --end
> SET NOCOUNT OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>|||Hi
Without DDL for the tables it is hard to say where the conflict occurs, but
the way to debug it would be to use profile to get the final SQL statement
and then you can find out which part of the union is causing the conflict an
d
use the COLLATE option to force the collation.
John
"Jaco" wrote:

> Hi
> I am getting the above error with my SP. Why would I get this error?
> Many Thanks
> Jaco
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[qfm_RptgetAsbLevelGeog]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[qfm_RptgetAsbLevelGeog]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
>
> CREATE PROCEDURE [dbo].[qfm_RptgetAsbLevelGeog]
> @.EventID varchar(10),
> @.Config int
> AS
>
> SET NOCOUNT ON
> --If we want to add the main event as a row in the sql below (to make it
> have a 'from event' equivalent to QFM3),
> --then only need to add the @.eventid to the select.
> Declare @.parID int
> Declare @.GeogID varchar(5)
> declare @.sql nvarchar(4000)
> set @.sql=' '
> if @.Config = 0 or @.Config = 1 or @.Config = 3 or @.Config = 5 or @.Config = 7
> or @.Config = 9 or @.Config = 11 or @.Config = 13 or @.Config = 15
> --1. find all other details of asb found to the exact same geog (so if fou
nd
> at site/locn/unit, then exactly this level), including current event.
> begin
> set @.sql="
> select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Found
'
> as asbestoststaus, es.surveydate, esd.samplenumber,
> esd.materialdescription, esda.description + '[' +
> ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
> ac.description as classification, esd.risk as hazard, esd.remarks,
> g.geographyid, g.parentid, g.siteid, g.level, g.region,
> ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
> ,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
> (select site + ' ' + location + ' ' + unit + ' ' + partition from geograp
hy
> where geography.geographyid = es.geographyid) as surveygeog --gja 5.12.04
> from geography g, event e, eventsurvey es, eventsurveydetail esd,
> asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
> where g.geographyid = e.geographyid --gja5.12.04
> -- where g.geographyid = es.geographyid --gja5.12.04
> and e.eventid = es.currenteventid
> and es.eventsurveyid = esd.eventsurveyid
> and ac.asbestosclassificationid = esd.classificationid
> and esd.analysisid = esda.EventSurveyDetailAnalysisid
> and e.geographyid in
> (select geographyid from event where eventid = " + rtrim(@.EventID) + ")
> -- select geographyid from event where eventid = 38590)
> and c.uniqueid =* esd.materialareaunitid "
>
> end
>
> if @.Config = 2 or @.Config = 3 or @.Config = 6 or @.Config = 7 or @.Config =
10
> or @.Config = 11 or @.Config = 14 or @.Config = 15
> --2. if asb was found at at ie bridge court/2s/test, then include all for
> bridge court/2s/anything, but not bridge court/2s/nothing
> --ie if found at a room on a floor, report on other rooms on same floor
> (with the same parent and level)
> --Get all geogs for the same level (ie rooms on the same floor) as the geo
g
> of a specific event.
> begin
> select @.parID = parentid from geography g, event e where g.geographyid =
> e.geographyid and e.eventid = rtrim(@.EventID)
> if @.parID is not null
> begin
> --If we are not at the highest level (site) then we can check for a paren
t
> set @.sql = @.sql + " union
> select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Foun
d'
> as asbestoststaus, es.surveydate, esd.samplenumber,
> esd.materialdescription, esda.description + '[' +
> ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
> ac.description as classification, esd.risk as hazard, esd.remarks,
> g.geographyid, g.parentid, g.siteid, g.level, g.region,
> ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
> ,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
> (select site + ' ' + location + ' ' + unit + ' ' + partition from
> geography where geography.geographyid = es.geographyid) as surveygeog --gj
a
> 5.12.04
> from geography g, event e, eventsurvey es, eventsurveydetail esd,
> asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
> where g.geographyid = e.geographyid --gja5.12.04
> -- where g.geographyid = es.geographyid --gja5.12.04
> and e.geographyid in
> (select g2.geographyid
> from geography g2, geography g
> where g.geographyid in (select geographyid from event where eventid = "
+
> rtrim(@.EventID) + ")
> and g.parentid = g2.parentid
> and g.level = g2.level) and e.eventid = es.currenteventid
> and es.eventsurveyid = esd.eventsurveyid
> and ac.asbestosclassificationid = esd.classificationid
> and esd.analysisid = esda.EventSurveyDetailAnalysisid
> and c.uniqueid =* esd.materialareaunitid "
>
> -- print 'first'
> -- print 'Config=2'
> end
> else
> begin
> set @.sql = @.sql + " union
> select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Foun
d'
> as asbestoststaus, es.surveydate, esd.samplenumber,
> esd.materialdescription, esda.description + '[' +
> ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
> ac.description as classification, esd.risk as hazard, esd.remarks,
> g.geographyid, g.parentid, g.siteid, g.level, g.region,
> ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
> ,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
> (select site + ' ' + location + ' ' + unit + ' ' + partition from
> geography where geography.geographyid = es.geographyid) as surveygeog --gj
a
> 5.12.04
> from geography g, event e, eventsurvey es, eventsurveydetail esd,
> asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
> where g.geographyid = e.geographyid --gja5.12.04
> -- where g.geographyid = es.geographyid --gja5.12.04
> and e.eventid = es.currenteventid
> and es.eventsurveyid = esd.eventsurveyid
> and ac.asbestosclassificationid = esd.classificationid
> and esd.analysisid = esda.EventSurveyDetailAnalysisid
> and g.parentid is null
> and g.geographyid = (select geographyid from event where eventid = " +
> rtrim(@.EventID) + ")
> and c.uniqueid =* esd.materialareaunitid "
>
> end
> end
>
> if @.Config = 4 or @.Config = 5 or @.Config = 6 or @.Config = 7 or @.Config =
12
> or @.Config = 13 or @.Config = 14 or @.Config = 15
> --3. if asb was found at at ie red lion court/3rd/, then include all for r
ed
> lion court/3rd/anything below.
> --Do we not then include the current event? (as it is obviously not below
> the level of geog for the current event)?
> --JF says maybe, maybe not, so does.
> --Use the eventid to get the geography level
> begin
> select @.GeogID = GeographyID from event where eventid = rtrim(@.EventID)
> set @.sql = @.sql + " union
> select eventid ,
> site ,
> location ,
> unit ,
> partition ,
> asbestoststaus ,
> surveydate datetime,
> samplenumber ,
> materialdescription ,
> analysis ,
> classification ,
> hazard ,
> remarks ,
> geographyid ,
> parentid ,
> siteid ,
> level ,
> region ,
> extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail,
> AsbestosImagePath,
> surveygeog --gja5.12.04
> from AsbLowestLevel "+ "(" +rtrim( @.EventID) + "," + @.GeogID +") "
>
> --print 'Config=3'
> end
>
> if @.Config = 8 or @.Config = 9 or @.Config = 10 or @.Config = 11 or @.Config
=
> 12 or @.Config = 13 or @.Config = 14 or @.Config = 15
> --4. as 3 only cant have anything below 2s (if unit or part then dont
> include)
> --ie if asb was found at at ie bridge court/2s/test, then include all geo
gs
> at bridge court/2s/nothing.
> --Do we not then include the current event? (as it is obviously not below
> the level of geog for the current event)?
> --JF says maybe, maybe not, so does.
> begin
> select @.GeogID = GeographyID from event where eventid = rtrim(@.EventID)
> set @.sql = @.sql + " union
> select eventid ,
> site ,
> location ,
> unit ,
> partition ,
> asbestoststaus ,
> surveydate datetime,
> samplenumber ,
> materialdescription ,
> analysis ,
> classification ,
> hazard ,
> remarks ,
> geographyid ,
> parentid ,
> siteid ,
> level ,
> region ,
> extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail,
> AsbestosImagePath,
> surveygeog --gja5.12.04
>
> from AsbHighestLevel "+ "(" +rtrim( @.EventID) + "," + @.GeogID +") "
>
> end
>
> if left(@.sql, 7) = ' union'
> set @.sql = substring(@.sql, 8, len(@.sql) - 7)
> set @.sql = @.sql + " order by 1"
>
> exec sp_executesql @.sql
>
>
> --end
> SET NOCOUNT OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>

Cannot resolve collation conflict for equal to operation.

Hy Guys!

I`m having a new problem today!
I Have two Databases, with the same collation configuration. (SQL_Latin1_General_CP1_CI_AS). But when I execute this qery, I got the collation message error:

select * from Cheque C
where
NOT EXISTS
(SELECT A.cmpCod + A.banCod
FROM dbCorporativo.dbo.Agencia A
WHERE
C.chqCmc7Compe + C.chqCmc7Bco
=
A.cmpCod + A.banCod
)

Result
Cannot resolve collation conflict for equal to operation.

This query executes with succes when I execute it like this:
select * from Cheque C
where
NOT EXISTS
(SELECT A.cmpCod + A.banCod
FROM dbCorporativo.dbo.Agencia A
WHERE
C.chqCmc7Compe + C.chqCmc7Bco
=
A.cmpCod + A.banCod COLLATE SQL_Latin1_General_CP1_CI_AS
)

Some suggestions? Since the configuration for the two databases are identical?Ooopppss!! WRONG FORUM!

Sorry guys!|||Diogo,

The model database must have different collation settings, temp databases / tables obtain/inherit the model database settings. When you specify a collation it sets the collation and thus there are no problems.

Cannot resolve collation conflict for equal to operation.

Hi,
I am getting this error while trying to join two tables on two different
databases.
How can I find out the collation on each and how can I make them similar?
Plz help.
Thnx in advanceMay be the collation of the columns participating in the join are not the
same. You can select from information_schema.columns to see the collation of
those columns and pick one collation to be used with COLLATE clause.
Example:
declare @.t1 table(c1 varchar(25) collate SQL_Latin1_General_CP1_CI_AS)
declare @.t2 table(c1 varchar(25) collate SQL_Latin1_General_CP1_CS_AS)
insert into @.t1 values('microsoft')
insert into @.t2 values('microsoft')
select
*
from
@.t1 as t1
inner join
@.t2 as t2
on t1.c1 = t2.c1 collate SQL_Latin1_General_CP1_CI_AS
if you do not use the "collate" clause in the join, you will get same error
that you mentioned.
AMB
"dotnettester" wrote:

> Hi,
> I am getting this error while trying to join two tables on two different
> databases.
> How can I find out the collation on each and how can I make them similar?
> Plz help.
> Thnx in advance

Cannot resolve collation conflict for equal to operation.

Hi, I have this error when running a query:

Cannot resolve collation conflict for equal to operation.

but the situation is that I can run the query perfectly with one user (windows user) but using an standar user, I've got the error, as far as I know the collation feature applies to db's an objects not to users, what can I do to run this query with the standar user? both users have the same permission on the db. Below is the query attached.

Thank you
Gabriel

SELECT B.EmpSupervisorId, a.Info_ID as A_Info_ID,
ISNULL(a.Owner_SSO_ID,'') as A_Owner_SSO_ID,
ISNULL(a.Ref_SSO_ID,'') as A_Ref_SSO_ID,
ISNULL(a.Prev_Owner_SSO_ID,0) as A_Prev_Owner_SSO_ID,
ISNULL(a.MgmtTypeFlag,0) as A_MgmtTypeFlag,
ISNULL(a.CurrentStatusID, 1) as A_CurrentStatusID,
ISNULL(a.PrevStatusID,0) as A_PrevStatusID,
ISNULL(C.ConTypeOption, '') as C_ConTypeOption,
ISNULL(CAST(a.Last_Status_Update_Date AS VARCHAR),'') as A_Last_Status_Update_Date,
ISNULL(B.sfBUID,'0') as A_Bus_Group_ID, 0 AS A_Bus_Group_Seg_ID,
ISNULL(a.Organization,'') as A_Organization,
ISNULL(a.ContactName,'') as A_ContactName,
ISNULL(A.Title, '') as A_ContactTitle,
ISNULL(a.ContactComName,'') as A_ContactComName, ISNULL(a.Phone,'') as A_Phone,
ISNULL(a.NatureOfOppID,'') as A_NatureOfOppID, ISNULL(a.DealAmount,0) as A_DealAmount, ISNULL(CAST(a.Cust_Contacted_Date AS VARCHAR),'') A_Cust_Contacted_Date, ISNULL(CAST(a.Lead_Qualified_Date AS VARCHAR),'') A_Lead_Qualified_Date, ISNULL(CAST(a.Tran_Processed_Date AS VARCHAR),'') A_Tran_Processed_Date,
ISNULL(CAST(a.Quote_Accepted_Date AS VARCHAR),'') as A_Quote_Accepted_Date,
ISNULL(CAST(a.Approved_By_HFS_Date AS VARCHAR),'') as A_Approved_By_HFS_Date,
ISNULL(CAST(a.Funded_By_HFS_Date AS VARCHAR),'') as A_Funded_By_HFS_Date,
ISNULL(a.Lead_In_Amount,0) as A_Lead_In_Amount,
ISNULL(a.Cust_Contacted_Amount,0) as A_Cust_Contacted_Amount,
ISNULL(a.Lead_Qualified_Amount,0) as A_Lead_Qualified_Amount,
ISNULL(a.Tran_Processed_Amount,0) as A_Tran_Processed_Amount,
ISNULL(a.Quote_Accepted_Amount,0) as A_Quote_Accepted_Amount,
ISNULL(a.Approved_By_HFS_Amount,0) as A_Approved_By_HFS_Amount,
ISNULL(a.Funded_By_HFS_Amount,0) as A_Funded_By_HFS_Amount,
ISNULL(CAST(CreationDate AS VARCHAR),'') as A_CreationDate,
ISNULL(a.BusType,'') as A_BusType,
ISNULL(a.NonHFS_XLink_ContactName,'') as A_NonHFS_XLink_ContactName,
ISNULL(a.NonHFS_XLink_Bus_ID,'0') as A_NonHFS_XLink_Bus_ID,
ISNULL(a.Comments,'') as A_Comments, ISNULL(a.ExistCustomerID,'') as A_ExistCustomerID,
ISNULL(a.FinancialNeedID,'') as A_FinancialNeedID,
ISNULL(a.CampaignID,'') as A_CampaignID,
(GEC_HFS_CORE.dbo.GetBusinessDays(Last_Status_Upda te_Date, getdate())-1) as BusDateDiff, ISNULL(A.DealTypeID, '') as A_DealTypeID
FROM tblInformation a
LEFT OUTER JOIN GEC_HFS_LM_SSOInfo B ON A.Owner_SSO_ID = B.sfSSOID
LEFT OUTER JOIN tblContactType C ON a.Bustype = C.ConType_ID
WHERE a.CurrentStatusID NOT IN (9, 10, 11) AND
ISNULL(a.isNoChange,'NO') = 'NO' AND ISNULL(a.IsDeleted,'NO') = 'NO' AND
DATEDIFF(DAY, Last_Status_Update_Date, GETDATE())<> 0 AND
ISNULL(a.isNonHFS, 'NO') = 'NO'
ORDER BY A_Info_IDAdd COLLATE DATABASE_DEFAULT to character-based comparison operations and forget it.|||It is woking now, tks. Any idea why it is necessary to specify this on one account and not with the other? I wuold like to have something to say to the users.

Tks
GAP|||Here's what I think (it may be too far fetched, but I've seen it):

One of the tables has a twin brother under a different schema, which happens to be the standard login. That twin brother somehow was built using different collation. When that user logs on and runs the query (AND since you do not qualify the tables by schema) the twin brother is picked, thus, - collation errors.

Cannot resolve collation conflict for equal to operation.

I'm trying to audit our sql server and getting a collation error. "Cannot
resolve collation conflict for equal to operation." I know 3 of the
databases on this server have a different collation, but I need to find a wa
y
to get around this since I can't change the collation because they are vendo
r
databases. Does anyone have any ideas? I'm at a loss and this is due to our
auditors within the next few days. Thanks in advance for your help! This is
the code I'm using to audit:
DECLARE @.spname VARCHAR (128)
DECLARE @.dbname VARCHAR (128)
DECLARE @.logiNAME VARCHAR (2000)
DECLARE @.cmd VARCHAR (5000)
-- Declare cursor
DECLARE sp_csr INSENSITIVE CURSOR FOR
select name as dbname from sysdatabases
order by name
-- Open the cursor
OPEN sp_csr
-- Loop through all the databases in the database
FETCH NEXT
FROM sp_csr
INTO @.dbname
print @.dbname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.cmd = 'Use ' + @.dbname + ' '+ 'SELECT ''ROLE NAME'' = b.name, 30,
''OBJECT NAME'' = c.name, 30,
''ACTION'' = CASE a.action
WHEN 26 THEN ''REFERENCES''
WHEN 193 THEN ''SELECT''
WHEN 195 THEN ''INSERT''
WHEN 196 THEN ''DELETE''
WHEN 197 THEN ''UPDATE''
WHEN 198 THEN ''CREATE TABLE''
WHEN 203 THEN ''CREATE DATABASE''
WHEN 204 THEN ''GRANT_W_GRANT''
WHEN 205 THEN ''GRANT''
WHEN 206 THEN ''REVOKE''
WHEN 207 THEN ''CREATE VIEW''
WHEN 222 THEN ''CREATE PROCEDURE''
WHEN 224 THEN ''EXECUTE''
WHEN 228 THEN ''DUMP DATABASE''
WHEN 233 THEN ''CREATE DEFAULT''
WHEN 235 THEN ''DUMP TRANSACTION''
WHEN 236 THEN ''CREATE RULE''
END,
"TYPE" = CASE c.type
WHEN ''C'' THEN ''CHECK constraint''
WHEN ''D'' THEN ''Default or DEFAULT constraint''
WHEN ''F'' THEN ''FOREIGN KEY constraint''
WHEN ''K'' THEN ''PRIMARY KEY or UNIQUE constraint''
WHEN ''L'' THEN ''Log''
WHEN ''P'' THEN ''Stored procedure''
WHEN ''R'' THEN ''Rule''
WHEN ''RF'' THEN ''Stored procedure for replication''
WHEN ''S'' THEN ''System table''
WHEN ''TR'' THEN ''Trigger''
WHEN ''U'' THEN ''User table''
WHEN ''V'' THEN '' View''
WHEN ''X'' THEN ''Extended stored procedure''
WHEN ''FN'' THEN ''User Defined Function''
END,
ServerName=(select srvname from master..sysservers where srvid = ''0''),
DatabaseName=(SELECT master..sysdatabases.name
FROM dbo.sysfiles INNER JOIN
master..sysdatabases ON dbo.sysfiles.filename =
master..sysdatabases.filename)
, Environment = Case when (select srvname from master..sysservers where
srvid = ''0'') like ''Dev%''
THEN ''DEV''
WHEN (select srvname from master..sysservers where srvid = ''0'') like
''Prod%''
THEN ''PROD''
WHEN (select srvname from master..sysservers where srvid = ''0'')LIKE
''Test%''
THEN ''TEST''
ELSE ''Unknown''END
FROM sysprotects a, sysusers b, sysobjects c
WHERE a.uid = b.uid
AND c.id = a.id
--AND b.name = @.group
ORDER BY b.name, c.name, a.action'
--PRINT @.cmd
EXEC (@.cmd)
FETCH NEXT
FROM sp_csr
INTO @.dbname
END
-- Close and deallocate the cursor
CLOSE sp_csr
DEALLOCATE sp_csrat a glance, i'm not seeing anything jump out - first step would be
capturing the @.cmd that errored, run it separately and narrow down which
exact part is the problem.
It is possible to coerce a query to use a specified collation - after
the criteria. e.g.
...from table1 join table2 on table1.column = table2.column collate
<collation>...
...from table1 where column='some value' collate <collation>
Anita wrote:

>I'm trying to audit our sql server and getting a collation error. "Cannot
>resolve collation conflict for equal to operation." I know 3 of the
>databases on this server have a different collation, but I need to find a w
ay
>to get around this since I can't change the collation because they are vend
or
>databases. Does anyone have any ideas? I'm at a loss and this is due to our
>auditors within the next few days. Thanks in advance for your help! This is
>the code I'm using to audit:
>DECLARE @.spname VARCHAR (128)
>DECLARE @.dbname VARCHAR (128)
>DECLARE @.logiNAME VARCHAR (2000)
>DECLARE @.cmd VARCHAR (5000)
>
>-- Declare cursor
>DECLARE sp_csr INSENSITIVE CURSOR FOR
>select name as dbname from sysdatabases
>order by name
>-- Open the cursor
>OPEN sp_csr
>-- Loop through all the databases in the database
>FETCH NEXT
> FROM sp_csr
> INTO @.dbname
> print @.dbname
>WHILE @.@.FETCH_STATUS = 0
>BEGIN
> SELECT @.cmd = 'Use ' + @.dbname + ' '+ 'SELECT ''ROLE NAME'' = b.name, 30,
>''OBJECT NAME'' = c.name, 30,
>''ACTION'' = CASE a.action
>WHEN 26 THEN ''REFERENCES''
>WHEN 193 THEN ''SELECT''
>WHEN 195 THEN ''INSERT''
>WHEN 196 THEN ''DELETE''
>WHEN 197 THEN ''UPDATE''
>WHEN 198 THEN ''CREATE TABLE''
>WHEN 203 THEN ''CREATE DATABASE''
>WHEN 204 THEN ''GRANT_W_GRANT''
>WHEN 205 THEN ''GRANT''
>WHEN 206 THEN ''REVOKE''
>WHEN 207 THEN ''CREATE VIEW''
>WHEN 222 THEN ''CREATE PROCEDURE''
>WHEN 224 THEN ''EXECUTE''
>WHEN 228 THEN ''DUMP DATABASE''
>WHEN 233 THEN ''CREATE DEFAULT''
>WHEN 235 THEN ''DUMP TRANSACTION''
>WHEN 236 THEN ''CREATE RULE''
>END,
>"TYPE" = CASE c.type
>WHEN ''C'' THEN ''CHECK constraint''
>WHEN ''D'' THEN ''Default or DEFAULT constraint''
>WHEN ''F'' THEN ''FOREIGN KEY constraint''
>WHEN ''K'' THEN ''PRIMARY KEY or UNIQUE constraint''
>WHEN ''L'' THEN ''Log''
>WHEN ''P'' THEN ''Stored procedure''
>WHEN ''R'' THEN ''Rule''
>WHEN ''RF'' THEN ''Stored procedure for replication''
>WHEN ''S'' THEN ''System table''
>WHEN ''TR'' THEN ''Trigger''
>WHEN ''U'' THEN ''User table''
>WHEN ''V'' THEN '' View''
>WHEN ''X'' THEN ''Extended stored procedure''
>WHEN ''FN'' THEN ''User Defined Function''
>END,
>ServerName=(select srvname from master..sysservers where srvid = ''0''),
>DatabaseName=(SELECT master..sysdatabases.name
>FROM dbo.sysfiles INNER JOIN
> master..sysdatabases ON dbo.sysfiles.filename =
>master..sysdatabases.filename)
>, Environment = Case when (select srvname from master..sysservers where
>srvid = ''0'') like ''Dev%''
> THEN ''DEV''
> WHEN (select srvname from master..sysservers where srvid = ''0'') like
>''Prod%''
> THEN ''PROD''
> WHEN (select srvname from master..sysservers where srvid = ''0'')LIKE
>''Test%''
> THEN ''TEST''
> ELSE ''Unknown''END
>FROM sysprotects a, sysusers b, sysobjects c
>WHERE a.uid = b.uid
>AND c.id = a.id
>--AND b.name = @.group
>ORDER BY b.name, c.name, a.action'
>
> --PRINT @.cmd
>EXEC (@.cmd)
> FETCH NEXT
> FROM sp_csr
> INTO @.dbname
>END
>
>-- Close and deallocate the cursor
>CLOSE sp_csr
>DEALLOCATE sp_csr
>|||Change
DatabaseName=(SELECT master..sysdatabases.name
FROM dbo.sysfiles INNER JOIN
master..sysdatabases ON dbo.sysfiles.filename =
master..sysdatabases.filename)
to
DatabaseName=(SELECT master..sysdatabases.name
FROM dbo.sysfiles INNER JOIN
master..sysdatabases ON dbo.sysfiles.filename COLLATE
Latin1_General_CI_AS =
master..sysdatabases.filename COLLATE Latin1_General_CI_AS)|||That worked as far as getting those databases that have a different
collation's info, but I'm still getting errors. Now I'm getting these errors
:
Line 41: Incorrect syntax near 'COLLATE'.
Server: Msg 156, Level 15, State 1, Line 45
Incorrect syntax near the keyword 'like'.
Thanks!
"markc600@.hotmail.com" wrote:

> Change
> DatabaseName=(SELECT master..sysdatabases.name
> FROM dbo.sysfiles INNER JOIN
> master..sysdatabases ON dbo.sysfiles.filename =
> master..sysdatabases.filename)
> to
> DatabaseName=(SELECT master..sysdatabases.name
> FROM dbo.sysfiles INNER JOIN
> master..sysdatabases ON dbo.sysfiles.filename COLLATE
> Latin1_General_CI_AS =
> master..sysdatabases.filename COLLATE Latin1_General_CI_AS)
>

Cannot resolve collation conflict for equal to operation

Hello,
i am getting the following error in SQLServer2000:-
Cannot resolve collation conflict for equal to operation

Sequence Of Operations :
1. Restored a database from a SQLSERver7.0 backupfile into SQLServer2000 database

2. Differences found when the stored proc. 'sp_server_info' was run on SQLSErver2000 and SQLServer7.0

IDENTIFIER_CASE
SQLSERver7.0 SENSITIVE
SQLServer2000 MIXED

COLLATION_SEQ
SQLSERver7.0 :- charset=iso_1 sort_order=bin_iso_1 charset_num=1 sort_order_num=50
SQLServer2000 :- charset=iso_1 sort_order=nocase_iso charset_num=1 sort_order_num=52

3. For the command "select DATABASEPROPERTYEX()" in SQLServer2000
The temporary and master database returns --> SQL_Latin1_General_CP1_CI_AS
The restored database returns --> Latin1_General_BIN

4. I am getting the error in a sp where data is being fetched from 2 temporary tables.
I suppose the backupup database 'Collation' has to match the Master & temdb 'Collation'

How do i change the 'Collation' parameter of the new database so that it matches with the tempdb database ? Will this solve the problem ?Tempdb will use the collation of the model database when the server is started (i.e. tempdb is built).

Use can coerce the collations used in queries to compare using a specific collation.
= (colname collate collationname)

You can change the collation of a database by
alter database collate collationname
this will change the default collation for new tables
For existing ones I think you will have to run alter table scripts.

These may all change the representation of the data though.

Cannot resolve collation conflict for equal to operation

We have loaded a db from a different SQL Server, and now we are trying to recreate some stored procedures. Some procs experience the error "Cannot resolve collation conflict for equal to operation ".

Can we simply create a new db on our target Sql Server, create the tables w/ the default collation sequence, and then use DTS to pull the data and populate the new db? Will this pull the data and store it in the desired collation? Seems like it should - let me know if it will/will not work, or if there's an easier way. Thanks!apparently the default collation of your sql server is not the same as the one from which you loaded the data. you can create a new database with the collation being the same as the one that of the server from which you are getting your data. and your new database will work without you changing anything!!!

Cannot resolve collation conflict for equal operation

Hi, Im having a critical problem.

I have two databases: persons and cars

In database persons I have a table
named persons_class1 as follows:
person_id char(13) not null [primary key]
name varchar(20) not null

In database cars I have a table
named cars_blue as follows:
car_id char(13) not null [primary key]
model varchar(20) not null
person_id char(13) not null

In my program the initial catalog is: cars
and Im executing the following SQL:

select cars_blue.model,persons.dbo.persons_class1.name
from cars_blue,persons.dbo.persons_class1
where cars_blue.person_id=persons.dbo.persons_class1.per son_id

and the following error occurs:
Cannot resolve collation conflict for equal operation

I also tried like instead of = and the error is:
Cannot resolve collation conflict for like operation

what can I do?

Thanks in advance
RolandA collation is the way that characters are compared/sorted. Apparently your personid columns are character based, so at least at first I'd suggest using:select cars_blue.model,persons.dbo.persons_class1.name
from cars_blue,persons.dbo.persons_class1
where cars_blue.person_id COLLATE SQL_Latin1_General = persons.dbo.persons_class1.per son_id COLLATE SQL_Latin1_General-PatP|||Collation does not pertain to the fields that are being compared but to the operator that is used in comparison.

select cars_blue.model,persons.dbo.persons_class1.name
from cars_blue,persons.dbo.persons_class1
where cars_blue.person_id = persons.dbo.persons_class1.per son_id COLLATE SQL_Latin1_General_CP1251_CI_AS|||Collation does not pertain to the fields that are being compared but to the operator that is used in comparison.So you apply the collation to the operator, and once the collation is applied that operator will then compare any two objects? At least as I see it, the comparison operator invokes an operation which is dependant on the collations of both of the objects that are being compared. Did I miss a meeting somewhere?

-PatP|||Great, I solved the problem!!, further Im allowed to change
the default collation of the database.

Thanks!
Roland|||Well, he already solved it, so there is no point to continue, but...The COLLATE tells the optimizer what code page to use while comparing 2 values, not how they need to be collated before comparison can be performed. That's why your syntax will produce an error.|||So then how can this be?SELECT Count(*)
FROM dbo.sysobjects AS a
WHERE a.name = a.name
GO

SELECT Count(*)
FROM dbo.sysobjects AS a
WHERE a.name COLLATE Latin1_General_BIN = a.name

SELECT Count(*)
FROM dbo.sysobjects AS a
WHERE a.name COLLATE Latin1_General_BIN = a.name COLLATE Latin1_General_BIN
GO

SELECT Count(*)
FROM dbo.sysobjects AS a
WHERE a.name COLLATE Latin1_General_BIN = a.name COLLATE Cyrillic_General_BIN
GO-PatP|||Oh for Codd's sake, as you see yourself, the fourth statement will produce an error because you can't perform a comparison after collating 2 values to different code pages. Doesn't it mean to you that only 1 COLLATE statement actually makes sense? In the example below you'll see that regardless of what collation is chosen for a field, COLLATE forces the comparison to be performed using a specified code page. It does not "collate" the values!

begin tran
go
create table #t1 (f1 char(1) collate Cyrillic_General_BIN not null)
create table #t2 (f1 char(1) collate latin1_general_bin not null)
insert #t1 values ('a')
insert #t2 values ('a')
go
select * from #t1, #t2 where #t1.f1 = #t2.f1 collate French_BIN
select * from #t1 inner join #t2 on #t1.f1 = #t2.f1 collate French_BIN
go
rollback tran
go|||Oh for Codd's sake, as you see yourself, the fourth statement will produce an error because you can't perform a comparison after collating 2 values to different code pages. Doesn't it mean to you that only 1 COLLATE statement actually makes sense? In the example below you'll see that regardless of what collation is chosen for a field, COLLATE forces the comparison to be performed using a specified code page. It does not "collate" the values!In your earlier post I thought you said that the collation only applied to the operator, which I assumed meant there could only be one collation for an operator. What operator are you collating within your CREATE TABLE statements?

A collation applies to data, and within SQL Server it specifically applies to character data. You can apply operators to that character data, but you can't apply a collation to the operators. The relational operators (like equal, greather than, etc) use the collation to determine how they can compare the data. This determines things like does case matter, are accents important, what order should the characters sort, etc. Note that one set of rules can be used for sorting and a quite different set can be used for comparisons in some cases within the same collation!

-PatP

Cannot resolve collation

Trying tun a script that updates a table and i get the
following error
'Cannot resolve collation conflict for equal to operation.'
Any ideas what causes this?Probably different collations between your system databases and the
application database. Either make sure that the databases has the same
collation or adjust the offending query and specify which collation the
query is to use (using the COLLATE clause).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:b7b501c40815$73fe2f30$a301280a@.phx.gbl...
> Trying tun a script that updates a table and i get the
> following error
> 'Cannot resolve collation conflict for equal to operation.'
> Any ideas what causes this?