I was facing this issue for a while that I have created a Stored Procedure in SQL Server 2005 Database that performs a search on a table (according to the given parameters). It was happening that when this procedure was executed from ADO.net (my front-end Visual Basic Application) Timeout exception was occurring. But at the same time when we execute this procedure from SQL Server Management Studio it executes immediately.
The Timeout in Visual Basic Application was NOT coming immediately it was coming after some time when users execute the procedure many times. Whenever time out occurred, I went in the SQL Server Management Studio and just did ALTER (without changing a single thing in procedure). And the problem resolved for some time NOT permanently.
So I put this question on MSDN Forums and a member told me to put the WITH RECOMPILE in the procedure. He told me that
"I think the problem is with the cache. When you ALTER the procedure from Management Studio it is recompiled and the problem is fixed. this is my guess."
It sure fixed my problem and the Timeout never came afterwords. Here is the link to that post.
http://social.msdn.microsoft.com/Forums/en-US/...
The Timeout in Visual Basic Application was NOT coming immediately it was coming after some time when users execute the procedure many times. Whenever time out occurred, I went in the SQL Server Management Studio and just did ALTER (without changing a single thing in procedure). And the problem resolved for some time NOT permanently.
So I put this question on MSDN Forums and a member told me to put the WITH RECOMPILE in the procedure. He told me that
"I think the problem is with the cache. When you ALTER the procedure from Management Studio it is recompiled and the problem is fixed. this is my guess."
It sure fixed my problem and the Timeout never came afterwords. Here is the link to that post.
http://social.msdn.microsoft.com/Forums/en-US/...
