March 2014 | SansSQL

Wednesday, March 5, 2014

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

Tuesday, March 4, 2014

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

Monday, March 3, 2014

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