sql query to reportserver table error

I have a query below which run fine except when I include the join to the sysjobs table, I just can't figure out why the query get error, anyone has ideas where and how to start troubleshooting ? Here is the error message return.

Msg 8169, Level 16, State 2, Line 17
Conversion failed when converting from a character string to uniqueidentifier.



SELECT
          c.Name AS ReportName
          , rs.ScheduleID AS JOB_NAME
          , s.[Description]
          , s.LastStatus
          , s.LastRunTime
          --,JBS.Name
              --INTO drop table JOBS_Error
    FROM
          ReportServer..[Catalog] c
          JOIN ReportServer..Subscriptions s ON c.ItemID = s.Report_OID
          JOIN ReportServer..ReportSchedule rs ON c.ItemID = rs.ReportID
          JOIN msdb..sysjobs jbs ON JBS.Name = rs.ScheduleID
          AND rs.SubscriptionID = s.SubscriptionID
motioneyeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Try..

rs.ScheduleID is of type UniqueIdentifier and JBS.Name is of type Varchar.

SELECT
          c.Name AS ReportName
          , rs.ScheduleID AS JOB_NAME
          , s.[Description]
          , s.LastStatus
          , s.LastRunTime
          ,JBS.Name 
              --INTO drop table JOBS_Error
    FROM
          ReportServer..[Catalog] c
          JOIN ReportServer..Subscriptions s ON c.ItemID = s.Report_OID
          JOIN ReportServer..ReportSchedule rs ON c.ItemID = rs.ReportID
          JOIN msdb..sysjobs jbs ON JBS.Name = CAST(rs.ScheduleID AS VARCHAR(100))
          AND rs.SubscriptionID = s.SubscriptionID

Open in new window

0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
As the error states you can't join columns with different types. Name is varchar and ScheduleID is uniqueidentifier.
As stated the MSDN article the uniqueidentifier type is limited to 36 characters so you just need to convert it to a VARCHAR(36) data type to have your query working properly:
    JOIN msdb..sysjobs jbs ON JBS.Name = CAST(rs.ScheduleID AS VARCHAR(36))
0
 
motioneyeAuthor Commented:
Thanks both of you .
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.