Solved

SSRS 2005 hangs on "Report is being Generated" screen

Posted on 2010-08-25
15
1,722 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

770 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