create procedure on top of another procedure
-
hello all,
i am very new to SQL Server and i am trying to create a store procedure. I already have a stored procedure called sp_whoisactive. Now i want to create a new store procedure that run the sp_whoisactive as below(sp_whoisactive is going to take 2 paramter in find_block_leader and destination_table) and obviously i want that to run every 5 seconds for next 10 mins? the Sp_whoisactive is a store procedure created by Adam Machanic and i am just trying to schedule this to run every 5 seconds....i am on SQL 2005
EXEC sp_WhoIsActive
@find_block_leaders = 1,
@destination_table = 'stem.dbo.active'
WAITFOR DELAY '00:00:05'
GO 120
i tried something like below but it dose not work...
Create Procedure [stem].[dbo].[dba_active]
EXEC sp_WhoIsActive
@find_block_leaders = 1,
@destination_table = 'stem.dbo.active'
WAITFOR DELAY '00:00:05'
GO 120
-
GO cannot exist inside a procedure. it is a batch seperator, so when you compile it, the system thinks the procedure is finished..i would imagine if you try that code, it tries to create the procedure 119 extra times.
what you want to do insead, is create a scheduled job, and have that job call your procedure every x minites instead.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank you for your reply.
I initially thought of that as well, as to run my job every 5 secs for 10 min. But then i notice in the job history it creates entries every 5 sec and hence filling up history of that job.
is there a major difference between the below and running my code every 5 sec for 10 mins though a schedule job ?? would the schedule job being ran every 5 secs hammer the server(just the firing up of job, not my code).....
my code here
WAITFOR DELAY '00:00:05'
GO 120
-
ii think you want a near-endless while loop instead; something like this:
declare @i int,
@err varchar(100)
set @i=1
WHILE @i < 120
BEGIN
EXEC sp_WhoIsActive
@find_block_leaders = 1,
@destination_table = 'stem.dbo.active'
WAITFOR DELAY '00:00:05'
SET @err = 'Progress So Far: Step ' + convert(varchar(30),ISNULL(@i,1)) + ' completed.'
raiserror (@err,0,1) with nowait
SET @i=@i + 1
END
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell, why the WHILE 0=0?
Jared
CE - Microsoft -
SQLKnowItAll (8/21/2012)
Lowell, why the WHILE 0=0?thanks for catching that!
it was a copy paste blunder form something i adapted; i edited my post to yank that out;
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (8/21/2012)
SQLKnowItAll (8/21/2012)
Lowell, why the WHILE 0=0?thanks for catching that!
it was a copy paste blunder form something i adapted; i edited my post to yank that out;
Darn, I was hoping I was missing some kind of cool trick was auto-magical 🙂
Jared
CE - Microsoft -
SQLKnowItAll (8/21/2012)
Lowell (8/21/2012)
SQLKnowItAll (8/21/2012)
Lowell, why the WHILE 0=0?thanks for catching that!
it was a copy paste blunder form something i adapted; i edited my post to yank that out;
Darn, I was hoping I was missing some kind of cool trick was auto-magical 🙂
lol the secret, elusive GO FAST button for SQL?
yeah, that's the ticket!
WHILE 0 = 0 WITH (NOLOCK)
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (8/21/2012)
SQLKnowItAll (8/21/2012)
Lowell (8/21/2012)
SQLKnowItAll (8/21/2012)
Lowell, why the WHILE 0=0?thanks for catching that!
it was a copy paste blunder form something i adapted; i edited my post to yank that out;
Darn, I was hoping I was missing some kind of cool trick was auto-magical 🙂
lol the secret, elusive GO FAST button for SQL?
yeah, that's the ticket!
WHILE 0 = 0 WITH (NOLOCK)
:w00t::hehe::w00t::hehe::w00t::hehe:
Jared
CE - Microsoft -
Awesome, that works like a charm. Thank you guys
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply