troubleshooting Question

Stored procedure not working in Report

Avatar of allelopath
allelopath asked on
SSRSSSIS
11 Comments2 Solutions974 ViewsLast Modified:
I'm looking for ideas as to why executing a certain stored procedure as a dataset does not work in a SSRS Report.

By does not work, I mean no data is returned.

In BIDS by going into the Shared Dataset Properties -> Query ->  Query Designer -> ! (which runs the query). The column headers appear and data appear.

However, in the report itself, no data is returned. This stored procedure should return one row of data, no more no less. The dataset 'knows' the columns returned by the sproc in that the fields in the dataset properties are shown correctly. It has ` parameter, and I've tried hard-coding it, but makes no difference. When I look in SQL Server profiler on the database/server, indeed nothing happens when running the report.

I have another dataset in the same report, running another sproc and it runs correctly. This is sproc returns many rows. Is there something special about calling an sproc that returns only 1 row?
ASKER CERTIFIED SOLUTION
jonnidip
Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros