SansSQL

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
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
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

Message
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.
http://technet.microsoft.com/en-us/library/ms155866.aspx

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