Stored Procedure Progress Reporting 1

[RobertP]

Is there any way to return text strings during the processing of a stored procedure?

i.e. if my stored procedure has 3 parts, I want a message back after parts 1 and 2 to say that they've completed.

TIA,

Rob.

 

[ColinM]

Use can use the print command
i.e.

print 'Part 1 complete.'

this will return text in query analyzer. Might have different result with another front end.

 

[tlbroadbent]

A stored procedure is executed as one batch. I've found that Print doesn't return until the end of the stored procedure even if I have several Print statements in the SP. If anyone has successfully, printed as the SP progresses, I'd like some advice on how to do it. Terry

http://members.home.net/tlbroadbent/index.htm

 

[youngun]

If you specify GO command in the stored procedure, you can cut one batch (the entire stored procedure) into several small batches, then you can use the print to output your text string. Be careful, however, any variable you specified before only belongs to one batch. This means that now you have to declare them in each single small batch.

 

[tlbroadbent]

You cannot include a "GO" in a stored procedure. If you place a "GO" in a stored procedure and compile it, the creation of the stored procedure ends at the "GO" line. Terry

http://members.home.net/tlbroadbent/index.htm

 

[ColinM]

How about this:

use pubs
CREATE PROCEDURE [Test] AS
select * from titles
RAISERROR('Phase 1 complete', 10, 1)
select * from sales,authors, titles

 

[Ruairi]

Terry,
Colin is right, you can raise errors to report progress back to a client app. To read the information in client apps i use code like below which loops through the errors for a connection and removes the [Microsoft SQL Server Driver] noise and returns the actual string you raised. I didn't bother posting the part of the code that interprets the returned strings, just add some code to display the strings and a Next Index to close the loop and substitute your connection variable in place of 'm_objOpenSubs.cn.' (if you're using an ADO connection, if not this won't work as is). I think his suggestion would print the information in the querey results pane but i've never tried it since I debugged my stored procedures with visual studio.

------------------------------------------------------------

    For Index = 0 To m_objOpenSubs.cn.Errors.Count - 1
        sql = m_objOpenSubs.cn.Errors(Index).Description
        'delete all "[info]" blocks in each string
        Do
            position1 = InStr(sql, "[")
            position2 = InStr(sql, "]")
            If position1 > 0 And position2 > 0 And position1 < position2 Then
                sql = Mid(sql, position2 + 1, Len(sql) - position2)
            End If
        Loop Until position1 = 0 Or position2 = 0 Or (position1 >= position2)

Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontro

 

[Ruairi]

Terry,
Colin is right, you can raise errors to report progress back to a client app. To read the information in client apps i use code like below which loops through the errors for a connection and removes the [Microsoft SQL Server Driver] noise and returns the actual string you raised. I didn't bother posting the part of the code that interprets the returned strings, just add some code to display the strings and a Next Index to close the loop and substitute your connection variable in place of 'm_objOpenSubs.cn.' (if you're using an ADO connection, if not this won't work as is). I think his suggestion would print the information in the querey results pane but i've never tried it since I debugged my stored procedures with visual studio.

    For Index = 0 To m_objOpenSubs.cn.Errors.Count - 1
        sql = m_objOpenSubs.cn.Errors(Index).Description
        'delete all "[info]" blocks in each string
        Do
            position1 = InStr(sql, "[")
            position2 = InStr(sql, "]")
            If position1 > 0 And position2 > 0 And position1 < position2 Then
                sql = Mid(sql, position2 + 1, Len(sql) - position2)
            End If
        Loop Until position1 = 0 Or position2 = 0 Or (position1 >= position2)

Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontro

 

[tlbroadbent]

Ruairi,

It appears that you are reading the error log to get the message. Am I right? If so when you perform the RAISERROR in the procedure, you must raise an error that is logged. That seems like a real roundabout way to monitor progress.

In our shop, we have created an EventLog database on all of our servers. When we want to track progress of a process, we execute a stored procedure that adds a row to an EventLog table. The SP calls for three parameters - system, message and severity. We can monitor progress by polling this table.

In addition, we can execute additional processes depending on entries in the EventLog. It is a very simple and clean way to accomplish multiple purposes. Terry

http://members.home.net/tlbroadbent/index.htm

 

[tlbroadbent]

Colin, Ruairi and Rob,

By the way, today I tested printing a message and raising an error at various points in a stored procedure. I executed the SP in Query Analyzer. Neither method printed any messages until the entire SP finished. I'm still searching for a way to print a message at varous points in my procedure. Any more ideas?

I have SQL Server 7.0, SP1 installed. Which version do you have installed? If one of these methods works for you, perhaps there is SQL version or server configuration difference. Terry

http://members.home.net/tlbroadbent/index.htm

 

[Ruairi]

Terry,
1) No, i am not reading the error log. I am reading from the errors collection of the ADODB connection object. Think of it as the equivalent of VB's Err object. Errors accumulate until you clear them by calling the clear method.

2) I guess you're right and they don't show up until after the stored procedure. I have my SP's broken down into simple tasks which i can call like functions from a few major stored procedures that actually do the work. So the results are being reported while i'm in the middle of the stored procedure i called from my app. But after an individual SP has executed.

maybe the solution is to break the SP's down into smaller chunks. That may or may not make sense depending on what you do with them. Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontro

 

[ColinM]

Terry,

I'm using SQL7 SP3. Using the following script I get the three messages reported back at about 15 seconds interval.
Returning the results into grid display in Query Analyzer.

use pubs
go
CREATE PROCEDURE [Test] AS
select * from sales,authors, titles
RAISERROR('Phase 1 complete', 10, 1)
select * from sales,authors, titles
RAISERROR('Phase 2 complete', 10, 1)
select * from sales,authors, titles
RAISERROR('Phase 3 complete', 10, 1)
go
EXEC Test

 

[tlbroadbent]

Ruairi,

Thanks for your patient and thorough explanation. I haven't worked with ADO because I don't program on the client side currently. I took a wild leap into something I didn't know. Terry

http://members.home.net/tlbroadbent/index.htm