No Display Output using Stored Procedure

I call a stored procedure from the RS Data area and all data is retrieved on
the browse area when I run the procedure in the data window. I have
manually listed all fields in the Dataset->Fields window and drag them into
the Layout screen.
When I try to preview the report it seems to load the rows (ie 7 pages are
shown as loaded) but doesn't display any data, only the headings show with
and blank body section. If a put a string literal in one field it appears
on each row of the report but all other fields are blank.
I've looked through Output Options without any joy...Could you please post the rdl and ddl for a stored procedure that goes
against any of the sample databases so I can investigate?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"spamh8ers" <spamh8ers@.discussions.microsoft.com> wrote in message
news:2C2EC175-8681-42F9-A398-CAF83DEE765C@.microsoft.com...
> Does anyone have a solution for this yet? I have similar issues with
> creating new reports. If I start the the report with text query like
Select
> SalesOrder from SalesOrders, the report runs with no problem but when I
> change to a stored procedure, I get pages of empty report body.
> Ideas or workarounds?
> "news.microsoft.com" wrote:
> > I call a stored procedure from the RS Data area and all data is
retrieved on
> > the browse area when I run the procedure in the data window. I have
> > manually listed all fields in the Dataset->Fields window and drag them
into
> > the Layout screen.
> >
> > When I try to preview the report it seems to load the rows (ie 7 pages
are
> > shown as loaded) but doesn't display any data, only the headings show
with
> > and blank body section. If a put a string literal in one field it
appears
> > on each row of the report but all other fields are blank.
> >
> > I've looked through Output Options without any joy...
> >
> >
> >

NH: Best Practices Approach - call stored proc - or run it via linked server?

what pro's cons would there be to having a linked server run a local stored proc against another sql server or create that stored proc on that other sql server and call it from there in the c# code.

i would think that calling the stored proc would be more efficient that running a linked server - but please let me know your thoughts. I'm not sure i can have permission to add a stored proc on that server, so possibly the linked server is the only solution - but if i can put a stored proc on that server should i?

thanks.

Jeff

anyone?|||

One obvious thing to keep in mind is the performance. Depending on your network the executiion time could be different.

Now I am not sure if I understand your question. If you do not have permission to create the stored proc on the original server you would still need access to the underlying tables if you want to access them through server B, irrespective of whether its via stored proc or directly T-SQL.

|||

well typically I associate using a linked server to pull data from something using openquery such as ibm as400 so my question is do i get access to put the stored proc on the remote sql server (not one of my groups sql servers), or do i ask for setting up a linked server to it and execute the stored proc from my local sql server using the linked server approach.

either way?
get access to put the stored proc right on the sql server and call it in c#?
use the linked server and call it in c#

Just looking for some reason why one way may be better than another, primarily performance, and reducing putting in another possible point of failure.

thanks,

JB

|||

info@.learnbartending.com:

Just looking for some reason why one way may be better than another, primarily performance, and reducing putting in another possible point of failure.

Those are exactly the reasons why you might want to put the proc directly where the data is.