0

I have a Stored Procedure that is called through VB6 for a report. The Reports were working great until yesterday. Now all of the reports that use that sp are timing out. The default time is set to 60. I have adjusted this to 1000 and the report finally displays. When I login to sql using Management Studio I can run this sp and it takes less then a second. All data is being transferred correctly. I am at a loss. Nothing in the Code has been changed.
Any ideas will be greatly appreciated.

thanks

1
Contributor
1
Reply
3
Views
8 Years
Discussion Span
Last Post by ProfessorPC
1

Found my problem. I was passing 4 params. I found an article on Parameter Sniffers in 2005 SQL. Once I added another set or params with an existing value it all worked great.

@param1 int,
@param2 int,
@param3 int,
@param4 int,
@paramsniff1 int = 0,
@paramsniff2 int = 0,
@paramsniff3 int = 0,
@paramsniff4 int = 0

set paramsniff1 = @param1 
--etc..
Votes + Comments
Awesome Followup
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.