Declare @LiveRS Table (
RDL varchar(MAX),
CompletePath nvarchar(max)
)
Insert Into @LiveRS
select convert(varchar(max), convert(varbinary(max), content)) [RDL],Path + Name [CompletePath]
from [TEST].[Reportserver].[dbo].catalog
where content is not null
Declare @SourceControlRS Table (RDL varchar(max),
CompletePath nvarchar(max))
Insert Into @SourceControlRS
select convert(varchar(max), convert(varbinary(max), content)) [RDL],Path + Name [CompletePath]
from [LIVE].[Reportserver].[dbo].catalog
where content is not null
Select SC.CompletePath [Source Control Path], RS.CompletePath [Live Server Path],case
When SC.RDL = RS.RDL Then 'Match'
Else 'RDLs different'
End [RDL Match],
Case
When SC.CompletePath = RS.CompletePath Then 'Match'
Else 'Paths Different'
End [Path Match]
From @LiveRS RS
Full Outer join @SourceControlRS SC on SC.CompletePath = RS.CompletePath
[/Code]
that should work shouldn't it?