Post reply

SSRS 2008 R2 Admin Report

  • September 23, 2014 at 3:23 pm

    #311089

    I'm not sure if what I want to do is possible and I'm new enough to Reporting Services that I'm not even sure what to Google. What I'm looking to do is this. I have several instances of SQL Server on multiple servers. I want to run a simple query on each of them (probably using multiple Data Sources) and put the results into a single list that is sorted dynamically based on the datetime returned (e.g. Dev might show on top, but if I restart the service on Prod and rerun the report, then Prod would show on top).

    SELECT @@SERVERNAME AS [Server], create_date AS [UpSince]

    FROM master.sys.databases WHERE name = 'tempdb'

    Is this doable with SSRS? If so, would you mind pointing me in the right direction?

    Thanks!

  • September 23, 2014 at 7:30 pm

    #1747694

    I would look at having a central database (support or management type) that contains this type of information.

    Example:

    - Create a management database on the instance your SSRS is configured on.

    - Create linked servers to each instance you need to manage

    - Create a procedure that pulls in your data from each instance and writes to a local table within your management database

    - Create a SQL Agent job that executes that procedure on a schedule based on when the report needs to be run

    - Build your report based on the data in that management database, allowing you to keep up with one data source.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • September 24, 2014 at 4:19 am

    #1747760

    Shawn,

    Thanks for the reply. I was looking for something simple that could be run on demand. If SSRS can't do what I've described, I'd rather build an SSIS package to do this.

    Thanks,

    Alex

  • September 24, 2014 at 7:59 am

    #1747841

    alex_pixley (9/24/2014)

    Shawn,

    Thanks for the reply. I was looking for something simple that could be run on demand. If SSRS can't do what I've described, I'd rather build an SSIS package to do this.

    Thanks,

    Alex

    If you just want the information on demand write it in PowerShell and have it build out an HTML report file.

    [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

    $sList = "myServer1","myServer2\Instance1","myserver3"

    $results = foreach ($s in $sList)

    {

    $srv = new-object microsoft.sqlserver.management.smo.server $s

    $srv.Databases["tempdb"] | Select @{Label="ServerName";Expression={$s}}, @{Label="UpSince";Expression={$_.CreateDate}}

    }

    $results | ConverTo-Html | Out-File C:\Temp\Test.html

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply