Solved

SSRS 2005 hangs on "Report is being Generated" screen

Posted on 2010-08-25
15
1,709 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
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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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

21 Experts available now in Live!

Get 1:1 Help Now