problems in Executing stored procedures in Asynchronous Request

Hello everybody,

I'm trying to launch a stored procedure from an asynchronous request but it doesn't wan't to start...

The Sqlserver process goes crazy when calling the SP but nothing is written into the datatables... (I don't know what's going on at that moment. No exception can be thrown as calling manually the procedures with the same arguments works without any problems)

This is the code i'm using to call the procedure:

using (SqlConnection oConn = new SqlConnection(ConfigurationManager.AppSettings["MIS"]))
{
oConn.Open();

using (SqlCommand oCmd = new SqlCommand())

{

oCmd.Connection = oConn;

oCmd.Parameters.Add(new SqlParameter("SqlQuery", SQLquery.SelectStatement));

oCmd.Parameters.Add(new SqlParameter("threadId", ThreadId.ToString()));

oCmd.Parameters.Add(new SqlParameter("jobId", jobId));

oCmd.CommandText = "dbo.Stats_Print_Data";

oCmd.CommandType = CommandType.StoredProcedure;

oCmd.BeginExecuteNonQuery();

}

}

Any body any idea This is driving me nuts.

The stored procedure I'm calling is a CLR Stored procedure written in C#. I can run succesfully the procedure when performing a synchronous request using oCmd.ExecuteNonQuery(); However, this is not a solution as the procedures may take, for really big queries, more than 5 minutes to complete. In this case I receive a Request Time out before completion...

Any help or suggestion is welcome :-)

Thanks in advance

Answer this question

problems in Executing stored procedures in Asynchronous Request

HemantSoni
Where's your oCmd.EndExecuteNonQuery() method
Arunkjose
Hello Bill,

Thanks for your advice in this problem... I thaught it wasn't a necessity to check for the procedure to complete in this way...
However, Calling the procedure by the way it did it, didn't launch to procedure correctly, why Normally It should have been lauched anyway: SQL server should not care about the fact whether or not I'm check the IAsyncResult object does it
P.Johansson
How can I call this method if I don't provide a callback address to the beginExecuteNonQuery method
messaoud
Hello Bill,

I don't use this method as I'm checking every 5 seconds the database for procedure completion.... (checking status table)...
Matthew Lebo28574
I think you may have a problem because you're closing your connection (via the using statement) before calling EndExecuteNonQuery
Jademobile
from MSDN docs:

When you call BeginExecuteNonQuery to execute a Transact-SQL statement, you must call EndExecuteNonQuery in order to complete the operation.
flandercan
again, from msdn docs:

Because this overload does not support a callback procedure, developers must either poll to determine whether the command has completed, using the IsCompleted property of the IAsyncResult returned by the BeginExecuteNonQuery method; or wait for the completion of one or more commands using the AsyncWaitHandle property of the returned IAsyncResult.
Adrian Heath

You may want to check out more details from this article:

Asynchronous Command Execution in ADO.NET 2.0
problems in Executing stored procedures in Asynchronous Request

Answer this question