Capturing messages from long-running batches

[Steve Jorgensen]

Hi all,

The problem I'm having is actually documented as a bug in all versions of
ADO through and including 2.7, but it seems to be true in 2.8, too, so I
assume the article simply hasn't been updated.

I need to execute a long-running stored procedure that uses RAISEERROR to
report progress as warning messages. If I run the command synchronously, I
can read all the messages from ADO, but not until the procedure is done.
If I run the command asynchronously, the messages never appear in the
Errors collection.

Has anyone come up with a work-around for this?

Thanks,

- Steve J

By the way, I also tried DAO's ODBC-Direct for asynchronous execution, but
it doesn't seem to return warning messages in either synchronous or
asynchronous mode, though it does return a non-sense type conversion error
at some points.

 

[Vadim Rapp]

SJ> I need to execute a long-running stored procedure
SJ> that uses RAISEERROR to report progress as warning
SJ> messages. If I run the command synchronously, I
SJ> can read all the messages from ADO, but not until
SJ> the procedure is done. If I run the command
SJ> asynchronously, the messages never appear in the
SJ> Errors collection.

Depending on how you want to accept these messages, it can be
xp_cmdshell(net send), or writing in a file monitored by your application,
or in eventlog, or even sending emails...

Vadim

 

[Steve Jorgensen]

SJ> I need to execute a long-running stored procedure
SJ> that uses RAISEERROR to report progress as warning
SJ> messages. If I run the command synchronously, I
SJ> can read all the messages from ADO, but not until
SJ> the procedure is done. If I run the command
SJ> asynchronously, the messages never appear in the
SJ> Errors collection.

Depending on how you want to accept these messages, it can be
xp_cmdshell(net send), or writing in a file monitored by your application,
or in eventlog, or even sending emails...

Vadim

Well, none of those sound practical, but if I take that one step farther
and write to a table <g> - I think that might work.

Thank you Vadim

 

[Alick [MSFT]]

Hi Steve,

Could you please post the article num/link that states the bug you
mentioned?

You may want to refer to the sample in the article for asynchronously
executing a command:

Asynchronously Executing a Command
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html
/wpmdac_tf_asynchronously_executing.asp

Please feel free to reply to the threads if you have any concerns or
questions.




Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: Steve Jorgensen <[email protected]>
| Newsgroups: microsoft.public.access.adp.sqlserver
| X-Tomcat-NG: microsoft.public.access.adp.sqlserver
|
| Hi all,
|
| The problem I'm having is actually documented as a bug in all versions of
| ADO through and including 2.7, but it seems to be true in 2.8, too, so I
| assume the article simply hasn't been updated.
|
| I need to execute a long-running stored procedure that uses RAISEERROR to
| report progress as warning messages. If I run the command synchronously,
I
| can read all the messages from ADO, but not until the procedure is done.
| If I run the command asynchronously, the messages never appear in the
| Errors collection.
|
| Has anyone come up with a work-around for this?
|
| Thanks,
|
| - Steve J
|
| By the way, I also tried DAO's ODBC-Direct for asynchronous execution, but
| it doesn't seem to return warning messages in either synchronous or
| asynchronous mode, though it does return a non-sense type conversion error
| at some points.
|