Member Avatar for Ole Raptor

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
  • 167 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by Ole Raptor
Member Avatar for Ole Raptor

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..
commented: Awesome Followup +9
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.