SQL Error solved – “Syntax error converting from a character string to uniqueidentifier.”

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…
 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: