September 13, 2010 at 1:32 pm
I've done this a hundred times and now this one doesn't work.
I create a new report in a VS2005 Reporting solution. I create a connection string that works fine. In the Query Builder I put the SP:
exec dbo.LeaderSummaryReport @year
I'm prompted with the columns of the dataset to specify Page, Grouping and Details. When I try to preview the report it prompts for the year parameter then goes off into la-la land. I don't even get a SQL timeout. Eventually I start getting "Visual Studio is busy" messages, but that's it. I even tried going to the Data tab and running the query (!) - same thing - prompt for year parameter then no response.
I can run the same command line in SQL Management Studio and get a response in <5 seconds.
Am I missing something basic? It's been a while but I thought this was pretty straightforward.
September 13, 2010 at 1:44 pm
Are you sure the sp is not locking or deadlocking?
September 14, 2010 at 4:49 am
Ninja's_RGR'us (9/13/2010)
Are you sure the sp is not locking or deadlocking?
Fairly certain. It's a simple read of a couple of tables into a table variable, compute some additional fields in the table variable, then select the table variable. The final select that creates the SP output has all the fields defined (not Select *). The dataset that SSRS designer creates is correct. It's only when I try to run the query in SSRS designer that it times out. I can run the exact same query in an SSMS query window with no issues.
I've tested the connection SSRS is using and it passes. I'm completely stumped.
September 14, 2010 at 5:14 am
Without posting all the code which is sizable, here is an outline of the SP:
DECLARE @Temp TABLE (BusinessUnit varchar(255), Leader varchar(100), [Level] int,
Sublevel int, Staff int, Emps int, Resps int, RespPct decimal(18,2),
Donors int, DonorPct decimal(18,2), Donation decimal(18,2),
Match decimal(18,2), TotalDonation decimal(18,2), LdrDonors int,
LdrPct decimal(18,2), LdrDonation decimal(18,2))
-- create 5 CTEs to read data from three tables with different WHERE clauses
-- INSERT INTO @Temp from each CTE
-- INSERT INTO @Temp Subtotal rows based on data in @Temp (simple grouping based on Level)
-- create 5 new CTEs to read data from same three tables with different WHERE clauses
-- INSERT INTO @Temp from new CTEs
-- INSERT INTO @Temp a Grand Total line using subset of rows in @Temp (Single Group based on Sublevel=1)
SELECT BusinessUnit, Leader, [Level], Sublevel, Staff,
Emps, Resps, RespPct, Donors, DonorPct, Donation, Match,
TotalDonation, LdrDonors, LdrPct, LdrDonation
FROM @Temp
ORDER BY [Level], Sublevel, Staff
September 14, 2010 at 5:30 am
It was Parameter Sniffing. Check here:
http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html
I changed the single SP parameter to assign it to a local variable and it fixed the problem. Go figure..
September 14, 2010 at 6:10 am
I'm not surprised actually. If you were able to do it 500 times and now 501 is not working then the problem is with that 1. From there it was just a matter of figuring what was stopping the query from running which you did beautifully.
HTH.
September 14, 2010 at 6:13 am
I found that solution in the SSRS Forum on MSDN. Fortunately when you open a new question they look at the subject you entered and list what they think may be related topics (ones Search didn't turn up for me). One of those was specifically about a query that runs fine in SSMS but not in SSRS.
September 14, 2010 at 6:33 am
Ya I've seen that feature elsewhere, I'll propose this feature for this site, it would definitly be a great addition.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply