If any of you ever come across this kind of error when running a SQL query to query tables:
Server: Msg 8169, Level 16, State 2, Line 1
Syntax error converting from a character string to uniqueidentifier.
Make sure you cast the matching field to the correct data type – for instance (Querying for SQL Reporting’s report subscriptions):
SELECT msdb.dbo.sysjobs.name, msdb.dbo.sysjobs.enabled, ReportServer.dbo.Catalog.Name, ReportServer.dbo.Catalog.ExecutionTime, ReportServer.dbo.Schedule.StartDate, ReportServer.dbo.Schedule.NextRunTime FROM ReportServer.dbo.Schedule INNER JOIN ReportServer.dbo.ReportSchedule ON ReportServer.dbo.Schedule.ScheduleID = ReportServer.dbo.ReportSchedule.ScheduleID INNER JOIN ReportServer.dbo.Catalog ON ReportServer.dbo.ReportSchedule.ReportID = ReportServer.dbo.Catalog.ItemID INNER JOIN msdb.dbo.sysjobs ON msdb.dbo.sysjobs.name = cast(ReportServer.dbo.Schedule.ScheduleID as sysname)
In the example above, note for the last line:
ON msdb.dbo.sysjobs.name = cast(ReportServer.dbo.Schedule.ScheduleID as sysname)
"ReportServer.dbo.Schedule.ScheduleID" is cast to sysname – which is the data type for "msdb.dbo.sysjobs.name"
I guess if you think about this, it’s rather obvious you can’t compare apple to oranges…