Friday, March 23, 2012
how to use stored proc to drive data-driven subscription
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?
Monday, March 19, 2012
How to use reportviewer control
Hi,
I have couple of reports designed and they are on the report server and those reports are driven using some strored procedures.
I am building a asp.net program and want to use the reportviewer control in it, and i was wondering if there is any way that i could reference the reports thats been created usiing BIDS and use them in report viewer, or do i have to build them from scratch.
Any ideas pls?
Regards
Karen
Yes, you can use those reports.
When you want to use local reports (RDLCs) you use ReportViewer.LocalReport but when you want to host reports rendered on a server you use ReportViewer.ServerReport, which has a ReportServerUrl property, etc.
Here is a good place to start. http://gotreportviewer.com/ -- you will find a demo of remote mode there...
>L<
|||Thanks a lot Lisa for your answer, but i have a another question.
I have added a reportviewer control to my project, set the report processing mode to remote and have also specified the report server url and the report path. but when i build my project and run it, i get a error message saying the request failed with HTTP Status 401: unauthorized,
which tells me that i need to set the credentials. so u please tell me how can i set them.
Regards,
Karen
|||Is ShowCredentialPrompts set true or false?
IOW: Do you see the prompts, and your values don't work, or are you not seeing the prompts?
If you want to impersonate a user programmatically (not with the prompts) here is a help topic with code showing you how:
http://msdn2.microsoft.com/en-us/library/microsoft.reporting.winforms.ireportservercredentials(vs.80).aspx
... if you put in the credentials, whether interactively or by code, and it does not work, you need to check whether the identity you're using has rights by configuring the report server instance...
>L<
|||ShowCredentialsPrompts is set to true, and i dont any prompts at all, and i am getting the error messge The request failed on page load.
Regards
Karen
|||Then I guess you should try setting the credentials in code, as shown in the help file... If that doesn't work it is something about how your report server is set up that should be independent of what we're discussing here...
>L<
|||but i am using webforms and this is what i have specified
Dim cache As System.Net.CredentialCache = New System.Net.CredentialCache
'Dim cache As New ServerReport
cache.Add(New Uri("http://192.168.0.235/ReportServer"), "Negotiate", New System.Net.NetworkCredential("Administrator", "abc", "abc"))
ReportViewer2.ServerReport.ReportServerCredentials = cache
but when i try to run it i am getting the following error
Unable to cast object of type 'System.Net.CredentialCache' to type 'Microsoft.Reporting.WebForms.IReportServerCredentials'
Regards
Karen
|||I'm terribly sorry, I seem to have gotten two threads confused here <s>. Yes, you are using webforms.
Your code doesn't look like what I see in this topic on specifying credentials: http://msdn2.microsoft.com/en-us/library/microsoft.reporting.webforms.ireportservercredentials(VS.80).aspx
The error you're getting bears out what I'm saying, because the topic I'm pointing you to is the topic on the
IReportServerCredentials Interface <s>. So try to follow what it says there, and make any web.config changes you need to make (that topic will point you here http://msdn2.microsoft.com/en-us/library/ms251661(VS.80).aspx for web config stuff).
>L<
|||
Lisa,
Thanks a lot for your help, the report viewer control works fine.
Regards,
Karen