PowerShell cmdlets (Reporting Services SharePoint Mode)

When you install SQL Server 2012 Reporting Services SharePoint mode, PowerShell cmdlets are installed to support report Servers in SharePoint mode. The cmdlets cover three categories of functionality.

• Installation of the Reporting Services SharePoint shared service and proxy.

• Provisioning and management of Reporting Services service applications and associated proxies.

• Management of Reporting Services features, for example extensions and encryption keys.

This topic contains the following:

• Cmdlet Summary

• Shared Service Cmdlets

• Service Application Cmdlets

• Reporting Services functionality related Cmdlets

• Basic Samples

• Detailed Samples

  • Create a service application and proxy

  • Review and update a delivery extension

  • Get and set Properties of the Reporting Service Application Database

  • List Data Extensions

  • Change and list subscription owners

Cmdlet Summary

To run the cmdlets you need to open the SharePoint Management Shell. You can also use the graphical user interface editor that is included with Microsoft Windows, Windows PowerShell Integrated Scripting Environment (ISE). For more information, see Starting Windows PowerShell on Windows Server (https://technet.microsoft.com/en-us/library/hh847814.aspx). In the following cmdlet summaries, the references to service application ‘databases’, refer to all of the databases created and used by a Reporting Services service application. This includes the configuration, alerting, and temp databases.

If you see an error message similar to the following when you type the PowerShell examples:

• Install-SPRSService : The term 'Install-SPRSService' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

One of the following issues is occurring:

• Reporting Services SharePoint mode is not installed and therefore the Reporting Services cmdlets are not installed.

• You ran the PowerShell command in Windows PowerShell or Windows PowerShell ISE instead of the SharePoint Management Shell. Use the SharePoint Management shell or add the SharePoint Snap-in to the Windows PowerShell window with the following command:

  Add-PSSnapin Microsoft.SharePoint.PowerShell
  

For more information see Use Windows PowerShell to administer SharePoint 2013 (https://technet.microsoft.com/en-us/library/ee806878.aspx).

To Open the SharePoint Management Shell and run cmdlets

1. Click the Start button

2. Click the Microsoft SharePoint Products group.

3. Click the SharePoint Management Shell.

To view command line help for a cmdlet use the PowerShell ‘Get-Help’ command at the PowerShell command prompt. For example:

Get-Help Get-SPRSServiceApplicationServers

Top

Shared Service and Proxy Cmdlets

The following table contains the PowerShell cmdlets for the Reporting Services SharePoint shared service.

Top

Service Application and Proxy Cmdlets

The following table contains the PowerShell cmdlets for Reporting Services service applications and their associated proxies.

Top

Reporting Services Custom Functionality Cmdlets

Basic Samples

Return a list of cmdlets that contain ‘SPRS’ in the name. This will be the full list of Reporting Services cmdlets.

Get-command –noun *SPRS*

Or with a little more detail, piped to a text file named commandlist.txt

Get-command -noun *SPRS*| select name, definition |Format-list | Out-File c:\commandlist.txt

Install the Reporting Services SharePoint service and service proxy.

Install-SPRSService

Install-SPRSServiceProxy

Start the Reporting Services service

get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance

Type the following command from the SharePoint Management Shell to return a filtered list of rows from the a log file. The command will filter for lines that contain “ssrscustomactionerror”. This example is looking at the log file created when the rssharepoint.msi was installed.

Get-content -path C:\Users\testuser\AppData\Local\Temp\rs_sp_0.log | select-string "ssrscustomactionerror"

Top

Detailed Samples

In addition to the following samples, see the section “Windows PowerShell Script” in the topic (Steps 1-4) Windows PowerShell script for Steps 1–4.

Create a service application and proxy

This sample script completes the following tasks:

1. Create a Reporting Services service application and proxy. The script assumes the application pool “My App Pool” already exists.

2. Add the proxy to the default proxy group

3. Grant the service app access to the port 80 web app’s content database. The script assumes site “http://sitename” already exists.

# Create service application and service application proxy
$appPool = Get-SPServiceApplicationPool “My App Pool”
$serviceApp = New-SPRSServiceApplication “My RS Service App” –ApplicationPool $appPool
$serviceAppProxy = New-SPRSServiceApplicationProxy –Name “My RS Service App Proxy” –ServiceApplication $serviceApp

# Add service application proxy to default proxy group.  Any web application that uses the default proxy group will now be able to use this service application.
Get-SPServiceApplicationProxyGroup –default | Add-SPServiceApplicationProxyGroupMember –Member $serviceAppProxy

# Grant application pool account access to the port 80 web application’s content database.
$webApp = Get-SPWebApplication “http://sitename”
$appPoolAccountName = $appPool.ProcessAccount.LookupName()
$webApp.GrantAccessToProcessIdentity($appPoolAccountName)

Review and update a delivery extension

The following PowerShell script example, updates the configuration for the report server e-mail delivery extension for the service application named My RS Service App. Update the values for SMTP server name (<email server name>) and the FROM email alias (<your FROM email address>).

$app=get-sprsserviceapplication -Name "My RS Service App"
$emailCfg = Get-SPRSExtension -identity $app -ExtensionType "Delivery" -name "Report Server Email" | select -ExpandProperty ConfigurationXml 
$emailXml = [xml]$emailCfg 
$emailXml.SelectSingleNode("//SMTPServer").InnerText = “<email server name>”
$emailXml.SelectSingleNode("//SendUsing").InnerText = "2"
$emailXml.SelectSingleNode("//SMTPAuthenticate").InnerText = "2"
$emailXml.SelectSingleNode("//From").InnerText = '<your FROM email address>'
Set-SPRSExtension -identity $app -ExtensionType "Delivery" -name "Report Server Email" -ExtensionConfiguration $emailXml.OuterXml

In the above example if you did not know the exact name of the service application, you could rewrite the first statement to get the service application based on a search of the partial name. For example:

$app=get-sprsserviceapplication | where {$_.name -like " My RS Ser *"}

The following script will return the current configuration values for the report server e-mail delivery extension for the service application named “My RS Service App”. The first step sets the value of the variable $app to the object of the service application that has a name of "My RS Service App "

The second statement will Get the ‘Report Server Email’ delivery extension for the service application object in variable $app, and select the configurationXML

$app=get-sprsserviceapplication –Name "My RS Service App"
Get-SPRSExtension -identity $app -ExtensionType "Delivery" -name "Report Server Email" | select -ExpandProperty ConfigurationXml

Top

Get and set Properties of the Reporting Service Application Database

The following example first returns a list of the databases and properties so you can determine the database guid (ID) that you then supply to the set command. For a full list of the properties, use Get-SPRSDatabase | format-list.

get-SPRSDatabase | select id, querytimeout,connectiontimeout, status, server, ServiceInstance 

The following is an example of the output. Determine the ID for the database you want to modify and use the ID in the SET cmdlet.

• Id : 56f8d1bc-cb04-44cf-bd41-a873643c5a14

  QueryTimeout : 120

  ConnectionTimeout : 15

  Status : Online

  Server : SPServer Name=uetestb01

  ServiceInstance : SPDatabaseServiceInstance

Set-SPRSDatabase –identity 56f8d1bc-cb04-44cf-bd41-a873643c5a14 -QueryTimeout 300

To verify the value is set, run the GET cmdlet again.

Get-SPRSDatabase –identity 56f8d1bc-cb04-44cf-bd41-a873643c5a14 | select id, querytimeout,connectiontimeout, status, server, ServiceInstance

Top

List data extensions

The following example loops through each Reporting Services service application and lists the current data extensions for each.

$apps = Get-SPRSServiceApplication
foreach ($app in $apps) 
{
Write-host -ForegroundColor "yellow" Service App Name $app.Name
Get-SPRSExtension -identity $app -ExtensionType “Data” | select name,extensiontype | Format-Table -AutoSize
}

Example output:

• Name ExtensionType

  ---- -------------

  SQL Data

  SQLAZURE Data

  SQLPDW Data

  OLEDB Data

  OLEDB-MD Data

  ORACLE Data

  ODBC Data

  XML Data

  SHAREPOINTLIST Data

Top

Change and list subscription owners

See Use PowerShell to Change and List Reporting Services Subscription Owners and Run a Subscription.

Top

See Also

Concepts

Use PowerShell to Change and List Reporting Services Subscription Owners and Run a Subscription

Get Help SQL Server PowerShell

Other Resources

CodePlex SharePoint Management PowerShell scripts

How to Administer SSRS using PowerShell