June 14, 2016 at 3:02 am
I am combining two quries using " union all".when i run the query i get the following errror :
"Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict".
Here is the query below
SELECT SEM_COMPUTER.COMPUTER_NAME AS 'Computer name',
PATTERN.Version AS 'Virus definition used',
dateadd(second, SEM_AGENT.LAST_UPDATE_TIME/1000, '1970-01-01') AS 'Last check-in (GMT)', SEM_AGENT.AGENT_VERSION
FROM sem12Anglo.dbo.SEM_COMPUTER
INNER JOIN sem12Anglo.dbo.SEM_AGENT ON SEM_AGENT.COMPUTER_ID=SEM_COMPUTER.COMPUTER_ID
inner join [sem12Anglo].dbo.[IDENTITY_MAP] as d
on d.id = SEM_AGENT.group_id and d.Name like '%Greenside%'
INNER JOIN sem12Anglo.dbo.SEM_CONTENT ON SEM_CONTENT.AGENT_ID=SEM_AGENT.AGENT_ID
INNER JOIN sem12Anglo.dbo.PATTERN ON PATTERN.PATTERN_IDX=SEM_AGENT.PATTERN_IDX
INNER JOIN (
SELECT SEM_COMPUTER.COMPUTER_NAME AS 'TempHostName',
MAX(SEM_AGENT.LAST_UPDATE_TIME) AS 'TempMax'
FROM sem12Anglo.dbo.SEM_COMPUTER
INNER JOIN sem12Anglo.dbo.SEM_AGENT ON SEM_AGENT.COMPUTER_ID=SEM_COMPUTER.COMPUTER_ID
inner join [sem12Anglo].dbo.[IDENTITY_MAP] as d
on d.id = SEM_AGENT.group_id and d.Name like '%Greenside%'
GROUP BY COMPUTER_NAME)
TestTable ON TestTable.TempHostName=SEM_COMPUTER.COMPUTER_NAME
AND TestTable.TempMax=SEM_AGENT.LAST_UPDATE_TIME
WHERE PATTERN.PATTERN_TYPE='VIRUS_DEFS'
AND PATTERN.DELETED='0'
AND SEM_CONTENT.DELETED='0'
AND SEM_AGENT.DELETED='0'
AND SEM_COMPUTER.DELETED='0'
GROUP BY SEM_COMPUTER.COMPUTER_NAME, SEM_AGENT.LAST_UPDATE_TIME, PATTERN.Version, SEM_AGENT.AGENT_VERSION
UNION ALL
SELECT SEM_COMPUTER.COMPUTER_NAME AS 'Computer name',
PATTERN.Version AS 'Virus definition used',
dateadd(second, SEM_AGENT.LAST_UPDATE_TIME/1000, '1970-01-01') AS 'Last check-in (GMT)', SEM_AGENT.AGENT_VERSION
FROM sem5.dbo.SEM_COMPUTER
INNER JOIN sem5.dbo.SEM_AGENT ON SEM_AGENT.COMPUTER_ID=SEM_COMPUTER.COMPUTER_ID
inner join [sem5].dbo.[IDENTITY_MAP] as d
on d.id = SEM_AGENT.group_id and d.Name like '%Greenside%'
INNER JOIN sem5.dbo.SEM_CONTENT ON SEM_CONTENT.AGENT_ID=SEM_AGENT.AGENT_ID
INNER JOIN sem5.dbo.PATTERN ON PATTERN.PATTERN_IDX=SEM_AGENT.PATTERN_IDX
INNER JOIN (
SELECT SEM_COMPUTER.COMPUTER_NAME AS 'TempHostName',
MAX(SEM_AGENT.LAST_UPDATE_TIME) AS 'TempMax'
FROM sem5.dbo.SEM_COMPUTER
INNER JOIN sem5.dbo.SEM_AGENT ON SEM_AGENT.COMPUTER_ID=SEM_COMPUTER.COMPUTER_ID
inner join [sem5].dbo.[IDENTITY_MAP] as d
on d.id = SEM_AGENT.group_id and d.Name like '%Greenside%'
GROUP BY COMPUTER_NAME)
TestTable ON TestTable.TempHostName=SEM_COMPUTER.COMPUTER_NAME
AND TestTable.TempMax=SEM_AGENT.LAST_UPDATE_TIME
WHERE PATTERN.PATTERN_TYPE='VIRUS_DEFS'
AND PATTERN.DELETED='0'
AND SEM_CONTENT.DELETED='0'
AND SEM_AGENT.DELETED='0'
AND SEM_COMPUTER.DELETED='0'
GROUP BY SEM_COMPUTER.COMPUTER_NAME, SEM_AGENT.LAST_UPDATE_TIME, PATTERN.Version, SEM_AGENT.AGENT_VERSION
Please help.Thank you in dvance
June 14, 2016 at 4:04 am
tmmutsetse (6/14/2016)
I am combining two quries using " union all".when i run the query i get the following errror :"Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict".
You get the error because either the databases sem12angelo and sem5 have different collations, or some of the columns in the query have different collations.
I cant' tell from your query if it's the databases or some specific column that have different collations, but you can bypass the problem by doing something like this:
SELECT Table1.Column1 COLLATE DATABASE_DEFAULT
FROM
Database1.dbo.Table1
UNION ALL
SELECT Table1.Column1
FROM
Database2.dbo.Table1
June 14, 2016 at 4:15 am
Thank you i noticed that sep12 is on Latin1_General_CI_AS and sem5 is on SQL_Latin1_General_CP1_CI_AS.
however if i can add like below still it gives an error
SELECT SEM_COMPUTER.COMPUTER_NAME AS 'Computer name', collate DATABASE_DEFAULT
PATTERN.Version AS 'Virus definition used',
dateadd(second, SEM_AGENT.LAST_UPDATE_TIME/1000, '1970-01-01') AS 'Last check-in (GMT)', SEM_AGENT.AGENT_VERSION
FROM sem12Anglo.dbo.SEM_COMPUTER
INNER JOIN sem12Anglo.dbo.SEM_AGENT ON SEM_AGENT.COMPUTER_ID=SEM_COMPUTER.COMPUTER_ID
inner join [sem12Anglo].dbo.[IDENTITY_MAP] as d
on d.id = SEM_AGENT.group_id and d.Name like '%Greenside%'
INNER JOIN sem12Anglo.dbo.SEM_CONTENT ON SEM_CONTENT.AGENT_ID=SEM_AGENT.AGENT_ID
INNER JOIN sem12Anglo.dbo.PATTERN ON PATTERN.PATTERN_IDX=SEM_AGENT.PATTERN_IDX
INNER JOIN (
SELECT SEM_COMPUTER.COMPUTER_NAME AS 'TempHostName',
MAX(SEM_AGENT.LAST_UPDATE_TIME) AS 'TempMax'
FROM sem12Anglo.dbo.SEM_COMPUTER
INNER JOIN sem12Anglo.dbo.SEM_AGENT ON SEM_AGENT.COMPUTER_ID=SEM_COMPUTER.COMPUTER_ID
inner join [sem12Anglo].dbo.[IDENTITY_MAP] as d
on d.id = SEM_AGENT.group_id and d.Name like '%Greenside%'
GROUP BY COMPUTER_NAME)
TestTable ON TestTable.TempHostName=SEM_COMPUTER.COMPUTER_NAME
AND TestTable.TempMax=SEM_AGENT.LAST_UPDATE_TIME
WHERE PATTERN.PATTERN_TYPE='VIRUS_DEFS'
AND PATTERN.DELETED='0'
AND SEM_CONTENT.DELETED='0'
AND SEM_AGENT.DELETED='0'
AND SEM_COMPUTER.DELETED='0'
GROUP BY SEM_COMPUTER.COMPUTER_NAME, SEM_AGENT.LAST_UPDATE_TIME, PATTERN.Version, SEM_AGENT.AGENT_VERSION
UNION ALL
SELECT SEM_COMPUTER.COMPUTER_NAME AS 'Computer name'
PATTERN.Version AS 'Virus definition used',
dateadd(second, SEM_AGENT.LAST_UPDATE_TIME/1000, '1970-01-01') AS 'Last check-in (GMT)', SEM_AGENT.AGENT_VERSION
FROM sem5.dbo.SEM_COMPUTER
INNER JOIN sem5.dbo.SEM_AGENT ON SEM_AGENT.COMPUTER_ID=SEM_COMPUTER.COMPUTER_ID
inner join [sem5].dbo.[IDENTITY_MAP] as d
on d.id = SEM_AGENT.group_id and d.Name like '%Greenside%'
INNER JOIN sem5.dbo.SEM_CONTENT ON SEM_CONTENT.AGENT_ID=SEM_AGENT.AGENT_ID
INNER JOIN sem5.dbo.PATTERN ON PATTERN.PATTERN_IDX=SEM_AGENT.PATTERN_IDX
INNER JOIN (
SELECT SEM_COMPUTER.COMPUTER_NAME AS 'TempHostName',
MAX(SEM_AGENT.LAST_UPDATE_TIME) AS 'TempMax'
FROM sem5.dbo.SEM_COMPUTER
INNER JOIN sem5.dbo.SEM_AGENT ON SEM_AGENT.COMPUTER_ID=SEM_COMPUTER.COMPUTER_ID
inner join [sem5].dbo.[IDENTITY_MAP] as d
on d.id = SEM_AGENT.group_id and d.Name like '%Greenside%'
GROUP BY COMPUTER_NAME)
TestTable ON TestTable.TempHostName=SEM_COMPUTER.COMPUTER_NAME
AND TestTable.TempMax=SEM_AGENT.LAST_UPDATE_TIME
WHERE PATTERN.PATTERN_TYPE='VIRUS_DEFS'
AND PATTERN.DELETED='0'
AND SEM_CONTENT.DELETED='0'
AND SEM_AGENT.DELETED='0'
AND SEM_COMPUTER.DELETED='0'
GROUP BY SEM_COMPUTER.COMPUTER_NAME, SEM_AGENT.LAST_UPDATE_TIME, PATTERN.Version, SEM_AGENT.AGENT_VERSION
I am not sure if i am doing it right.
June 14, 2016 at 5:11 am
The collate clause needs to be specified on every varchar column in the query, also in the JOIN if you're joining on varchar columns.
But the easiest solution is probably to change collation on one of the databases. But be careful if one is a production database. Or maybe both are? If not, change collation on the non-production one to the same collation as the production database.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply