December 18, 2008 at 11:35 pm
Hi,
In one of the interview they asked me a question whether stored porcedure autofires.
I said no, and to this they replied that we have an option called as sp_procoption.
When I read that, I came to know tat will fire wen the sql server agent is restarted.
Does this mean autofire exactly., I mean what if the sql server agent is switched On and never switched off, then this procedure will never be fired.
Secondly its also said tat we cant use it other than in master database. Is it so?
Kindly clarify.
Regards
Hema
December 18, 2008 at 11:44 pm
well i would define triggers as procedures capable of autofiring based on certain events. difference is they donot accept parameters.
December 19, 2008 at 5:58 am
It really depends on what you mean by "auto"
There is the startup proc, and there can be only one. You can also set up triggers to respond to ddl or dml. You can set up alerts through SQL Agent that will automatically fire jobs that can "auto" fire procedures. In 2008 you can get into Extended Events and responses there.
So, depending on what you mean, there are a number of options for "auto" firing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 20, 2008 at 6:56 am
Thank you Grant and Pradeep
December 21, 2008 at 9:56 am
Add service broker, which can be set to "auto-fire" procs.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply