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

Check AutoClose Status of a Database

For Setting up Database Mirroring, one of the important pre-requisite is to turn off AutoClose option on the database.
This script helps to check the status of the autoclose option on a database and will generate the script to disable autoclose if it is enabled.

DECLARE @DBName NVARCHAR(100)
SET @DBName ='SansSQL'
PRINT CASE WHEN DATABASEPROPERTY ( @DBName, 'IsAutoClose') = 1
   THEN 'AUTO Close is enabled. Run the below statement to Disable Auto Close '+ 
     char(10) + '----------------------------------------------------------'+ char(10)+
     'ALTER DATABASE '+ @DBName +' SET AUTO_CLOSE OFF'
   WHEN DATABASEPROPERTY ( @DBName, 'IsAutoClose') = 0
   THEN 'AUTO Close is Disabled'
  END

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

T-SQL to get Machine name from SQL Server Instance Name

How many of you use Central Management Server?
How many of you use CMS for just storing the list of servers?
How many of you use CMS for other activities other thank just storing the server names?

The list of servers registered under the CMS can be queried using the view "sysmanagement_shared_registered_servers" present in msdb database.

This will give the list of SQL Server Instance names, but when we require to get the Machine name from the SQL Server instance name, we can use this query to achieve it.

SELECT DISTINCT CASE WHEN CHARINDEX('\',server_name) = 0 THEN server_name
      ELSE SUBSTRING(server_name,1,CHARINDEX('\',server_name)-1) 
      END AS MachineName
FROM msdb.dbo.sysmanagement_shared_registered_servers