Displaying Status from Stored Procedure

Hi,

I am wondering if this is possible. I have an ASP page that calls a stored
procedure (MSSQL). I want to display the status on the ASP page as each
process is executed on the Stored Procedure and ASP page. For example, when
I connect to the database, show the user that a connection has been made or
in the stored procedure when some tables are updated to show that.

Is this possible? Are there any samples?

Thanks,
Shahid

Re: Displaying Status from Stored Procedure

>For example, when
> I connect to the database, show the user that a connection has been made

set conn = CreateObject("ADODB.Connection")
on error resume next
conn.open ""
if err.number = 0 then
response.write "Connection has been made"
else
response.write "Connection failed: " & err.description
end if

> in the stored procedure when some tables are updated to show that.

This is going to be a lot trickier. ASP doesn't really have an event sink,
so you're not going to be able to wait for errors or print statements during
the execution. But you can possibly mimic this with a bunch of selects, and
use rs.nextrecordset() to cycle through them. I tried to do this using
RAISERROR() WITH NOWAIT and while it worked great in Query Analyzer, it
wasn't easy to capture in ASP.




CREATE PROCEDURE dbo.gunther
AS
BEGIN
SET NOCOUNT ON

WAITFOR DELAY '00:00:02'

CREATE TABLE #foo(i INT)

SELECT 'Table created'

WAITFOR DELAY '00:00:02'

INSERT #foo SELECT 1 UNION ALL SELECT 2

SELECT RTRIM(@@ROWCOUNT) ' row(s) affected'

WAITFOR DELAY '00:00:02'

UPDATE #foo SET i = i + 1 WHERE i < 2

SELECT RTRIM(@@ROWCOUNT) ' row(s) affected'

WAITFOR DELAY '00:00:02'

DROP TABLE #foo

SELECT 'Table dropped'
END


Now, in previous versions of IE/IIS, you could do this:

<%
response.buffer = true
set conn = CreateObject("ADODB.Connection")
conn.open ""
set rs = conn.execute("EXEC dbo.gunther")
do while not rs is nothing
if not rs.eof then
response.write rs(0) & "
"
Response.Flush()
end if
set rs = rs.nextrecordset()
loop
%>

However, on my machine (IIS 6.0), the buffer/flush isn't obeyed, so you
don't get any of the text until the whole script has been processed...

--
http://www.aspfaq.com/
(Reverse address to reply.)

Re: Displaying Status from Stored Procedure

Aaron [SQL Server MVP] wrote:

>
> However, on my machine (IIS 6.0), the buffer/flush isn't obeyed, so
> you don't get any of the text until the whole script has been
> processed...
>
I think I read somewhere that a minimum amount of data needed to be
initially flushed in order for subsequent flushes to work. Does that sound
familiar to you?

Bob

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Displaying Status from Stored Procedure

> I think I read somewhere that a minimum amount of data needed to be
> initially flushed in order for subsequent flushes to work. Does that sound
> familiar to you?

I don't doubt that that's the case, but I don't recall reading that
anywhere. Then again, we don't really get that much in the way of change
documentation for IIS releases...

A

Re: Displaying Status from Stored Procedure

Thank you, it worked like a charm!

Shahid

"Aaron [SQL Server MVP]" wrote in message
news:eTETLscxEHA.3260@TK2MSFTNGP10.phx.gbl...
> >For example, when
> > I connect to the database, show the user that a connection has been made
>
> set conn = CreateObject("ADODB.Connection")
> on error resume next
> conn.open ""
> if err.number = 0 then
> response.write "Connection has been made"
> else
> response.write "Connection failed: " & err.description
> end if
>
> > in the stored procedure when some tables are updated to show that.
>
> This is going to be a lot trickier. ASP doesn't really have an event
sink,
> so you're not going to be able to wait for errors or print statements
during
> the execution. But you can possibly mimic this with a bunch of selects,
and
> use rs.nextrecordset() to cycle through them. I tried to do this using
> RAISERROR() WITH NOWAIT and while it worked great in Query Analyzer, it
> wasn't easy to capture in ASP.
>
>
>
>
> CREATE PROCEDURE dbo.gunther
> AS
> BEGIN
> SET NOCOUNT ON
>
> WAITFOR DELAY '00:00:02'
>
> CREATE TABLE #foo(i INT)
>
> SELECT 'Table created'
>
> WAITFOR DELAY '00:00:02'
>
> INSERT #foo SELECT 1 UNION ALL SELECT 2
>
> SELECT RTRIM(@@ROWCOUNT) ' row(s) affected'
>
> WAITFOR DELAY '00:00:02'
>
> UPDATE #foo SET i = i + 1 WHERE i < 2
>
> SELECT RTRIM(@@ROWCOUNT) ' row(s) affected'
>
> WAITFOR DELAY '00:00:02'
>
> DROP TABLE #foo
>
> SELECT 'Table dropped'
> END
>
>
> Now, in previous versions of IE/IIS, you could do this:
>
> <%
> response.buffer = true
> set conn = CreateObject("ADODB.Connection")
> conn.open ""
> set rs = conn.execute("EXEC dbo.gunther")
> do while not rs is nothing
> if not rs.eof then
> response.write rs(0) & "
"
> Response.Flush()
> end if
> set rs = rs.nextrecordset()
> loop
> %>
>
> However, on my machine (IIS 6.0), the buffer/flush isn't obeyed, so you
> don't get any of the text until the whole script has been processed...
>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>