Solved

Stored procedure in SSRS and Report Manager

Posted on 2009-07-02
4
674 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:angelnjj
  • 2
4 Comments
 
LVL 8

Expert Comment

by:Hadush
ID: 24764889
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
 
LVL 1

Author Comment

by:angelnjj
ID: 24765082
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
 
LVL 8

Accepted Solution

by:
Hadush earned 250 total points
ID: 24766032
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
 
LVL 9

Expert Comment

by:Tone' Shelby
ID: 24775025
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Reporting 8 82
SSRS Dynamic Column Names 3 60
Backup Exec 15 not showing job history in report if failed/cancelled. 9 100
Tracking a Database Lock - SQL 2014 10 47
Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now