Is there way for a SQL Server stored procedure to report progress to the caller?
I have a complex task consisting of multiple steps, and I have a stored procedure that does it. I call this stored procedure from a MS Access front-end. The stored procedure takes about 3-4 minutes to finish, during which period Access seems to "hang", waiting for the stored procedure to return a result or time out.
I know I could take my stored procedure apart into 3-4 little pieces, and call them one after the other, providing some feedback between the steps. This however feels like a workaround, not a solution.
I'd also hate to have four stored procedures instead of just one, because I have many tasks like this, and this path would turn my 30 stored procedures (already numerous) into 50+.
So, is there any way for a stored procedure to occasionally report back to the calling Access (DAO) with a status or anything?
Here is how I call the stored procedure:
Dim cdb As DAO.Database, qdf As DAO.QueryDef
Set cdb = CurrentDb
Set qdf = cdb.CreateQueryDef("")
' get .Connect property from existing ODBC linked table
qdf.Connect = cdb.TableDefs("Quote").Connect
qdf.sql = "EXEC INV.ProcessMatchedQueries"
qdf.ReturnsRecords = False
qdf.ODBCTimeout = 300
qdf.Execute dbFailOnError
Set qdf = Nothing
Set cdb = Nothing
It seems there no good solution.
Try to execute one SP to do your job (with dbRunAsync) and another SP to receive result status. First SP should store execution status somewhere and second SP will read it.