Is Stored Procedure Running??

[Jason Burton]

How in ASP can I check to see if a stored proc is running??

The reason being is that when I execute this specific stored proc, it takes
a while.. and I dont want the
user to execute it again....


Let me know please.

Jason Burton
[email]talon@starloop.com[/email]

[Adrian Forbes [ASP MVP]]

> How in ASP can I check to see if a stored proc is running??

Not really possible. Let's say the SP is running for another user?

> The reason being is that when I execute this specific stored proc, it

takes

> a while.. and I dont want the
> user to execute it again....

Is the SP being run synchrously or asynchronously? You would have to
control this using Session vars. Set in the session that the SP is being
run, then clear the session value when the SP has finished

Session("RunningSP") = true
objconn.execute "...."
Session("RunningSP") = false

[Jason Burton]

Thanks Adrian,

Thats a good ASP method, but I'm wondering if ie. SQL 2000 or in the stored
procedure itself theirs a way to ie. return a value if its presently being
used.. or refreshing..

The stored proc I made just moves data, but scans over 100,000 records
first. Then every time it finds the records it wants to move, it does it
again til all of them are done. Then the proc is done. But I run it like
every other day.

Any ideas? Anyone else have this experience? What do banking institutions
do for large amounts of data to expedite the process? This is dealing with
my SMTP server, (I do ODBC logging) so as a spam solution I check for 550
errors. (thats pretty much what it does..)

Jason


"Adrian Forbes [ASP MVP]" <sorry@noemail.zzz> wrote in message
news:uWRhXpARDHA.2276@TK2MSFTNGP12.phx.gbl...

> > How in ASP can I check to see if a stored proc is running??

>
> Not really possible. Let's say the SP is running for another user?
>

> > The reason being is that when I execute this specific stored proc, it

> takes

> > a while.. and I dont want the
> > user to execute it again....

>
> Is the SP being run synchrously or asynchronously? You would have to
> control this using Session vars. Set in the session that the SP is being
> run, then clear the session value when the SP has finished
>
> Session("RunningSP") = true
> objconn.execute "...."
> Session("RunningSP") = false
>
>

[Chris Hohmann]

"Jason Burton" <j.burton@intelligence-agency.net> wrote in message
news:vgh7hch4t24d6@corp.supernews.com...

> Thanks Adrian,
>
> Thats a good ASP method, but I'm wondering if ie. SQL 2000 or in the

stored

> procedure itself theirs a way to ie. return a value if its presently

being

> used.. or refreshing..
>
> The stored proc I made just moves data, but scans over 100,000 records
> first. Then every time it finds the records it wants to move, it does

it

> again til all of them are done. Then the proc is done. But I run it

like

> every other day.
>
> Any ideas? Anyone else have this experience? What do banking

institutions

> do for large amounts of data to expedite the process? This is dealing

with

> my SMTP server, (I do ODBC logging) so as a spam solution I check for

550

> errors. (thats pretty much what it does..)
>
> Jason
>
>
> "Adrian Forbes [ASP MVP]" <sorry@noemail.zzz> wrote in message
> news:uWRhXpARDHA.2276@TK2MSFTNGP12.phx.gbl...

> > > How in ASP can I check to see if a stored proc is running??

> >
> > Not really possible. Let's say the SP is running for another user?
> >

> > > The reason being is that when I execute this specific stored proc,

it

> > takes

> > > a while.. and I dont want the
> > > user to execute it again....

> >
> > Is the SP being run synchrously or asynchronously? You would have

to

> > control this using Session vars. Set in the session that the SP is

being

> > run, then clear the session value when the SP has finished
> >
> > Session("RunningSP") = true
> > objconn.execute "...."
> > Session("RunningSP") = false
> >
> >

>
>

Checkout sp_who in BOL(Books OnLine). Basically it returns a result set
of running processes. The stored procedure accepts (process id | user id
| active) as filtering parameters. Ideally, if you have the process id
of the procedure in question that's the best way to limit the result
set.

