The Case of the lost Report Data Sources

Maybe you had this one before: after doing a fresh install of Service Manager or even after SCSM Reporting was already up and running, some or all of the reports lost the connection to their data source. Because I had this issue again this week, I decided to write a blog post on how to solve this.

This is how the error looks like. When you run a Report in the SCSM Console an error message is displayed telling you that the Data Source is not valid.

image

When you connect to the SQL Reporting Server Website you will see that the Report lost the configured data source.

image

OK, this can be easily fixed by just browsing to the data source that exists on the server and reconnect the data source. But doing this for 30+ Reports is not very handy. Another way to solve this is executing a SQL script that does the work for you. Just run this script against the SQL ReportServer Database for SCSM.

USE [ReportServer]
GO

UPDATE [dbo].[DataSource]
SET [Link] = (SELECT TOP 1 [ItemID] FROM [dbo].[Catalog] where name = [dbo].[DataSource].[Name])
WHERE name is not null and link is null

After the script was successfully executed, the reports are connected again.

image

image

Thanks to Raphael Koeppel – a SQL specialist of one of our customers – to share the script!

regards
Marcel

This entry was posted in SCSM and tagged , , , , , . Bookmark the permalink.

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