March 12, 2009 at 12:41 pm
We still have problems when reports stop running, then after a couple days start running again. When running a trace at the database, the main query (run through a stored procedure) never runs. Queries defined directly in reporting services and used for parameters run without issue. Is there some way to use debug or some other tracing type tool to figure out what reporting services is doing and why it is having problems running the stored procedures.
March 12, 2009 at 12:53 pm
If your stored procedure is not running it means someone is writing very bad code because SSRS usually run stored procedure more than SELECT statement because it passes execution to the relational engine instead of SSRS.
So you need to profile the stored procedure in SMS and find out why it is not running in SSRS, it generally rejects code with many temp table and some other problem code.
Kind regards,
Gift Peddie
March 12, 2009 at 12:58 pm
Sorry, I forgot to mention, the stored procedures run fine in SSMS. There are not temp tables being used. There are four queries used in the report and they take from 14 to 36 seconds to run in SSMS. I will run the profiler just to be sure though
March 12, 2009 at 1:03 pm
When you have four queries you have two choices either convert it to views or use subreports, that is do design before wrting code so your code will run.
Kind regards,
Gift Peddie
March 12, 2009 at 1:03 pm
profiler shows no activity
March 12, 2009 at 1:17 pm
That is the answer I was expecting on four stored procedures because it is the amount of data profiler produces that makes it not for production so you need to design before writing code. Here are pointers what data, tables and what format of report.
Kind regards,
Gift Peddie
March 12, 2009 at 1:57 pm
I admit I'm new to the sql world and am still getting up to speed with the language. I still think in terms of fields and files not tables and columns.
You seem to be stuck on the fact I am including multiple tables and graphs on the same report and several of these components need a separate query to pull different information. I see if reporting services did not handle this type of set up well, where subreports would help. I'm not seeing the view part or fully understanding what doing design before writing code means.
Anyhow to simplify this process, I just created a new report which only pulls one query (stored procedure) and dumps the query result into a table. This very simple report is still not running. Also, you have not indicated why reports would randomly start and stop running.
Sorry if I'm missing something here.
March 12, 2009 at 2:19 pm
You seem to be stuck on the fact I am including multiple tables and graphs on the same report and several of these components need a separate query to pull different information. I see if reporting services did not handle this type of set up well, where subreports would help. I'm not seeing the view part or fully understanding what doing design before writing code means
Reporting system gets data from large databases from large tables with many columns and rows so you design the report and find your columns and move those columns to views for fast access.
Anyhow to simplify this process, I just created a new report which only pulls one query (stored procedure) and dumps the query result into a table. This very simple report is still not running. Also, you have not indicated why reports would randomly start and stop running.
I have written reports that generates more than 180 pages by default in a test box so there is something wrong with your code or system setup, SSRS runs clean SET based code better than other types of code. So if it is not running you need to check your configuration of SSRS in Report Manager and IIS
Kind regards,
Gift Peddie
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply