January 11, 2007 at 4:28 pm
I am having trouble with an asynchronous call to a long-running stored procedure. Specifically, the IAsyncResult that I get back from the call sets IsCompleted to true way too early, so when I call EndExecuteNonQuery(), it takes quite some time to complete before returning control to the calling application. I have a way to check to see if the procedure has actually completed without polling the IAsyncResult object, but the stored procedure seems to freeze at a certain point without completing unless I call EndExecuteNonQuery().
The code that sets this up (C# in an ASP.NET 2.0 app) is as follows:
SqlConnection async = new SqlConnection(Global.asyncConnString);
openDB(async);
SqlCommand sth = new SqlCommand("EXECUTE myStoredProcedure @param", async);
sth.CommandTimeout = 86400; // need to set here and in conn string
sth.Prepare();
sth.Parameters.Add("@param", SqlDbType.Char, 4).Value = someValue;
Session["asyncResult"] = sth.BeginExecuteNonQuery();
Session["asyncsth"] = sth;
if (!((IAsyncResult)(Session["asyncResult"])).IsCompleted)
{
updateLinkButton.Enabled = false;
updateLinkButton.Text = "Update in progress...";
cancelLinkButton.Visible = true;
}
All this code is in the event handler for a particular link click. I have "Asynchronous Processing=true;Connection Timeout=86400" in my connection string for this connection. Right now, the stored procedure is set up not to use a transaction (for testing purposes), so I can select from the table on which it operates and see the progress. The stored procedures sets a flag in another table to 1 when it starts and sets it back to 0 when it ends. Like I said, though, if I just check for this flag, the procedure never ends. It gets to a certain point in the table and does no more operations unless EndExecuteNonQuery(IAsyncResult) gets called. If I just run the SP from management studio, it works fine and ends after about five and a half minutes.
Suggestions? Do you need more information to help me? Thanks!
January 12, 2007 at 4:29 pm
I don't think you should call a five and half minutes stored proc in Asp.net, you need to split your stored proc operation to two or three because the link below Microsoft called a stored proc and the second call is to a fast read only DataReader so you can access the rows of the second table. It will help if you will run your code first in Management Studio with show execution cost on so you can tune your query and then run it through the Profiler and Database tuning advisor to add indexes as needed and your code is ready to run in Asp.net. Try the link below for correct uses of the ADO.NET 2.0 Asych method. Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms379553(VS.80).aspx
Kind regards,
Gift Peddie
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply