Getting error reports when a SQL Reporting Subscription Fails

UPDATE 2:

Either this never worked in the first place as I wanted, or MS has changed how they are doing things in SSRS 2008.

I now don’t see any of the errors in the tables I had been looking in, and in fact, the errors don’t show up in any of their logs either.  This is crap.

If you are like me, you don’t like the fact that the first time you realize that a data driven subscriptions in SQL Server Reporting Services has been failing is when someone comes up to you and says “Hey, I did XYZ and I never saw the server kick off an email.” 

Really?  Let me check…. oh, looks like it has been broken for god knows how long.  What the heck?

Well, I wrote a script to notify you when there are errors on the report server.

First, you need to setup Database Mail.  Expand Management, and pick Database Mail.  For my script I used the name “Email Profile” for the “Email Profile” get it?

Then create a trigger on the ExecutionLogStorage table like this:

USE [ReportServer]
GO
/****** Object:  Trigger [dbo].[ExecutionLogStorage_Insert]    Script Date: 05/13/2009 10:20:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[ExecutionLogStorage_Insert]
ON [dbo].[ExecutionLogStorage]
AFTER INSERT
AS
BEGIN
    DECLARE @sStatus AS NVARCHAR(32)
    SET @sStatus = (SELECT Status FROM inserted)
    
    IF(@sStatus <> 'rsSuccess')
    BEGIN
        DECLARE @sReportName AS NVARCHAR(425)
        DECLARE @sPath AS NVARCHAR(425)
        DECLARE @ReportId AS UNIQUEIDENTIFIER
        DECLARE @sUserName AS NVARCHAR(260)
        DECLARE @sParameters AS NVARCHAR(4000)
        DECLARE @dTimeEnd AS DATETIME
        DECLARE @iTimeProcessing as int
        
        
        SELECT
          @sReportName = Name,
          @sPath =PATH,
          @ReportId = ReportId,
          @sUserName = username,
          @sParameters = (select CAST(ISNULL(ELS.Parameters,'') AS NVARCHAR(4000)) from ExecutionLogStorage ELS Where LogEntryId = Inserted.LogEntryId),
          @dTimeEnd = TimeEnd,
          @iTimeProcessing = ISNULL(TimeProcessing,0)
        FROM inserted INNER JOIN [Catalog]
        ON inserted.ReportId = CATALOG.[ItemID]
        
        DECLARE @sBody VARCHAR(8000)
        SET @sBody = 'Error with SQL Report '
                    + CAST(@sReportName AS VARCHAR(100))
                    + ' at path '
                    + CAST (@sPath AS VARCHAR(250))
                    + ' with ReportId '
                    + CAST(@ReportId AS VARCHAR(100))
                    + ' and username '
                    + CAST(@sUserName AS VARCHAR(100))
                    + ' Parameters '
                    + CAST(@sParameters  AS VARCHAR(5000))
                    + ' End time '
                    + CAST(@dTimeEnd AS VARCHAR(50))
                    + ' Status '
                    + CAST(@sStatus AS VARCHAR(50))
                    + ' Time Processing '
                    + CAST(@iTimeProcessing AS VARCHAR(50));
        
        DECLARE @sSubject VARCHAR(500)
        SET @sSubject = 'Error With Report ' + @sReportName
        
        EXEC msdb.dbo.sp_send_dbmail
         @profile_name = 'Email Profile',
         @recipients = 'email@address.com',
         @body =  @sBody  ,
         @subject = @sSubject;

    END
    
END

Presto!

 

UPDATE: Added logic to get the status and parameters.

 

Finding if SP Parameters are Nullable

I’m working on something where I wanted to know if some parameters to a SQL Server Stored Procedure were nullable (or, in otherwords, if they had a default value).

The API I was working with provided a way to find info about the SP, but the isNullable value was never accurate.

I tried working with with the MS DAABs which have a way to fetch SP parameter info, but this method also produced faulty information.

Looking into the code of the DAAB, it was internally calling:

Dim command As SqlCommand

SqlCommandBuilder.DeriveParameters(command)

Somewhere I came across some code for the DeriveParameters method (I think from the Mono project maybe?) which showed it calling this system SP:

sp_procedure_params_rowset

Which returns output fields,:

PARAMETER_HASDEFAULT

IS_NULLABLE

But, again, neither of these appear correctly.

However, I finally came across this thread where a MSFT poster indicates that this data is simply not available:

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/900756fd-3980-48e3-ae59-a15d7fc15b4c/

Instead, you need to parse the SP definition to see what the default parameter value is, which is a pain, but at least I know why these other methods kept not working.

 

Avoid Overwriting Modified SPs With Code Generation

One of the challenges of using code generation is to avoid overwriting your custom changes when you re-generate the code later.

This is a trick I am using to avoid overwriting my stored procedures if I update them manually.  The trick is to include a comment indicating that the SP has been code generated, and then to remove that comment if you update the SP.  So lets say that you generate:

CREATE PROCEDURE OrderDetails_Get
@OrderId as int
AS
-- CODE GENERATED
SELECT * FROM
OrderDetails
WHERE
OrderId = @OrderId

and you made some customizations to the SP so it now looks like this:

CREATE PROCEDURE OrderDetails_Get
@OrderId as int
AS
SELECT * FROM
OrderDetails
INNER JOIN
Orders ON
Orders.OrderId = OrderDetails.OrderId
WHERE
OrderId = @OrderId

The following code will help you when generating your SPs to avoid overwriting this change:

IF EXISTS(
  SELECT [definition] AS objectText
  FROM sys.sql_modules
  WHERE [object_id] = OBJECT_ID(N'OrderDetails_Get', 'P')
  AND definition LIKE '%CODE GENERATED%')
BEGIN
   -- overwrite is ok
END

 

SQL Server encountered error 0x80070422 (FIXED)

Today we started getting this error when trying to do full text search with SQL Server 2008.

Msg 30046, Level 16, State 1, Procedure CodeMaster_GetCostCodesByCriteria_FullText, Line 47

SQL Server encountered error 0x80070422 while communicating with full-text filter daemon host (FDHost) process. Make sure that the FDHost process is running. To re-start the FDHost process, run the sp_fulltext_service ‘restart_all_fdhosts’ command or restart the SQL Server instance.

Google has NOTHING on this problem, except 1 other guy reporting it to MS, who tells him to go to some other forum.

Great.

If I find anything I will post it.

UPDATE:

Ok I figured this out.

The problem was that the actual FTS service was disabled (but not just that, so read on).

So go into your services and make sure that this one is running:

SQL Full-text Filter Daemon Launcher (MSSQLSERVER)

But even after I set this up running things weren’t working.  More research led me to find out that there was an issue with the fact that we run our SQL Server under a domain account and the full text search was not running as that same user.

So I configured this service to run as the same user and restarted the service.

Then in SQL Server I ran this:

EXEC sp_fulltext_service 'restart_all_fdhosts'

to restart things.

I also right clicked on the FTS catalog (under databasename/Storage/Full Text Catalog), picked properties, and then selected the option to rebuild catalog.

 

SQL Reporting Services Subscription Ownership

We utilize data driven subscriptions in SQL Server Reporting Services (SSRS) to automate several reports and their distribution to a group of people.  For example maybe when a work order is created in the database a report with info about that order is emailed to everyone who will have to be involved in fulfiling the order. 

We realized that some of these reports were not going out.  By looking at the log files (located at ) it became clear that we were hitting a permissions issue:

ReportingServicesService!library!4!09/25/2008-08:15:04:: 
e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
The permissions granted to user 'MYDOMAINsomeuser' are insufficient for performing this operation., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
The permissions granted to user 'MYDOMAINsomeuser' are insufficient for performing this operation.

Ok that seemed to make sense.  The user “someuser” had left our company and so I’m sure his account was disabled.  After looking around at the report, it’s definition, the subscriptions, the data access, nothing was tied to this old employee. 

But…. the subscription itself still is.

The downside of this is that there is no way to change who “owns” the subscription.

However, you can make the changes manually in the database with the following code:

DECLARE @OldUserID uniqueidentifier
DECLARE @NewUserID uniqueidentifier

SELECT @OldUserID = UserID FROM dbo.Users
WHERE UserName = 'MYDOMAINsomeuser'

SELECT @NewUserID = UserID FROM dbo.Users
WHERE UserName = 'MYDOMAINnewuserhere'

UPDATE dbo.Subscriptions
SET OwnerID = @NewUserID
WHERE OwnerID = @OldUserID

Presto, your subscription has a new owner and will once again start running correctly.

UPDATE: I am going to try to work on something that will monitor the subscriptions and notify me if one of them fail.  Check back later.

NHibernate Tutorials

I have been poking around with NHibernate for a while now, but I am actually writing a small app with it at the moment.

During my time getting it up and running, I came across a few well written tutorials that I want to catalog here in case I want to return to them at some point for more in depth reading.

Great NHibernate Faq:
http://www.tobinharris.com/2007/2/3/nhibernate-faq

Fluent Interface for NHibernate:
http://blogs.hibernatingrhinos.com/…nhibernate.aspx

Alan Northam’s Tutorials:
http://devlicio.us/blogs/alan_northam/…part-i.aspx
http://devlicio.us/blogs/alan_northam/…part-ii.aspx
http://devlicio.us/blogs/alan_northam/…part-iii.aspx
http://devlicio.us/blogs/alan_northam/…part-iv.aspx

 

A connection that was expected to be kept alive was closed by the server

Has anyone been getting these errors when serving up SQL Reporting Services Reports?

A connection that was expected to be kept alive was closed by the server

All of a sudden we have been getting a lot of these, and I am not sure at the moment what is causing them.

We have 2 webservers that are hosting the reportviewer control, which loads up reports from a single reporting server.

This guy suggested that this problem was related to some 10 minute timeout, but I haven’t been able to replicate his 10 minute problem.

Some other people are talking about overrideing the webservice proxy classes to set KeepAlive = false, but we have no webservice proxy to override as we are using the reportviewer.

Update:

This guy is talking about changing the SSRS session timeout.

Could “rc:Toolbar=false” be the problem?

Update 2:
Looks like this was all caused by the introduction of an IDP by our network guys.

 

Excluding weekends from a SQL date range

Recently I wanted to find the time between 2 datetimes, but I wanted to exclude weekends.  To further complicate matters, I wanted to know hours, not just days.

The “standard” way to do stuff like this is to build out a calendar table that has every date for the next 50 years, along with info like “IsWeekend” or “IsHoliday”.

But I found some snippets around that I hoped to use to my advantage, and what I came up with works very well:

DECLARE @start DATETIME
DECLARE @end DATETIME

SET @start = '3/7/08 12:50 pm'
SET @end = '3/11/08 1:50 am'

SELECT (DateDiff(hh, @start, @end) - DateDiff(ww, @start, @end)*2*24)/24.0

I won’t spend a lot of time explaining why it works, but it basically counts the number of weekend “jumps” that are crossed between the 2 dates, and subtracts accordingly.