Agent job question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Agent job question

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-05-05 : 22:47:47

We have 2 databases, PBS and SUPPL does this job in fact run the stored proc stage on both of them?

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Refresh SpendAnalyzer Caches',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC dbo.SpendAnalyzer_Stage
EXEC PBS.dbo.SpendAnalyzer_Stage',
@database_name=N'SUPPL',
@flags=0

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-05 : 23:56:50

You query is not clear for me atleast..
What i understand is that you want to run a sp on two database using a same job.. If my understanding is correct then my answer is yes you can do it..

you need to add two steps in the job. Example of one step is given below.. You will require to specify the database_name in the step.

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC PR_POP_SCD_CONTROL',
@database_name=N'HNT_DWH',
@flags=0

If my understanding is wrong then let me know of the same.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-05-06 : 07:24:58

Yes it does because you have two EXEC statements in the @command. Both EXEC-commands are run in the scope of the SUPPL-database but since you're specifying the PBS-database in the second EXEC statement it runs there instead.

I would however recomend that you separate these two into separate job steps. It would provide better error handling and more readability.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-05-06 : 07:27:51

One clarification: What I said is only true if the procedure is present in *both* databases...if the SpendAnalyzer_Stage procedure is only present in the SUPPL database it will not work. It's not possible to run a procedure against another database like this, it will have to be present in both.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-05-06 : 09:09:37

dont know, it seems to have worked on both

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources