Kevin Holman's System Center Blog

Posts in this blog are provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of UseAre you interested in having a dedicated engineer that will be your Mic

Creating a new data source for reporting against the Operational Database

Creating a new data source for reporting against the Operational Database

  • Comments 12
  • Likes

I am going to be publishing several reports over the next few months, that query the Operational Database instead of the Data warehouse.  We will use these reports for getting all sorts of administrative information... which will allow us to get data without having to launch a SQL query analyzer window all the time.

 

First thing... we will need to create a new Data Source for the OpsDB.

Start, by opening a web page, and browsing to http://SRSServer/Reports  where "SRSServer" is the name of your reporting server.

 

Click "New Data source"

image

 

1.  For the Name - type "OpsDB"  This will be the data source that I will be using for all my reports.  If you want a different name, thats fine and dandy... you will just have to modify each report to link to your data source name.  Not a big deal.

 

2.  In the "Connections String" paste in the following:     data source=DBSERVER;initial catalog=OperationsManager;Integrated Security=SSPI

DBSERVER is the name of the SQL server hosting your operational database.  Initial Catalog is the name of your Operational Database.

 

3.  Under "Connect Using" choose "Credentials are not required"  Click OK.

 

Here is my example:

 

image

 

 

All done with the data source!

 

Now... we need a custom folder to place our custom reports.  Click New Folder:

 

image

 

Give this folder a name.  This name will appear to all reporting users in the OpsMgr console.  I am calling mine "Custom - Reports"

 

image

 

Click OK.

 

Now - in the OpsMgr Console - we can see our new report folder:

 

image

 

When we publish or import new reports in the future, we can use this folder.

Comments
  • Certain types of agents need the agent proxy setting enabled.  These are documented in various guides...

  • In OpsMgr 2007, when a agent experiences a heartbeat failure, several things happen.  There are

  •   This is a continuation of my previous post on determining which agents are missing a hot-fix:

  • Kevin, great blog.  I setup the datasource as described here, but after importing your Agents_Missing_Hotfix.rdl and trying to run it, I get an error "the data source connection information has been deleted (rsInvalidDataSourceReference)".  If I try to open it with Report Builder an error says the report cannot be opened because no data source is associated with it and to associate a model data source with the report and try again.  This happens from the OpsMgr console and from SRS.

  • Yeah - shame on me - I dorked up the data source in that report.  Simply go to the reporting server url http://servername/reports, find the Agents Missing hotfix report - click it - get the error.  Then - click properties, and on the left - click Data Sources - on shared data source - browse to the correct "OpsDB" we just made here... and then click ok, then apply, then your report will work.

    Sorry... I guess I had a different data source when I wrote that one.  But this is a good learning opportunity on how to change a data source in a report!  :-)

  • Uh, this is embarrassing, I just figured out I had to edit the report data source in SRS and choose a shared data source 'OpsDB'.  So that problem is solved.  Now the report lets me put in a hotfix KB number, but when I run the report, I get an error "An error has occurred during report processing.  Cannot create a connection to data source 'OperationsManager'."  That is the name of my operational database.

  • paste your connect string....

  • data source=iolopsmgr01;initial catalog=OperationsManager;Integrated Security=SSPI

    I also noticed that in the .rdl of the report, it says <DataSource Name="OperationsManager">.  I tried changing the name of my datasource from OpsDB to OperationsManager and I get the same error.

  • Looks like a permissions issue.  When I change my datasource to use "Credentials supplied by the user running the report" and also click "use as Windows credentials when connecting to the data source", I can run the report.

  • Nice report. Works well,and I learnt a thing or two about SRS along the way. I did have to refer to Marnix's blog about importing rdl files, but then it all came together.

    Thx Kevin

    John Bradshaw

    http://thoughtsonopsmgr.blogspot.com/2009/12/how-to-upload-rdl-file-for-sql-server.html

  • Hi Kevin,

    I have followed the steps in creating the data source and imported the rdl file after updating Ops to OpsDB. Im getting an error  - could not open a connection to sql server.

  • Thanks, great blog. Jimmy created something like that for us in the past actually

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
Search Blogs