Stored procedure for RS report fails to return data

At month-end when multiple users are running the same report, sporatically the stored procedure will fail to return data for the report. The stored procedure returns data in the query window, and the problem is fixed by re-generating the stored proc (script it as alter into a query wendow and execute it). Has anyone else had this happen? If so is there a solution?


more ▼

asked Nov 03, 2011 at 06:20 AM in Default

avatar image

11 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I believe after sometime you may be getting timeouts. This is where no data may be coming.

Now as far as the procedure is concerned, there I think may be issue of parameter sniffing. Obviously, at month-ends the parameters supplied could be totally different from daily procedure call. I believe this is where a new execution plan produces the output. In our environment, we have maintenance window in which some procedures are recompiled, statistics are updated, indexes re-organized/re-build are done before moth-end routines. So my suggestion would be to re-compile this procedure before month-end routines.

If you can provide us the procedure/tables DDL and parameters (for routine and month-end), we will be able to help you better.

more ▼

answered Nov 03, 2011 at 06:45 AM

avatar image

Usman Butt
14.6k 6 13 21

Time to go home. I am sure you will be helped out by our SQL GURUs to follow, once you add more details.

Nov 03, 2011 at 06:47 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 03, 2011 at 06:20 AM

Seen: 1093 times

Last Updated: Nov 03, 2011 at 06:20 AM

Copyright 2018 Redgate Software. Privacy Policy