[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql query to reportserver  table error

Posted on 2016-11-25
3
Medium Priority
?
94 Views
Last Modified: 2016-11-25
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
0
Comment
Question by:motioneye
3 Comments
 
LVL 37

Accepted Solution

by:
Pawan Kumar earned 1000 total points
ID: 41901328
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
 
LVL 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 total points
ID: 41901337
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
 

Author Closing Comment

by:motioneye
ID: 41902100
Thanks both of you .
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question