Why doesn't Data Driven Subscription in SSRS 2005 like my Stored Procedure?

Question

I'm trying to define a Data Driven Subscription for a report in SSRS 2005.

In Step 3 of the set up you're asked for:

" a command or query that returns a list of recipients and optionally returns fields used to vary delivery settings and report parameter values for each recipient"

This I have written and it returns the data without a hitch. I press next and it rolls onto the next screen in the set up which has all the variables to set for the DDS and in each case it has an option to "Select Value From Database"

I select this radio button and press the drop down. No fields are available to me.

Now the only way I could vary the number of parameters returned by the SP was to have the SP write the SQL to an nvarchar variable and then at the end execute the variable as sql. I have tested this in the Management Studio and it returns the expected fields. I even named them after the fields in SSRS but the thing won't put the field names into the dropdowns.

I've even taken the query body out of the Stored Proc, verified it in SSRS and then tried that. It doesn't work either.

Can anyone shed any light into what I'm doing wrong?

Answer 1

You may need to start your stored proc with something like this:

CREATE PROCEDURE [GetRecipients]
AS

SET NOCOUNT ON
If 1=0
BEGIN
Select CAST(NULL as nvarchar(50)) as RecipientEmail,
CAST(NULL as integer) as  Param1, 
CAST(NULL as nvarchar(10)) as Param2,
CAST(NULL as DATETIME) as Param3
END

... insert your code here ...

End;  

This was needed for a procedure I used as a data source in SSIS that used temp tables. The Select at the top with the format of the final output is never run due to the If contstruct, but it allows SSIS (and possibly SSRS) to see and derive the metadata for the output. I believe that this is due to SSIS and SSRS looking for the first select in your code to try and derive the metadata.