Call or Reference a Stored Procedure from a published report

Hi All,
Got a report published that uses a stored procedure which is on a different
server in our Domain. I have the correct connection string info entered via
the Report Manager / Data Sources tab, but how and where does one reference
the SP to execute it? The SP does work in BIDS.
Thanx,
~SDDid you check if the user that reads the report has the execute permission
on the SP?
What error message are you getting?
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"iamscott" <iamscott@.discussions.microsoft.com> wrote in message
news:D3B5C2A0-49AC-4786-AE1C-68990FF487D1@.microsoft.com...
> Hi All,
> Got a report published that uses a stored procedure which is on a
> different
> server in our Domain. I have the correct connection string info entered
> via
> the Report Manager / Data Sources tab, but how and where does one
> reference
> the SP to execute it? The SP does work in BIDS.
> Thanx,
> ~SD
>|||The reference to the SP is in the report. If the report uses a shared
datasource then the datasource is used to get the connection and the report
RDL specifies the stored procedure. The rendering engine then executes the
SP. There is no way to force the SP to execute other than trying to run the
report.
But, it is also possible to embed the datasource into the report rather than
used a shared datasource. If that is the case and it is wrong or has some
issue with it then you will have problems. Most people use shared data
sources.
You say it has the correct connection information. What type of credentials
are you using. Are you securely storing the credentials or are you using
integrated security. If integrated then it will be using the windows
credentials of the user running the report which might not have correct
permissions for SQL Server. I always securely stored the credentials. I use
a SQL account that I have as a read only account which is given execute
permission on any necessary stored procedures. You can also do this with a
domain account but I prefer running in mixed mode and uses a SQL account.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"iamscott" <iamscott@.discussions.microsoft.com> wrote in message
news:D3B5C2A0-49AC-4786-AE1C-68990FF487D1@.microsoft.com...
> Hi All,
> Got a report published that uses a stored procedure which is on a
> different
> server in our Domain. I have the correct connection string info entered
> via
> the Report Manager / Data Sources tab, but how and where does one
> reference
> the SP to execute it? The SP does work in BIDS.
> Thanx,
> ~SD
>