Solved

SSRS 2005 hangs on "Report is being Generated" screen

Posted on 2010-08-25
15
1,741 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 3
  • 3
15 Comments
 
LVL 27

Accepted Solution

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

Author Comment

by:MarkMahon
ID: 33527562
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
ID: 33527573
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 27

Expert Comment

by:planocz
ID: 33527685
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
ID: 33527692
Sorry, type - o error
WHERE
     last.ContractYear =@ContractYear
     AND last.UWCode =@UWCode
     AND last.ClosingMeetingContract =@ClosingMtgContract
0
 

Author Comment

by:MarkMahon
ID: 33527730
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
ID: 33527744
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
 
LVL 11

Expert Comment

by:tickett
ID: 33528775
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
ID: 33530448
tickett -

It definitly runs fine in SSMS.

 
0
 
LVL 11

Assisted Solution

by:tickett
tickett earned 250 total points
ID: 33530477
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
ID: 33530514
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
ID: 33530556
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
ID: 33533249
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
ID: 33533271
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
ID: 33533432
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

749 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