Tuesday, April 15, 2014

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
IF EXISTS (SELECT * FROM sys.objects WHERE NAME = 'ConfigurationInfo')

 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'
DECLARE @DBName nvarchar(100)
RAISERROR('The database %s is not a report server database',16,1,@DBName)
Related Posts Widget For Blogger with ThumbnailsBlogger Templates

Saturday, April 12, 2014

Unable to connect to SQL Server '(local)'. The step failed.

One fine morning you go to your office as usual and in a happy mood, you start looking into your routine tasks. Till this time everything is fine and suddenly you come across an Job failure alert which says,
Date 4/14/2014 11:47:53 PM
Log Job History (SimpleJob)

Step ID 1
Server SANSLAB\SQL2008R2
Job Name SimpleJob
Step Name SimpleStep
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Unable to connect to SQL Server 'SANSLAB\SQL2008R2'.  The step failed.

The first thing you look at is, the connectivity to the server (because the message says so) and you find the server is contactable and also all the databases are up and online.

So now the question is from where the error is popping?
Open the job and its step and see what it is doing and you find every thing is fine including the syntax.
But the section "Database" is blank which is supposed to have a value.
That triggers something in your mind and a possible cause for the job failure

What next?
The database section should have a value and this is the cause for the job failure.
This usually happens if a user database is used within this section and it has been renamed or deleted.

So, the best practice is to
  • Choose a system database name for this section and use the name of user database in your syntax. It will help avoid the failures of this kind.
  • Before renaming or deleting a database, make sure it is not referenced anywhere.
By doing so, even if the job fails it will give some meaningful error for us to troubleshoot further.

Friday, April 4, 2014

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.

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.

SET @DBName ='SansSQL'
   THEN 'AUTO Close is enabled. Run the below statement to Disable Auto Close '+ 
     char(10) + '----------------------------------------------------------'+ char(10)+
   WHEN DATABASEPROPERTY ( @DBName, 'IsAutoClose') = 0
   THEN 'AUTO Close is Disabled'

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