SSRS data driven subscription with dynamic pivot table as the return value

Question

I have a rather complex message queue system where in the end I do a dynamic pivot to pass back the TO as well as all the report parameters. I'm using SSRS as my mail message format provider and data driven subscriptions to auto deliver my e-mails.

My problem is that when I am using the UI to set up my data driven subscription, SSRS prevents me from moving on with an error.

The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. (rsCannotPrepareQuery)

Here's a basic rundown of what I'm doing. I have these tables for my message queuing system.

CREATE TABLE MessageParameters 
(
    SequenceId              int                 IDENTITY(1,1) NOT NULL,
    MessageQueueId          int                 NOT NULL,
    ParameterName           varchar(50)         NOT NULL,
    ParameterValue          varchar(300)        NOT NULL,

    CONSTRAINT PK_MessageParameters PRIMARY KEY CLUSTERED(SequenceId)
)

CREATE TABLE MessageRecipients 
(
    SequenceId              int                 IDENTITY(1,1) NOT NULL,
    MessageQueueId          int                 NOT NULL,
    ProfileId               int                 NOT NULL,

    CONSTRAINT PK_MessageRecipients PRIMARY KEY CLUSTERED(SequenceId)
)

CREATE TABLE MessageTypes 
(
    Id                      int                 NOT NULL,
    ReportName              varchar(50)         NOT NULL,

    CONSTRAINT PK_MessageTypes PRIMARY KEY CLUSTERED(Id)
)

CREATE TABLE MessageStatus 
(
    Id                      int                 NOT NULL,
    StatusName              varchar(50)         NOT NULL,

    CONSTRAINT PK_MessageStatus PRIMARY KEY CLUSTERED(Id)
)

CREATE TABLE MessageQueue
(
    SequenceId              int                 IDENTITY(1,1) NOT NULL,
    MessageType             int                 NOT NULL,
    [Status]                int                 DEFAULT(0) NOT NULL,
    Created                 datetime            DEFAULT(GETDATE()) NOT NULL,
    Modified                datetime            DEFAULT(GETDATE()) NOT NULL,

    CONSTRAINT PK_MessageQueue PRIMARY KEY CLUSTERED(SequenceId)
)

Fairly simple to understand. Each message can have more than one parameter and more than one recipient.

Then, I have a sproc that gets me all of the pending messages in the queue by message type.

CREATE PROCEDURE GetPendingMessageRecipients 
    @MessageType        int
AS
BEGIN


    SELECT 
        SequenceId
    INTO
        #Messages
    FROM
        MessageQueue
    WHERE
        MessageType = @MessageType
    AND
        [Status] = 0

    --###############################################################
    --###############################################################

    DECLARE @ParameterList varchar(max)
    DECLARE @To varchar(max)

    SELECT 
        r.MessageQueueId,
        ISNULL(@To, '') + p.EmailAddress + ',' AS [TO]
    INTO
        #Recipients
    FROM 
        MessageRecipients r
    JOIN
        WebProfiles p
    ON 
        r.ProfileId = p.Id
    JOIN
        #Messages m
    ON
        r.MessageQueueId = m.SequenceId

    --remove the last comma
    UPDATE 
        #Recipients
    SET
        [TO] = LEFT([TO], LEN([TO]) - 1)

    --###############################################################
    --###############################################################

    SELECT 
        p.MessageQueueId,
        p.ParameterName, 
        p.ParameterValue 
    INTO 
        #Params 
    FROM 
        MessageParameters p 
    JOIN
        #Messages m
    ON
        p.MessageQueueId = m.SequenceId

    INSERT INTO #Params
    SELECT
        MessageQueueId, 'TO', [TO]
    FROM
        #Recipients

    --###############################################################
    --###############################################################

    SELECT @ParameterList = ISNULL(@ParameterList, '') +
        '[' + ParameterName + '],'
    FROM 
        #Params
    GROUP BY
        ParameterName

    -- Remove last comma
    SET @ParameterList = LEFT(@ParameterList, LEN(@ParameterList) - 1)

    DECLARE @Query varchar(max)
    SET @Query = 'SELECT * ' +
        'FROM #Params ' +
        'PIVOT ( ' +
        '   max([ParameterValue]) ' +
        '   for [ParameterName] ' +
        '   in (' + @ParameterList + ') ' +
        ') AS pvt'

    EXEC (@Query)


END

It works like a champ inside management studio. I get the messageid, the recipients in a comma delimted list as the TO field, and then all the report parameters with the correct field names. But, SSRS doesn't like it one bit when I try to enter it as my recipient data source.

Answer 1

If you put

SET FMTONLY OFF

at the top of your stored procedure, SSRS skips the validation so that you can use temp tables in your query.