ADO.NET Timout

[Lit]

Hello,

The default ADO.NET Timeout is 30 seconds.
If I execute a Stored procedure that takes lets say 120 seconds then what
happens.

ADO.NET Times and throws an exception

But what about SQL Server Does SQL Server keep on Executing the Stored
procedure?

Thanks

Lit

 

[Mary Chipman [MSFT]]

If the connection is terminated, then any active transactions that are
occuring inside the stored procedure that have not yet been committed
will be rolled back. Any committed transactions will be preserved. If
you want all work items inside of the stored procedure to be handled
as a single unit of work, you can implement an explicit transaction.
For more information, see "Explicit Transactions" in SQL Server Books
Online.

--Mary

 

[Lit]

Mary,

Thanks for your answer.

What if there are no transactions at all.
What you are saying that once ADO.NET times out the procedure is terminated?

Thanks again,

Lit

 

[Lit]

Mary,

How can I call a stored procedure to do its thing asynchronously.

What is the best way of doing this?

Thank You,

Lit

 

[William \(Bill\) Vaughn]

A SP can be called asynchronously from ADO.NET (2.0) by using the
BeginExecute method of the Command object. ADO classic supports async ops as
well. I'll be discussing this in my session on Async operations at VSLive
this week (Thursday 11:30) in Vegas.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Lit]

William,

So the 30 sec Connection Timeout will have no effect when a SP called
asynchronously?
Do I still have to give it a higher time value?
What I am trying to do is call a SP and let it execute no matter how long it
takes it.
Being asynchronous does not mean it is not going to timeout?

Thanks

Lit

 

[William \(Bill\) Vaughn]

Sure it will (timeout). You need to set the Command.Timeout property to the
number of seconds required to complete the operation. 0 :: wait
indefinitely. I don't recommend this approach as your execution thread will
hang indefinitely.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Lit]

William,

However how can I just tell SQL via ADO.NET to start executing a stored
procedure and that's it.
I don't need any returned values.
Can I tell it to start a Job? That in turn execute SP(s)

Thanks for your help

Lit

 

[Cor Ligthert [MVP]]

Bill,

Asynchronously operations, is your session about gambling?



Cor

 

[William \(Bill\) Vaughn]

There are lots of ways to start SPs including OSQL/ISQL/SQLCMD that don't
require ADO.NET at all. To do so in ADO.NET, simply create a Command object,
set the CommandText to the SP name and set the CommandType to stored
procedure and execute it. You might also want to set the CommandTimeout as
discussed earlier.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________