Stored procedure in SSRS and Report Manager

I built a stored procedure to test a process that I want to put in place with another report - this sp is very simple, the other won't be.  The whole thing works well in management studio and renders a report fine, but when the .rdl in the reports manager and view, I get an error.
***
An error has occurred during report processing.
Query execution failed for data set 'DataSet1'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors
***
Help?  Suggestions?
execute usr_sp_cxlalert
select * from omni_cancellation where year(cxldate) = year(getdate()) and month(cxldate) = month(getdate())

Open in new window

LVL 1
angelnjjAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HadushCommented:
If you are running both the select statment and store proc to get one dataset you have to use like UNION otherwise it will continue to give an error.  When you run SSMS it gives two result sets which means either you have to combine them if you want them in one result. If they are separate datasets then dataset1 will be only the store proc, and dataset2 will be your select statment.
0
angelnjjAuthor Commented:
I've split into two datasets, but my main problem - that they won't render when I run from the report manager - still happens...with the same error message.
expertsexchange.doc
0
HadushCommented:
It may be credential issue for the store proc.  Copy the report and deply with out the store proc dataset and check if you can view the report.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tone' ShelbyMicrosoft® Power BI, Power Apps & O365 Design, DevelopmentCommented:
Also, You may assure that the data source connection type is the same on report manager to assure the parameter passing is supported the same. This almost sounds like the sp is not recognizing the parameter passed from the report into the stored procedure or the are named the same? (you get the same message for both)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.