SansSQL

Tuesday, September 2, 2014

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
Related Posts Widget For Blogger with ThumbnailsBlogger Templates

Thursday, August 28, 2014

T-SQL to get running sessions and its progress details

Here is a handy T-SQL to get running sessions and its progress details.

SELECT 
  ExecSessions.session_id AS SessionID
 ,ExecRequests.blocking_session_id AS BlockingSessionID
 ,db_name(ExecRequests.database_id) AS DatabaseName
 ,ExecSessions.login_name AS LoginName
 ,ExecSessions.memory_usage AS MemoryUsage
 ,CONVERT(dec(6,3), (ExecRequests.wait_time/60000.00)) AS [WaitTime (mins)]
 ,ExecRequests.[status] AS [Status]
 ,ExecRequests.percent_complete AS PercentComplete
 ,[Text]
FROM sys.dm_exec_sessions AS ExecSessions INNER JOIN sys.dm_exec_requests AS ExecRequests 
ON ExecSessions.session_id = ExecRequests.session_id
CROSS APPLY sys.dm_exec_sql_text (sql_handle) 
WHERE ExecSessions.status = 'running' AND ExecSessions.session_id <> @@SPID
ORDER BY ExecSessions.session_id

Friday, August 22, 2014

A connection was successfully established with the server, but then an error occurred during the login process.

Recently I came across an issue when connecting to SQL Server instance using SQL Authentication.
The error says,
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error:0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error:233)


To fix the issue, Disable "Shared Memory" protocol in the SQL Server Configuration Manager.


To find the status of SQL Server Protocols using T-SQL, refer to the below link
http://www.sanssql.com/2011/04/t-sql-query-to-find-sql-server.html

Monday, June 30, 2014

The connected user is not an Analysis Services server administrator

Consider a situation where you have installed the SQL Server Analysis Services and during installation, you forget to add the Analysis Services server administrator.
When you connect to the analysis services and try to do some administrative tasks then you are presented with the error "The connected user is not an Analysis Services server administrator."
So now you do not have administrative privilege and no one is added into this rights and how to fix it?
  • Start SQL Server Management Studio as administrator >> "Run as Administrator" 
  • Go to "Security" Page
  • Add user to the "Server Administrators" group

Tuesday, May 27, 2014

Reporting Services Error - The report server installation is not initialized. (rsReportServerNotActivated)

Recently, I had a task for restoring the Reporting Services database from a live environment to a test environment.
Right after the successful restore, I tried opening the Report Manager website and I was presented with the error "The report server installation is not initialized. (rsReportServerNotActivated)"

This is because of the mismatch in the encryption key.
To fix this, either restore the encryption key from the live environment or delete the encryption key from the restored reporting services database.
The restoration or deletion of encryption key can be done by using the Reporting Services Configuration Manager.