Re: Asynchronous Stored Procedure Never Returns - Help?

Hi Siv,

If you have the Sql Server 2000 or 2005 docs they are thorough and can be
used as a reference while writing procedures. Open the help file and filter
for Transaction SQL Reference from the drop-down or select a keyword from
your query in Query Analyzer or Sql Server Management Studio, such as
"TRAN", and press Shift+F1. You could also just type "TRAN' in the search
and you'll get some useful docs.

-- this variable will hold the first error code encountered
DECLARE @Error int;

-- begin an unnamed transaction
BEGIN TRAN;

-- perform some operation
INSERT TestTable SELECT N'Test String Value';

-- you must copy the global error value into a local variable immediately
after
-- you perform certain operations in T-SQL because you should expect that
-- operations such as SET, SELECT, INSERT, UPDATE, DELETE, EXEC
-- all set the @@Error to 0 if they succeed.
SET @Error = @@ERROR;

IF @Error <> 0 GOTO Complete;

-- perform another operation
INSERT TestTable SELECT N'Another String Value';

-- check the error state once again
SET @Error = @@ERROR;

-- this check isn't necessary here, but I wanted to illustrate that you
would need
-- it again if the previous INSERT statement wasn't the last operation that
this
-- procedure were to perform
IF @Error <> 0 GOTO Complete;

-- Todo: more db work

Complete: -- label for when an error occurs

-- complete the procedure
IF @Error <> 0
ROLLBACK TRAN;
ELSE
COMMIT TRAN;

-- Dave Sexton

"Siv" <siv@xxxxxxxxxxxxxxxxxxx> wrote in message
news:%23liXokfpGHA.4760@xxxxxxxxxxxxxxxxxxxxxxx
Dave,
Funnily enough I was just implementing the overload that uses the Async
Callback as when I last checked no-one had responded. The SP uses a
cursor to iterate through a recordset created from one query applying an
update to another set of records using some fields from the first query.
So it's a little bit more complex than it may appear from my code.

What I want to do is ensure that if the SP fails I can roll back all the
transactions done as it is an all or nothing job as reporting is done from
this and if only half of the records have been processed there will be
some fairly big problems.

I take your point about coding a rollback into the SP, I will have to look
at that, do you have any examples of doing that as I must admit to not
having done that before, I always do it from the calling application?

Thanks for your advice I will attempt to implement that and feedback here
how it works.

--
Siv
Martley, Near Worcester, United Kingdom.
"Dave Sexton" <dave@jwa[remove.this]online.com> wrote in message
news:Oph$ygepGHA.4988@xxxxxxxxxxxxxxxxxxxxxxx
Hi Siv,

1. You have to call EndExecuteNonQuery at some point.

2. Why loop until IsCompleted is true, sleeping for 100 ms every
iteration, when you can just specify an AsyncCallback and immediately
return to the caller? One of the overloads for BeginExecuteNonQuery
accepts an AsyncCallback argument and an Object argument that you can use
to send state information to the callback. The callback implementation
is the perfect opportunity to call EndExecuteNonQuery on the SqlCommand,
which can be supplied as the state argument, and to update the UI to
notify the user that the process has completed.

3. Use a Timer to update the UI thread if you want to display status
information to the user while processing asynchronously, unless the app
is targeted for users that have no need for performance or diagnostics
data, in which case I would omit the Timer altogether and display an
animating progress bar or something of the like.

4. See inline

SqlCommand cm = new SqlCommand();
[snip]
cm.Connection=Conn;
[snip]
IAsyncResult Res = cm.BeginExecuteNonQuery();
tran = Conn.BeginTransaction();

If you are trying to execute this command within a transaction:

tran = Conn.BeginTransaction();
SqlCommand cm = new SqlCommand("proc_Calc_Monthly", Conn, tran);
cm.CommandType = CommandType.StoredProcedure;
IAsyncResult Res = cm.BeginExecuteNonQuery();

