x

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?

Thanks.
more ▼

asked Nov 03 '11 at 06:20 AM in Default

MamaJuJu gravatar image

MamaJuJu
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 '11 at 06:45 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

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 '11 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x94
x21
x16
x11

asked: Nov 03 '11 at 06:20 AM

Seen: 789 times

Last Updated: Nov 03 '11 at 06:20 AM