call procedure from another procedure

Question

call procedure from another procedure

Mad-Dog

SSCertifiable

Points: 6117
More actions
May 16, 2010 at 2:01 am

#221581

Hi,
I'm running a proc that monitor my server every 2 min by sql agent job.
this proc duration is about 2 sec.
now i want in this 2 sec to call another proc and this another proc runs about 3 min(this new poc will not run every 2 min).
how i can make this 2 sec proc to call another proc that runs 3 min and still be in the cycle to run every 2 min by sql agent job.
can i call this new proc outside the 2 min proc or something like that?(i don't want to create sql agent job for this new proc)
THX.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

One option:

Determine the condition when the 3min-sproc needs to be called either within your 2se-sproc or in the next step in the job agent. Based on that condition fire a ServiceBroker event that will call your 3min sproc.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 2

ok.

how can i do the service broker event to call this sp?

THX

LutzM SSC Guru Points: 107049 More actions · Answer 3

Mad-Dog (5/16/2010)
ok.
how can i do the service broker event to call this sp?
THX

Nope, it's the other way around: you SEND a message to the Service Broker queue and within the RECEIVE statement of the Service Broker message you call your 3min-sp.

Wouldn't it be easier to run that 3min-sp independent of the 2min scheduled sp, e.g. by running a separate job? Or can the 3min-sp improved to prform in less than 2min?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 4

the call to the proc must be run from within the 2 min job.

is this is the only way to achieve my goal?

can call this proc from the 2 min proc with another spid maybe or something like that?

LutzM SSC Guru Points: 107049 More actions · Answer 5

So, what does that sproc do that takes 3min?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

I am not sure I follow this - you want to start a process that takes 3 minutes to run, every 2 minutes? That means the previous execution will still be running when your 2 minute job runs again.

You cannot do that with an agent job - since the next time you want it to run it will already be running and the job won't be started.

Service Broker is probably the only way you are going to be able to do this, unless you can improve the 3 minute process to less than the 2 minute cycle of the first job.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 7

i wrote that i want to fire the 3 min proc inside the 2 min schedule sql agent job but the proc will not interfere with the 2 min sql agent job,just call it and continue with the 2 min sched.

how i can setup the service broker to achieve what i want?

THX

homebrew01 SSC Guru Points: 55548 More actions · Answer 8

Mad-Dog (5/16/2010)
i wrote that i want to fire the 3 min proc inside the 2 min schedule sql agent job but the proc will not interfere with the 2 min sql agent job,just call it and continue with the 2 min sched.
how i can setup the service broker to achieve what i want?
THX

So, you want your 3 minute job to start every 2 minutes. Then you will have multiple 3 minute jobs running at the same time ?

twalston Mr or Mrs. 500 Points: 505 More actions · Answer 9

Can you explain what the 3 min proc is doing? I think a lot of the comments above mine are leading towards checking to see that the 3 min proc is optimized and really needs to take 3 min.