stored procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » SQL Server Agent » stored procedure

stored procedure

Rate Topic

Display Mode

Topic Options

Author

Message

harriet.louis

Posted Thursday, April 30, 2009 3:02 AM

SSC 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

Ramesh Saive

Posted Thursday, April 30, 2009 4:07 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, February 22, 2013 12:03 AM
Points: 2,555, Visits: 2,587

Double post, continue discussions here

--Ramesh

Post #707520

arun.sas

Posted Thursday, April 30, 2009 4:39 AM

Ten 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 »

Permissions