Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

stored procedure Expand / Collapse
Author
Message
Posted Thursday, April 30, 2009 3:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 12, 2010 8:36 AM
Points: 26, Visits: 98
how to check if a stored procedure is currently running - can anyone plz help with a solution?
Post #707494
Posted Thursday, April 30, 2009 4:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,556, Visits: 2,593
Double post, continue discussions here

--Ramesh

Post #707520
Posted Thursday, April 30, 2009 4:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,231, Visits: 3,483
Hi,

try this sp

create procedure Spinrun (@Parameter varchar(90))
as
begin

create table #temp
(
eventtype nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)

declare @SPID varchar(10)
DECLARE OPENTRANS CURSOR FOR
SELECT SPID from master.dbo.sysprocesses (nolock)
OPEN OPENTRANS
FETCH NEXT FROM OPENTRANS into @SPID
WHILE @@FETCH_STATUS = 0
BEGIN

declare @SQL nvarchar(100)
set @SQL = N'dbcc inputbuffer ('+@SPID+')'
insert into #temp exec (@SQL)

FETCH NEXT FROM OPENTRANS into @SPID
END
CLOSE OPENTRANS
DEALLOCATE OPENTRANS


declare @Parameter varchar(100)
select @Parameter = ('%'+@Parameter+'%')
if exists (select 1 from #temp where eventinfo like @Parameter)
print 'SP is Running'
else
print 'SP is not Running'

end

-----
exec Spinrun 'SP NAME'
-----


ARUN SAS


Post #707544
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse