I have an asp.net (c#) web site that was built a few years ago and no code changes for last couple years.
This web site takes user input (either incremental or all), imports data from one location to another and display the results at the end.
Depending on the option chosen, the web site could run for 2-4 hrs and only user intereaction required is to select the option and clicke "Import" at the beginning (then they usually do something else and come back to the page later to see the result).
Recently, users have reported that they are getting prompted for login about 5 min into the import. Once they provide login and that basically terminates the session.
Further investigation, I found that one sproc it calls via ADO is taking longer than it used to since we have more data to import now. This sproc, sp_refreshdata now takes about 6-7 min and I know while the sproc is running, the web site suddenly prompt
login. So I commented out teh parts that takes majority time from sp_refreshdata and all runs fine. I even updated the sproc so that it deals with subset of data and I get mixed result depending on the amount of records to process. So I am pretty sure this
behaviour is related to the time it takes to run the sproc via ado but can't seem to find a way to solve this.
Here is the code:
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 0; //for unlimit. I changed this to 600 (10 min) but no differences
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_refreshdata";
//log to indicate start
cmd.ExecuteNonQuery();
//log to indicate finish
So if I run this method, I see an event entry log that the method is starting. Then just about 5-7 min, I see a login popup. I then see an event entry log that the method is done.
Two other things I did was:
- increased Connection timeout property of IIS to 600 (no difference)
- increased ADO connection object timeout (within connection string) to 600 (no difference).
I think I am running out of optins. Could you please help??
This sproc, sp_refreshdata now takes about 6-7 min and I know while the sproc is running, the web site suddenly prompt login.
Your stored procdure runs about for 6—7 mins？Maybe a bit too long……Please simplify your Stored procdure first。
For very complicated things，you should use Views and then call them through the Stored procdure to simply the complicated logic codes to shorten the execution time period……
seank_ca@hot...
0 Points
2 Posts
Getting login challege while running a stored procedure
May 01, 2012 04:42 PM|LINK
Hello,
I have an asp.net (c#) web site that was built a few years ago and no code changes for last couple years.
This web site takes user input (either incremental or all), imports data from one location to another and display the results at the end.
Depending on the option chosen, the web site could run for 2-4 hrs and only user intereaction required is to select the option and clicke "Import" at the beginning (then they usually do something else and come back to the page later to see the result).
Recently, users have reported that they are getting prompted for login about 5 min into the import. Once they provide login and that basically terminates the session.
Further investigation, I found that one sproc it calls via ADO is taking longer than it used to since we have more data to import now. This sproc, sp_refreshdata now takes about 6-7 min and I know while the sproc is running, the web site suddenly prompt login. So I commented out teh parts that takes majority time from sp_refreshdata and all runs fine. I even updated the sproc so that it deals with subset of data and I get mixed result depending on the amount of records to process. So I am pretty sure this behaviour is related to the time it takes to run the sproc via ado but can't seem to find a way to solve this.
Here is the code:
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 0; //for unlimit. I changed this to 600 (10 min) but no differences
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_refreshdata";
//log to indicate start
cmd.ExecuteNonQuery();
//log to indicate finish
So if I run this method, I see an event entry log that the method is starting. Then just about 5-7 min, I see a login popup. I then see an event entry log that the method is done.
Two other things I did was:
- increased Connection timeout property of IIS to 600 (no difference)
- increased ADO connection object timeout (within connection string) to 600 (no difference).
I think I am running out of optins. Could you please help??
thanks.
Ken Tucker
All-Star
16797 Points
2608 Posts
MVP
Re: Getting login challege while running a stored procedure
May 01, 2012 04:46 PM|LINK
Is the session timeout set high enough in the web.config?
<authentication mode="Forms">
<forms loginUrl="~/Account/LogOn" timeout="2880"/>
</authentication>
Space Coast .Net User Group
seank_ca@hot...
0 Points
2 Posts
Re: Getting login challege while running a stored procedure
May 01, 2012 05:14 PM|LINK
There is no login page and uses integrated windows auth.
<authentication mode="Windows"/>
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Getting login challege while running a stored procedure
May 03, 2012 01:24 AM|LINK
Your stored procdure runs about for 6—7 mins？Maybe a bit too long……Please simplify your Stored procdure first。
For very complicated things，you should use Views and then call them through the Stored procdure to simply the complicated logic codes to shorten the execution time period……
Reguards.