Solved

SSRS 2005 hangs on "Report is being Generated" screen

Posted on 2010-08-25
15
1,695 Views
Last Modified: 2012-05-10
Here's the deal ...

The report uses a stored procedure which accepts several parameters.  The stored procedure runs fine in SSMS, in fact it runs in seconds.  However, it hangs (forever showing "Report is being Generated") when rendering in "preview" of Visual Studio.

Other information:
The stored procedure accepts 4 paramenters, 3 are multi-value.
The preview works when only a few values are entered in the parameters.
The preview worked fine when I had 3 parameters (2 are multi-value); adding the 3 multivalue parameter is when the issue starts.
The report will render but it takes 20 minutes ... way too long.

Thank you for your comments
Mark
0
Comment
Question by:MarkMahon
  • 9
  • 3
  • 3
15 Comments
 
LVL 27

Accepted Solution

by:
planocz earned 250 total points
Comment Utility
What are the parameters? there may be a different way to do it.
0
 

Author Comment

by:MarkMahon
Comment Utility
To run the stored procedure I can use this (hopefully this gives u a sense of the parameters)

EXEC  [dbo].[usp_Report_RSGEstPremiumCheckPLAY]
@EndDate = '6/30/2010',
            @ClosingMtgContract = N'Y,N',
            @UWCode = N'DB,DBM,DEG,DGV,DR,GPP,JCF,JFR,JLB,KCI,KDS,MBR,SJH,TEC,TGD,UP,WDC',
            @ContractYear = N'2010,2009,2008,2007,2006,2005,2004,2003,2002,2001'
0
 

Author Comment

by:MarkMahon
Comment Utility
Also we  use the parameters in the stored procedure as follows ...

  WHERE
      last.ContractYear IN(Select Param from ArchRe.dbo.fn_MVParam(@ContractYear,','))
      AND last.UWCode IN(Select Param from ArchRe.dbo.fn_MVParam(@UWCode,','))
      AND last.ClosingMeetingContract IN(Select Param from ArchRe.dbo.fn_MVParam(@ClosingMtgContract,','))
0
 
LVL 27

Expert Comment

by:planocz
Comment Utility
I am not the best in SQL statements, but your where clause does not look right.
I am thinking more like this...
 WHERE
      last.ContractYear =@ContractYear
      AND last.UWCode =(@UWCode
      AND last.ClosingMeetingContract =(@ClosingMtgContract
0
 
LVL 27

Expert Comment

by:planocz
Comment Utility
Sorry, type - o error
WHERE
     last.ContractYear =@ContractYear
     AND last.UWCode =@UWCode
     AND last.ClosingMeetingContract =@ClosingMtgContract
0
 

Author Comment

by:MarkMahon
Comment Utility
The SQL works; as I mentioned the SQL works fine in SSMS.

I don't think the code you provided will work.
0
 

Author Comment

by:MarkMahon
Comment Utility
Your code unfortuntely won't work given that the parameter is multivalued.  It has to be parsed which is what the table valued function fn_MVParam does.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 11

Expert Comment

by:tickett
Comment Utility
Have you tried using SQL profiler to run a trace? Capture the sql command to ensure it looks right (and try copy/paste/run in SSMS). If that works and quickly we then need to start looking at SSRS...

L
0
 

Author Comment

by:MarkMahon
Comment Utility
tickett -

It definitly runs fine in SSMS.

 
0
 
LVL 11

Assisted Solution

by:tickett
tickett earned 250 total points
Comment Utility
I'm not sure if you understood what I was asking. I appreciate that the stored procedure may work fine from SSMS when you run it yourself with correct parameters- but by running a trace you can double check the parameters being passed and see if SQL is behaving somehow differently.

Apologies if you correctly understood and I've already tried this.

L
0
 

Author Comment

by:MarkMahon
Comment Utility
tickett - Would you "hand hold" me thru the process of running a trace thru the SQL profiler?

Do I run it from SSRS or SSMS?  Which everone, how is it performed?
0
 
LVL 11

Expert Comment

by:tickett
Comment Utility
In SSMS from the Tools menu (I think it jut says SQL Profiler). From profiler select new trace, connect to the relevant SQL server use the standard template (I think that will catch stored procedure calls) then hit ok/start/begin (i don't remember the button name).

If your SQL sees a lot of traffic you may want to do thi outside of busy hours or apply a column filter based on hostname or username to make it easier to spot your SSRS connection/commands.

Hope that helps- a bit vague as I'm on the mobile.

L
0
 

Author Comment

by:MarkMahon
Comment Utility
I changed the SQL around and it seems  (early in testing so I'm not out of the woods) to render faster.
Basically replaced the "IN" in the Where clause with Joins.

Here is a splice of code before:
WHERE
      last.ContractYear IN(Select Param from dbo.fn_MVParam(@ContractYear,','))
      AND last.UWCode IN(Select Param from dbo.fn_MVParam(@UWCode,','))
      AND last.ClosingMeetingContract IN(Select Param from dbo.fn_MVParam(@ClosingMtgContract,','))

Here is the revised splice:
:
LEFT OUTER JOIN (Select Param from fn_MVParam(@UWCode,',')) AS uwc ON last.UWCode  = uwc.Param
LEFT OUTER JOIN (Select Param from fn_MVParam(@ContractYear,',')) AS cyc ON last.ContractYear = cyc.Param
LEFT OUTER JOIN (Select Param from fn_MVParam(@ClosingMtgContract,',')) AS cmc ON last.ClosingMeetingContract = cmc.Param
WHERE
   uwc.Param is not NULL
   AND cyc.Param is not NULL
   AND cmc.Param is not NULL
0
 

Author Comment

by:MarkMahon
Comment Utility
tickett - thanks for the 101 class on SQL Profiler.  While I didn't use it perse to fix my issue, it's nice to have in the tool box.
0
 

Author Closing Comment

by:MarkMahon
Comment Utility
Your web page would not let me mark my code as the solution and award points to the experts.  It kept saying I needed to award at least 20 points.

So instead I awarded the partial points to the experts who gave helpful advice.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

771 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

14 Experts available now in Live!

Get 1:1 Help Now