T-SQL query to find if a Report Server Database is configured in Native or Sharepoint Integration mode

Here is a T-SQL Query to find if a Report Server Database is configured in Native or Sharepoint Integration mode.

USE ReportServer$SQL2008R2 --Change the Database Name
GO
IF EXISTS (SELECT * FROM sys.objects WHERE NAME = 'ConfigurationInfo')
BEGIN

 DECLARE @Result nvarchar(max)
 SELECT @Result = Value FROM ConfigurationInfo
 WHERE NAME = 'SharePointIntegrated'
 PRINT CASE WHEN @Result= 'False' THEN 'The specified Report Server Database "'+ DB_NAME() + '" is configured in "NATIVE" mode'
      WHEN @Result = 'True' THEN 'The specified Report Server Database "'+ DB_NAME() + '" is configured in "SHAREPOINT INTEGRATION" mode'
    END
END
ELSE
BEGIN
DECLARE @DBName nvarchar(100)
SELECT @DBName = DB_NAME()
RAISERROR('The database %s is not a report server database',16,1,@DBName)
END

Location of SSRS config files - Sharepoint integration mode

When SSRS is configured in Sharepoint Integration Mode then the config files reside in a different location when compared to the normal installation.

The location where these files can be found are
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\WebServices\Reporting

The below link provides a description of configuration files used for a SharePoint mode report server.
http://technet.microsoft.com/en-us/library/ms155866.aspx

Error while Provisioning Reporting Services Subscriptions in sharepoint

When trying to create subscriptions for SQL Server Reporting Services when hosted in SharePoint Integration mode , you might get an error which says

“The EXECUTE permission was denied on the object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.”

To Fix this issue,

1. Login to Sharepoint central administration and navigate to the “Manage Reporting Services Application” page
   
   
2. Click “Provision Subscriptions and Alerts” 
3. Click “Download Script” and this will download a file named “SSRS-GrantRights.sql”
4. Execute the script on your SharePoint database server

Configure e-Mail for a Reporting Services Service Application – Sharepoint Integration mode

Here are the steps to configure STMP/e-mail for SSRS Service application in SharePoint integration mode.

1. In SharePoint Central Administration, click the Application Management
   
2. In the Application Management page, under Service Applications group, click Manage service applications.
   
3. In the list of configured applications, click on the name of your Reporting Services service application (SSRS).
   
4. Click E-mail Settings on the Manage Reporting Services Application page.
   
5. Select Use SMTP server In the Outbound SMTP server box, type the name of an SMTP server.In the From address box, type an e-mail address. This e-Mail address will be used as the sender of the e-mail alert.
   
6. Click OK