| title | PowerShell cmdlets for Reporting Services SharePoint Mode | Microsoft Docs |
|---|---|
| ms.custom | |
| ms.date | 03/06/2017 |
| ms.prod | sql-server-2014 |
| ms.reviewer | |
| ms.technology | reporting-services-native |
| ms.topic | conceptual |
| ms.assetid | 7835bc97-2827-4215-b0dd-52f692ce5e02 |
| author | maggiesMSFT |
| ms.author | maggies |
| manager | kfile |
When you install [!INCLUDEssCurrent] [!INCLUDEssRSnoversion] SharePoint mode, PowerShell cmdlets are installed to support report Servers in SharePoint mode. The cmdlets cover three categories of functionality.
-
Installation of the [!INCLUDEssRSnoversion] SharePoint shared service and proxy.
-
Provisioning and management of [!INCLUDEssRSnoversion] service applications and associated proxies.
-
Management of [!INCLUDEssRSnoversion] features, for example extensions and encryption keys.
| [!INCLUDEapplies] [!INCLUDEssRSnoversion] SharePoint Mode |
This topic contains the following:
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. In the following cmdlet summaries, the references to service application 'databases', refer to all of the databases created and used by a [!INCLUDEssRSnoversion] 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:
-
[!INCLUDEssRSnoversion] SharePoint mode is not installed and therefore the [!INCLUDEssRSnoversion] 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/library/ee806878.aspx).
-
Click the Start button
-
Click the Microsoft SharePoint Products group.
-
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
The following table contains the PowerShell cmdlets for the [!INCLUDEssRSnoversion] SharePoint shared service.
| Cmdlet | Description |
|---|---|
| Install-SPRSService | Installs and registers, or uninstalls, the [!INCLUDEssRSnoversion] shared service. This can be done only on the machine that has an installation of SQL Server [!INCLUDEssRSnoversion] in SharePoint mode. For installation, two operations occur: 1) The [!INCLUDEssRSnoversion] service is installed in the farm. 2) The [!INCLUDEssRSnoversion] service instance is installed to the current machine. For Uninstallation, two operations occur: 1) The [!INCLUDEssRSnoversion] service is uninstalled from the current machine. 2) The [!INCLUDEssRSnoversion] service is uninstalled from the farm. NOTE: If there are any other machines in the farm that have the [!INCLUDEssRSnoversion] service installed, or if there are still [!INCLUDEssRSnoversion] service applications running in the farm, a warning message is displayed. |
| Install-SPRSServiceProxy | Installs and registers, or uninstalls, the Reporting Services service proxy in the SharePoint farm. |
| Get-SPRSProxyUrl | Gets the URL(s) for accessing the [!INCLUDEssRSnoversion] service. |
| Get-SPRSServiceApplicationServers | Gets all servers in the local SharePoint farm that contain an installation of the [!INCLUDEssRSnoversion] shared service. This cmdlet is useful for [!INCLUDEssRSnoversion] upgrades, to determine which servers run the shared service and therefore need to be upgraded. |
The following table contains the PowerShell cmdlets for [!INCLUDEssRSnoversion] service applications and their associated proxies.
| cmdlet | Description |
|---|---|
| Get-SPRSServiceApplication | Gets one or more [!INCLUDEssRSnoversion] service application objects. |
| New-SPRSServiceApplication | Create a new Reporting Services service application and associated databases. LogonType Parameter: Specifies if the report server uses the SSRS Application Pool account or a SQL Server login to access the report server database. It can be one of the following: 0 Windows Authentication 1 SQL Server 2 Application Pool Account (default) |
| Remove-SPRSServiceApplication | Removes the specified Reporting Services service application. This will also remove the associated databases. |
| Set-SPRSServiceApplication | Edits the properties of an existing Reporting Services service application. |
| New-SPRSServiceApplicationProxy | Creates a new Reporting Services service application proxy. |
| Get-SPRSServiceApplicationProxy | Gets one or more [!INCLUDEssRSnoversion] service application proxies. |
| Dismount-SPRSDatabase | Dismounts the service application databases for a [!INCLUDEssRSnoversion] service application. |
| Remove-SPRSDatabase | Remove the service application databases for a [!INCLUDEssRSnoversion] service application. |
| Set-SPRSDatabase | Sets the properties of the databases associated to a [!INCLUDEssRSnoversion] service application. |
| Mount-SPRSDatabase | Mounts databases for a [!INCLUDEssRSnoversion] service application. |
| New-SPRSDatabase | Create new service application databases for the specified [!INCLUDEssRSnoversion] service application. |
| Get-SPRSDatabaseCreationScript | Outputs the database creation script to the screen for a [!INCLUDEssRSnoversion] service application. You can then run the script in SQL Server Management Studio. |
| Get-SPRSDatabase | Gets one or more [!INCLUDEssRSnoversion] service application databases. Use the command to get the ID of service application database so you can use the Set-SPRSDatabase comdlet to modify properties, for example the querytimeout. See the example in this topic, Get and set properties of the Reporting Servicea application database, for example database timeout. |
| Get-SPRSDatabaseRightsScript | Outputs the database rights script to the screen for a [!INCLUDEssRSnoversion] service application. It will prompt for desired user and database then returns transact SQL you can run to modify permissions. You can then run this script in SQL Server Management Studio. |
| Get-SPRSDatabaseUpgradeScript | Outputs a database upgrade script to the screen. The script will upgrade [!INCLUDEssRSnoversion] service application databases to the database version of the current [!INCLUDEssRSnoversion] installation. |
| Cmdlet | Description |
|---|---|
| Update-SPRSEncryptionKey | Updates the encryption key for the specified Reporting Services service application and re-encrypts its data. |
| Restore-SPRSEncryptionKey | Restores a previously backed up encryption key for a Reporting Services service application. |
| Remove-SPRSEncryptedData | Delete the encrypted data for the specified Reporting Services service application. |
| Backup-SPRSEncryptionKey | Backs up the encryption key for the specified Reporting Services service application. |
| New-SPRSExtension | Registers a new extension with a Reporting Services service application. |
| Set-SPRSExtension | Sets the properties of an existing Reporting Services extension. |
| Remove-SPRSExtension | Removes an extension from a Reporting Services service application. |
| Get-SPRSExtension | Gets one or more [!INCLUDEssRSnoversion] extensions for a [!INCLUDEssRSnoversion] service application. Valid values are: Delivery DeliveryUI Render Data Security Authentication EventProcessing ReportItems Designer ReportItemDesigner ReportItemConverter ReportDefinitionCustomization |
| Get-SPRSSite | Gets the SharePoint sites based on whether the "ReportingService" feature is enabled. By default, sites that enable the "ReportingService" feature are returned. |
Return a list of cmdlets that contain 'SPRS' in the name. This will be the full list of [!INCLUDEssRSnoversion] 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 [!INCLUDEssRSnoversion] SharePoint service and service proxy.
Install-SPRSService Install-SPRSServiceProxy Start the [!INCLUDEssRSnoversion] 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" In addition to the following samples, see the section "Windows PowerShell Script" in the topic Windows PowerShell script for Steps 1-4.
This sample script completes the following tasks:
-
Create a Reporting Services service application and proxy. The script assumes the application pool "My App Pool" already exists.
-
Add the proxy to the default proxy group
-
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)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 the SMTP server (<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 " ssrs_testapp *"} The following script will return the current configuration values for the report server e-mail delivery extension for the service application named "Reporting Services Application". 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 "Reporting Services Application"
Get-SPRSExtension -Identity $app -ExtensionType "Delivery" -Name "Report Server Email" | Select -ExpandProperty ConfigurationXml You can also rewrite the above two statements as one:
Get-SPRSServiceApplication -Name "Reporting Services Application" | Get-SPRSExtension -ExtensionType "Delivery" -Name "Report Server Email" | Select -ExpandProperty ConfigurationXml 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, ServiceInstanceThe 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-a873643c5a14QueryTimeout : 120ConnectionTimeout : 15Status : OnlineServer : SPServer Name=uetestb01ServiceInstance : 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 The following example loops through each [!INCLUDEssRSnoversion] 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 DataSQLAZURE DataSQLPDW DataOLEDB DataOLEDB-MD DataORACLE DataODBC DataXML DataSHAREPOINTLIST Data
See Use PowerShell to Change and List Reporting Services Subscription Owners and Run a Subscription.
Use PowerShell to Change and List Reporting Services Subscription Owners and Run a Subscription
CheckList: Use PowerShell to Verify PowerPivot for SharePoint
CodePlex SharePoint Management PowerShell scripts