how to use stored proc to drive data-driven subscription

I am wondering how to use a stored proc that, in the procedure, uses TEMP
TABLES to drive the parameters for a data driven subscriptions.
In other words, I want this stored procedure to return a list of e-mail
addresses to distribute the report to. This procedure gathers this list of
addresses, but in order to do so it needs to execute *another* stored
procedure, putting the data from that stored proc into a TEMP table.
When I run the proc in query analyzer it works fine (no surprise) but when I
tell RS to use it as the query for the subscription it gives me the 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) Get Online Help Invalid object name '#step1'.
I am setting up the data-drive subscription as follows:
exec rpt_misc_GetLostBillPayments_emailproc_sp
Here is a copy of the above proc:
<snip>
declare @.emails varchar(255)
set @.emails = 'mymail@.myaddress.com;'
create table #step1
(claim_id int,
bill_id int,
received_date_2 datetime,
modified_date datetime,
bill_amount float,
provider_claim_payment_id int,
provider_payment_created_date datetime)
insert into #step1
(claim_id,
bill_id,
received_date_2,
modified_date,
bill_amount,
provider_claim_payment_id,
provider_payment_created_date)
exec rpt_misc_getlostbillpayments_sp
select top 1 claim_id, @.emails as email from #step1
drop table #step1
Any suggestions?You should be able to do this through the SOAP API (read use rs.exe). The
CreateDataDrivenSubscription does not validate the fields you specify, so as
long as the field names returned at runtime match those used in the data
driven subscriptions' field references it should work.
The UI calls PrepareQuery before creating the subscription to get the fields
list. It sounds like you know what the fields are already, so you might be
able to skip this test. Note that this makes the subscription uneditable in
the UI.
-Lukasz
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"david boardman" <davidboardman@.discussions.microsoft.com> wrote in message
news:D5877C1D-596C-4E56-A9DF-98D4CB6000F4@.microsoft.com...
>I am wondering how to use a stored proc that, in the procedure, uses TEMP
> TABLES to drive the parameters for a data driven subscriptions.
> In other words, I want this stored procedure to return a list of e-mail
> addresses to distribute the report to. This procedure gathers this list
> of
> addresses, but in order to do so it needs to execute *another* stored
> procedure, putting the data from that stored proc into a TEMP table.
> When I run the proc in query analyzer it works fine (no surprise) but when
> I
> tell RS to use it as the query for the subscription it gives me the 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) Get Online Help Invalid object name '#step1'.
> I am setting up the data-drive subscription as follows:
> exec rpt_misc_GetLostBillPayments_emailproc_sp
>
> Here is a copy of the above proc:
> <snip>
> declare @.emails varchar(255)
> set @.emails = 'mymail@.myaddress.com;'
>
> create table #step1
> (claim_id int,
> bill_id int,
> received_date_2 datetime,
> modified_date datetime,
> bill_amount float,
> provider_claim_payment_id int,
> provider_payment_created_date datetime)
> insert into #step1
> (claim_id,
> bill_id,
> received_date_2,
> modified_date,
> bill_amount,
> provider_claim_payment_id,
> provider_payment_created_date)
> exec rpt_misc_getlostbillpayments_sp
>
> select top 1 claim_id, @.emails as email from #step1
> drop table #step1
>
> Any suggestions?