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