T-SQL to find Job Name of Subscribed Reports

Here is an handy T-SQL script which gives information about your subscribed reports in SSRS.

USE ReportServer
GO
SELECT 
   SJ.name AS JobName
  ,Cat.Name AS ReportName
  ,Sub.Description AS SubscriptionDescription
  ,Cat.Path AS ReportPath
  ,Sub.DeliveryExtension AS SubscriptionDeliveryType
  ,Sub.LastStatus
  ,Sub.EventType
  ,SJ.date_created AS SubscriptionCreatedDate
  ,SJ.date_modified AS SubscriptionModifiedDate
FROM msdb.dbo.sysjobs SJ
INNER JOIN ReportSchedule RS ON SJ.name=CAST(RS.ScheduleID AS VARCHAR(max))
INNER JOIN Subscriptions Sub ON Sub.SubscriptionID=RS.SubscriptionID
INNER JOIN Catalog Cat ON Cat.ItemID=Sub.Report_OID

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

Guidelines for choosing antivirus software to run on the computers that are running SQL Server

These are the very general guidelines to help you decide which kind of antivirus software to run on the computers that are running Microsoft SQL Server and I personally recommend to perform some testing before and after the installation of antivirus to determine if there are any performance issues as the antivirus software will utilize some system resources to perform their duties.

To drill own, basically there are two kinds of servers, one is High Risk Servers which are generally exposed to public internet or which have open ports to the servers that are not behind firewall or which hosts file shares or HTTP services like IIS or Apache.
And the servers which do not meet the above criteria of High Risk servers will fall under the category of Low Risk Server although not always.

When you configure antivirus software on a server running SQL Server, make sure to exclude the following

File Extensions and Directories

• SQL Server database files
• .mdf
• .ndf
• .ldf
• SQL Server backup files
• .bak
• .trn
• Full-Text catalog files
• Default instance: Program Files\Microsoft SQL Server\MSSQL\FTDATA
• Named instance: Program Files\Microsoft SQL Server\MSSQL$instancename\FTDATA
• Trace Files
• .trc
• Audit files
• .sqlaudit
• Query files
• .sql
• Directory that holds Analysis Services Data and Temporary files
• Directory that holds Analysis Services Log files
• Analysis Services backup files

Processes

• SQLServr.exe - Process related to SQL Server Database engine
• ReportingServicesService.exe  - Process related to SQL Server Reporting Services
• MSMDSrv.exe  - Process related to SQL Server Analysis Services

We can also run antivirus software on a SQL Server cluster. For this we have make sure that the antivirus software we choose supports cluster. 

When running antivirus on cluster make sure to exclude 

• Q:\ (Quorum drive)
• C:\Windows\Cluster

Doing this improves the performance of the files and helps make sure that the files are not locked when the SQL Server service must use them. However, if these files become infected, the antivirus software cannot detect the infection. So if you suspect a virus infection then you have to scan the entire system without any exclusions.

Reference: http://support.microsoft.com/kb/309422

Report Builder 3.0 Error - Unable to connect to the server that is specified in the URL

Consider you built a report using Report Builder 3.0 and now you are deploying the same to your report server. Suddenly the Report Builder presents you with a error which says the below

Connection failed. Unable to connect to the server that is specified in the URL,'http://<servername>/<ReportServer>'. Make sure the server is running, the URL is correct, and you have permission to use the server.

Solution:
Make sure that the SQL Server Version is SQL Server 2008 R2 as Report Builder 3.0 does not support SQL Server 2008 or below.

SSRS Error - An Unexpected Error occurred in Report Processing (rsInternalError)

I was working on a SQL Server Reporting Services (SSRS) Report just a few minutes back and i thought my deployment of the report went fine and i can now view my deployed reports on the Report Manager site.
Assuming that everything went fine, I opened the Report Manager and there was a surprise awaiting for me.
And the surprise was, the Report Manager which was working fine till yesterday is now giving an error.
The error is:
An Unexpected Error occurred in Report Processing (rsInternalError)
Could not find a part of the path 'C:\Documents and Settings\SANSLAPTOP\ASPNET.SANSLAPTOP.000\Local Settings\Temp\brhx7gi1.tmp

Fix that worked for me:
I manually went into each folder structure that is displayed as part of the error message and found that in my case, the folder "Temp"  was missing in the file system.
I created this folder and went back to my report manager and refreshed the page and the report manager started to work as before.