5. Your code does not show any necessity for a transaction since you are
only executing a single command against the database. If your stored
procedure requires a transaction then code the transaction in T-SQL.

6. Use the SqlCommandBuilder.DeriveParameters method so you don't have to
hard-code every parameter in your SqlCommand, or better yet use the Data
Access Application Block from Microsoft, which provides a ParameterCache
and standardized data access utilities.

- Dave Sexton

"Siv" <siv@xxxxxxxxxxxxxxxxxxx> wrote in message
news:%23xwOcCcpGHA.524@xxxxxxxxxxxxxxxxxxxxxxx
Hi,
I have a stored procedure that I want to execute and then wait in a loop
showing a timer whilst it completes and then carry on once I get
notification that it has completed. The main reason for this being to
stop the user thinking the application has frozen when in fact it is
just waiting for a long SP to complete. Another reason for doing it
like this is that I also have had a problem in the past where the SP
takes longer than the timeout and the app then stops before the query
has completed.

My code is as follows:
==========================================================
SqlCommand cm = new SqlCommand();
SqlParameter par = new SqlParameter();
SqlConnection Conn = new SqlConnection();

SqlTransaction tran=null;

DateTime CurrTime, PrevTime;
TimeSpan ElapsedTime;

Conn.ConnectionString = Common.strCnn;
//Common.strCnn is a user setting pointing to a Connection string
//that is formatted like this:
//Server=SERVERNAME;DATABASE=DATABASENAME;Integrated
Security=true;Asynchronous Processing=true;

Conn.Open();
cm.Connection=Conn;
cm.CommandText = "proc_Calc_Monthly";
cm.CommandType = CommandType.StoredProcedure;
cm.CommandTimeout = 0;

par = cm.CreateParameter();
par.Direction=ParameterDirection.Input;
par.ParameterName="@SM";
par.SqlDbType=SqlDbType.Int;
par.Value=EndM; //This is a variable holding an end month as an integer
such as 7 meaning July
cm.Parameters.Add(par);

par = cm.CreateParameter();
par.Direction=ParameterDirection.Input;
par.ParameterName="@SY";
par.SqlDbType=SqlDbType.Int;
par.Value=EndY; //This is a varible holding an end year as an integer
such as 2006
cm.Parameters.Add(par);

//Start transaction here
IAsyncResult Res = cm.BeginExecuteNonQuery();
tran = Conn.BeginTransaction();

while (!Res.IsCompleted)
{
CurrTime = DateTime.Now;
ElapsedTime = CurrTime.Subtract(PrevTime);
System.Threading.Thread.Sleep(100);
//Let system in for a click
Readout.Text = "Processing Monthly Totals calculation - Please Wait
... ";
Readout.Text += "\nTime Elapsed=" +
string.Format("{0:hh:mm:ss}",ElapsedTime);
Readout.text += "\nPress F2 function key to abort ...";
System.Windows.Forms.Application.DoEvents();
if (CalcsAborted)
{
string msg = "Are you sure, all transactions created by the
Proc_Calc_Monthly Stored Procedure will be rolled back?";

if(MessageBox.Show(msg,Common.H,MessageBoxButtons.OKCancel,MessageBoxIcon.Question,MessageBoxDefaultButton.Button1)
== DialogResult.OK)
{
CalcsAborted = true;
break;
}
else
{
CalcsAborted = false;
break;
}
}
}
//Processing contines here once Res.IsCompleted becomes true, however it
never does??
// etc ...
==========================================================
I expect the while statement to keep looping round until the IsCompleted
flag is set true, but at the moment it seems to never return true. I
took this from an example in the BeginExecuteNonQuery help text.
Can anyone explain why this doesn't work? The above code is within a try
catch block that doesn't trigger as any error, so I can't see why SQL
Server never sends a message back. I am running this against a SQL
Server 2000 database.
--
Siv
Martley, Near Worcester, United Kingdom.







.