Catch sevice stopping mid procedure

[Ed Pinto]

Hello all,
I have a TSQL procedure that takes about 30 seconds to run. I'm using ADO
with the SQLOLEDB provider to run the proc. I need to know if someone shuts
down the SQL Server Service while this proc is running. How can I detect
this? I've tried to use the ExecuteComplete and the Disconnect events of
the connection, but to no avail. The proc in question uses a cursor on a
view to perform multiple inserts. The most info I can get from the
ExecuteComplete event is that one record has been effected. Of course many
records have been effected (I can tell by doing a select count(*)), but I
assume the one record is the most recently commited transaction in the loop.
Any ideas on how I can detect a disconnect that occurs prior to the proc
looping through the selection of the view?

TIA,
Ed

[C. E. Buttles]

I don't think you have a problem since the proc obviously completes its
function. If any one turned off SQL Server, you would not get the results
you are getting. You can, however, run a trace on stored procedures event
category or on the data columns event data (look in BOL).


"Ed Pinto" <epinto@autoadmin.com> wrote in message
news:38ce738f@news.devx.com...
> Hello all,
> I have a TSQL procedure that takes about 30 seconds to run. I'm using ADO
> with the SQLOLEDB provider to run the proc. I need to know if someone
shuts
> down the SQL Server Service while this proc is running. How can I detect
> this? I've tried to use the ExecuteComplete and the Disconnect events of
> the connection, but to no avail. The proc in question uses a cursor on a
> view to perform multiple inserts. The most info I can get from the
> ExecuteComplete event is that one record has been effected. Of course
many
> records have been effected (I can tell by doing a select count(*)), but I
> assume the one record is the most recently commited transaction in the
loop.
> Any ideas on how I can detect a disconnect that occurs prior to the proc
> looping through the selection of the view?
>
> TIA,
> Ed
>
>

[Frank]

If you are using ADO to connect to a SQL Server database you can create error
handling code and retrieve OLE DB provide error messages from ADO's InfoMessage
event .

"C. E. Buttles" <cebuttle@sprintsvc.net> wrote:
>I don't think you have a problem since the proc obviously completes its
>function. If any one turned off SQL Server, you would not get the results
>you are getting. You can, however, run a trace on stored procedures event
>category or on the data columns event data (look in BOL).
>
>
>"Ed Pinto" <epinto@autoadmin.com> wrote in message
>news:38ce738f@news.devx.com...
>> Hello all,
>> I have a TSQL procedure that takes about 30 seconds to run. I'm using
ADO
>> with the SQLOLEDB provider to run the proc. I need to know if someone
>shuts
>> down the SQL Server Service while this proc is running. How can I detect
>> this? I've tried to use the ExecuteComplete and the Disconnect events
of
>> the connection, but to no avail. The proc in question uses a cursor on
a
>> view to perform multiple inserts. The most info I can get from the
>> ExecuteComplete event is that one record has been effected. Of course
>many
>> records have been effected (I can tell by doing a select count(*)), but
I
>> assume the one record is the most recently commited transaction in the
>loop.
>> Any ideas on how I can detect a disconnect that occurs prior to the proc
>> looping through the selection of the view?
>>
>> TIA,
>> Ed
>>
>>
>