SQL Server Performance Forum – Threads Archive

stored procedure in MS SQL

Hi, i have a problem [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />], a have stored procedure in ms sql, when i’m calling this procedure from query analyzer, execute time is very short but when i’m calling this procedure from asp with Server.CreateObject("ADODB.Command") process ends with ‘Timeout expired’, but when i execute sp_recompile for this procedure, problem is solved, for a few days and repeated what can I do? pls. help me thnx

For timeout problem refer this
http://vyaskn.tripod.com/watch_your_timeouts.htm
Madhivanan Failing to plan is Planning to fail

Perform a schedule of update statistics and SP_RECOMPILE or DBCC DBREINDEX as a job in order to maintain optimum performance. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

What about increasing the timeout like
Server.Scripttimeout = 640

Also make sure that the stored procedure won’t require different execution plans when different parameters are passed e.g. things like select *
from table
where a = isnull( @p1, a )
and b = isnull( @p2, b ) this could result in a different plan if @p1 is null first call, and then @p2 is null subsequently. There are numerous other examples, so if the proc is not too big, perhaps post the contents of it? Cheers
Twan

Hiya, pretty good to see you’re back. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

yeah time flies! I go away to NZ to get married, come back to UK, get totally snowed under at work, then poof it’s 7 months later…! Cheers
Twan

Yes, I remember your special reason to go to NZ. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Welcome back to the "real world" anyway! – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

]]>