Solved

sql query to reportserver  table error

Posted on 2016-11-25
3
50 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 28

Accepted Solution

by:
Pawan Kumar earned 250 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 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

Suggested Solutions

Title # Comments Views Activity
SQL USE DATABASE VARIABLE 5 28
sql server query 18 39
sql 2016 data tools breakdown.. 1 14
Create a Scheduled Job in MSSQL from MS ACCESS and VBA 5 13
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

860 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