HTH
-Chris

[Jason Burton]

Thanks,

So in reality, I could for example filter using the user id I use for
processing the procedure.. (only one user uses it..)

Then just check that result set if not empty..

Am I correct with this analogy?

Jason


"Chris Hohmann" <hohmannATyahooDOTcom> wrote in message
news:OCcFq5ARDHA.2636@TK2MSFTNGP10.phx.gbl...

> "Jason Burton" <j.burton@intelligence-agency.net> wrote in message
> news:vgh7hch4t24d6@corp.supernews.com...

> > Thanks Adrian,
> >
> > Thats a good ASP method, but I'm wondering if ie. SQL 2000 or in the

> stored

> > procedure itself theirs a way to ie. return a value if its presently

> being

> > used.. or refreshing..
> >
> > The stored proc I made just moves data, but scans over 100,000 records
> > first. Then every time it finds the records it wants to move, it does

> it

> > again til all of them are done. Then the proc is done. But I run it

> like

> > every other day.
> >
> > Any ideas? Anyone else have this experience? What do banking

> institutions

> > do for large amounts of data to expedite the process? This is dealing

> with

> > my SMTP server, (I do ODBC logging) so as a spam solution I check for

> 550

> > errors. (thats pretty much what it does..)
> >
> > Jason
> >
> >
> > "Adrian Forbes [ASP MVP]" <sorry@noemail.zzz> wrote in message
> > news:uWRhXpARDHA.2276@TK2MSFTNGP12.phx.gbl...

> > > > How in ASP can I check to see if a stored proc is running??
> > >
> > > Not really possible. Let's say the SP is running for another user?
> > >
> > > > The reason being is that when I execute this specific stored proc,

> it

> > > takes
> > > > a while.. and I dont want the
> > > > user to execute it again....
> > >
> > > Is the SP being run synchrously or asynchronously? You would have

> to

> > > control this using Session vars. Set in the session that the SP is

> being

> > > run, then clear the session value when the SP has finished
> > >
> > > Session("RunningSP") = true
> > > objconn.execute "...."
> > > Session("RunningSP") = false
> > >
> > >

> >
> >

> Checkout sp_who in BOL(Books OnLine). Basically it returns a result set
> of running processes. The stored procedure accepts (process id | user id
> | active) as filtering parameters. Ideally, if you have the process id
> of the procedure in question that's the best way to limit the result
> set.
>
> HTH
> -Chris
>
>

[Chris Hohmann]

"Jason Burton" <j.burton@intelligence-agency.net> wrote in message
news:vghcb1ablr6n03@corp.supernews.com...

> Thanks,
>
> So in reality, I could for example filter using the user id I use for
> processing the procedure.. (only one user uses it..)
>
> Then just check that result set if not empty..
>
> Am I correct with this analogy?
>
> Jason

<<End of message omitted for brevity>>

Yes, although you should probably filter on the "cmd" column to make
sure you are capturing the process running your specified procedure. I
don't know how your application is setup, but your sp user could be
spawning other processes. Something else to consider is that a stored
procedure that takes that long should probably not be instantiated via
ASP>ADO>OLEDB>TCP/IP>Agent>SQLServer. I'm sure I left out a few layers
but you get the idea. When you buy a car, you don't wait around at the
factory while it's being built (at least I hope not). You just pick it
up when it's finished. Same thing here, don't make your ASP page wait
around for the sp to finish executing. How about having the sp write to
a log table upon completion or better yet a log file since the data is
relatively static, or better still an XML based log file. That way you
can have ASP parse as you see fit.

HTH
-Chris

[Chris Hohmann]

"Jason Burton" <j.burton@intelligence-agency.net> wrote in message
news:vgi6pa9uicph85@corp.supernews.com...

> Do you know a site or anything w/SP's or SQL sample applications?

[url]http://www.aspfaq.com/show.asp?id=2403[/url]