Getting progress of stored procedure

Question

I have a long running stored procedure, executed in a thread (via TADOStoredProc) to ensure that the application remains responsive.

There are a number of RAISERROR() WITH NOWAIT statements in the stored procedure so I can monitor progress while running it in SSMS.

Is there any way that the application can get these messages so that I can give the users some indication of progress?

Why not insert the logging in a table that a separate process can read / poll? — Stu, Jun 21 '23 at 08:25
Or create an extended event with sp_statement_started and completed. — Peter, Jun 21 '23 at 08:47
Try handling the [Data.Win.ADODB.TADOConnection.OnInfoMessage](https://docwiki.embarcadero.com/Libraries/Alexandria/en/Data.Win.ADODB.TADOConnection.OnInfoMessage) event. — Dan Guzman, Jun 21 '23 at 10:34
Thanks @DanGuzman, that worked. Please add that as an answer so I can accept it — Keith Miller, Jun 21 '23 at 13:38

score 2 · Accepted Answer · edited Jun 21 '23 at 14:44

2

Messages (e.g. PRINT and RAISERROR) may be captured by handling the ADODB.Connection.InfoMessage event in ADO classic. This is exposed by the Data.Win.ADODB.TADOConnection.OnInfoMessage event.

edited Jun 21 '23 at 14:44

Remy Lebeau

answered Jun 21 '23 at 14:17

Dan Guzman

The comments in https://stackoverflow.com/a/255311/212016 were helpful as well to get it working – Keith Miller Jun 21 '23 at 14:52

1 Answers1