ASP.NET - asynchronously executed stored procedure - not responding.
I have an web project (ASP.NET ) that searches the archive database.
From the web GUI the file is loaded by the user that is used to search the archive database.
This archive database search is performed by a stored procedure running under .NET CLR on SQL server 2005). The stored procedure code is written in using C#.
* Code is attached which makes Asynchronous call.
PROBLEM :
# The sp starts to run under the SQL server but the web page goes into infinite loop (stops responding).
From the web GUI the file is loaded by the user that is used to search the archive database.
This archive database search is performed by a stored procedure running under .NET CLR on SQL server 2005). The stored procedure code is written in using C#.
* Code is attached which makes Asynchronous call.
PROBLEM :
# The sp starts to run under the SQL server but the web page goes into infinite loop (stops responding).
...
//Calling Synchronous Methods Asynchronously
SystemMaint.UploadFileToDatabase_Async UploadCaller =
new SystemMaint.UploadFinCENFileToDatabase_Async(oSystemMaint.UploadFileToDatabase);
IAsyncResult result = UploadCaller.BeginInvoke((appPath + saveDir),
Server.HtmlEncode(FULoad.FileName), txtSearchStartDate.Text,
txtSearchEndDate.Text, null, null);
Thread.Sleep(System.Threading.Timeout.Infinite);
...
Walter Ritzel
Ok, so if on this code you are creating the assync call, where is the method that will detect the end of it?
you are using threads are you using multithreading since if thread are not created correctly you might expierence this infinite loop
Well, I am not sure that you have an infinite loop, you just wait infinite time :)
The line below instruct the current thread to do it:
Thread.Sleep(System.Thread
There are a couple ways to proceed with that
1) The simple way is to make synchronous call, and remove Thread.Sleep(). So your request will be processed and you'll see the page after the call. If the call takes a long time it might be not a good solution thought.
2) If the only thing you're interested is to post data to a database, and you don't read data immediately, then just remove Sleep call. SP will be called in background but page will be returned almost immediately, as soon as page rendering will not wait until SP is complete
3) In case if it;s a long call but you need to get results (and you don't want to have page frozen until your SP is done), then you need to use either
a) Register callback while making BeginInvoke call (it should be last parameter, I guess). In the method called by the callback, set some flag to the session, like Session["SP_Complete"] = true;
b) Use an AJAX timer to refresh a page (let' say. each second). Check for Session["SP_Complete"] value when page is loaded (and it's postback). If it's not set, then just return the page. If the value was set, then stop the timer,and render data on the page. If should ensure that you're using ViewState on the page, otherwise the timer won't be stopped. Also, try not to use pointers to a page while passing data into a callback method. If you store a pointer, then page may not be disposed and each time when you get a request top a page, one more instance will stuck in memory (I got this issue some time ago).
The line below instruct the current thread to do it:
Thread.Sleep(System.Thread
There are a couple ways to proceed with that
1) The simple way is to make synchronous call, and remove Thread.Sleep(). So your request will be processed and you'll see the page after the call. If the call takes a long time it might be not a good solution thought.
2) If the only thing you're interested is to post data to a database, and you don't read data immediately, then just remove Sleep call. SP will be called in background but page will be returned almost immediately, as soon as page rendering will not wait until SP is complete
3) In case if it;s a long call but you need to get results (and you don't want to have page frozen until your SP is done), then you need to use either
a) Register callback while making BeginInvoke call (it should be last parameter, I guess). In the method called by the callback, set some flag to the session, like Session["SP_Complete"] = true;
b) Use an AJAX timer to refresh a page (let' say. each second). Check for Session["SP_Complete"] value when page is loaded (and it's postback). If it's not set, then just return the page. If the value was set, then stop the timer,and render data on the page. If should ensure that you're using ViewState on the page, otherwise the timer won't be stopped. Also, try not to use pointers to a page while passing data into a callback method. If you store a pointer, then page may not be disposed and each time when you get a request top a page, one more instance will stuck in memory (I got this issue some time ago).
ASKER
Blacklord_76
My Lord ! :)
* your "1)" is not good for me, I need asynchronous execution of the stored procedure (it will run from 1 to 24 hours depending on the search range)
* your "2)" has been tried (without the thread.sleep call). In that case the sp execution strarts on the SQL server but that process is for some reason killed somehow (some times after 10 min and some times after 2-3-4 hours, randomly), I am not able to figure out the reason behind that. May be the Garbage collector (on web server or the SQL server) is killing it.
* your "3)" is good but I don't care for the call back or the result of the call, the sp updates the database table which is displayed to the user.
So my original problem is the stored procedure execution on the SQL server (under CLR) being killed randomly.
My Lord ! :)
* your "1)" is not good for me, I need asynchronous execution of the stored procedure (it will run from 1 to 24 hours depending on the search range)
* your "2)" has been tried (without the thread.sleep call). In that case the sp execution strarts on the SQL server but that process is for some reason killed somehow (some times after 10 min and some times after 2-3-4 hours, randomly), I am not able to figure out the reason behind that. May be the Garbage collector (on web server or the SQL server) is killing it.
* your "3)" is good but I don't care for the call back or the result of the call, the sp updates the database table which is displayed to the user.
So my original problem is the stored procedure execution on the SQL server (under CLR) being killed randomly.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Blacklord_76
I tried running it with the "IsBackground = true" ; while being called this way, for some reason the sp on the server gets killed in 1-2 seconds.
I changed the code back to
...
//Thread thread = new Thread(DoWork);
//thread.IsBackground = true;
//thread.Start();
DoWork();
...
And it works the way it used to before.
I tried running it with the "IsBackground = true" ; while being called this way, for some reason the sp on the server gets killed in 1-2 seconds.
I changed the code back to
...
//Thread thread = new Thread(DoWork);
//thread.IsBackground = true;
//thread.Start();
DoWork();
...
And it works the way it used to before.
ASKER
Is it possible Garbage collector of SQL server's CLR is cleaning the process ?
N.B. With the existing code (without IsBackground = True) the sp keeps running even after I've restarted the IIS.
N.B. With the existing code (without IsBackground = True) the sp keeps running even after I've restarted the IIS.
ASKER
I decided to create a job and call the stored procedure inside the job (run it as a JOB).
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.