SqlException Timeout expired

Hello peps,

I got a gridview control that uses an SqlDataSource.  The SqlDataSource is set to an store procedure on SQL 2005 server.  Here is the connection string:
Data Source=RM-SQL01\SQL05;Initial Catalog=Lists;Persist Security Info=True;User ID=*****;Password=*******;Connection Timeout=120;

As you can see I did put the connection timeout equal to 120 mins.  But still times out in about 30 sec.

This is happening because the store procedure is compiling.  After the first user compiles the sp than other users should fly.

I noticed this happens in the beginning of the week, seems that SQL Server is deleting the compiled sp out of it's memory during the weekend when no one is using the sp.  Is there away to tell SQL server to keep the compiled sp indefintly, then if things change I manually compile the sp?

Or is there another timeout property I am missing?  I cant find a timeout property in the SqlDataSource Control.

Thanks in advance
LVL 2
boricua1213Asked:
Who is Participating?
 
Nandakumar SakthivelTechnology LeadCommented:
Hi,
   Set the command timeout as 0 it will wait indefinitely

Thanks,
Nanda
0
 
boricua1213Author Commented:
Well I figured how to set command timeout on sqlDataSource:

Add a Selecting Event to the SqlDataSource and then add

    Protected Sub FailedCalls_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles FailedCalls.Selecting
        e.Command.CommandTimeout = 120
    End Sub

FailedCalls is the SqlDataSource

Any idea how to keep the sp in memory, cause it takes about 50 secs to compile after compile the sp flys?
0
 
Nandakumar SakthivelTechnology LeadCommented:
Volunteer,
           I think that trying my solution will help in  solving the problem.

Thanks,
Nanda
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.