T-SQL query to find the timezone offset of the SQL server

Here is a short T-SQL query to find the timezone offset of the SQL server.

SELECT 'UTC'+RIGHT(SYSDATETIMEOFFSET(),6) AS 'TimeZone Offset'

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

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.

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