[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SSRS 2005 hangs on "Report is being Generated" screen

Posted on 2010-08-25
15
Medium Priority
?
1,817 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 750 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 750 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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

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…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

830 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