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.
| Author |
Topic |
|
tfountain
Constraint Violating Yak Guru
491 Posts |
 Posted - 2008-05-12 : 15:40:01
|
| Lets say I have a SQL Server Agent job that has several steps. For our example lets say 2 steps. Each step executes stored procedures with output parameters like so:Step 1: EXECUTE <db>.<schema>.<proc A> @outputVar=@outputVar OUTPUTStep 2: EXECUTE <db>.<schema>.<proc A> @inputVar=@outputVarIs it possible to pass the @outputVar variable as an input to the procedure in step 2? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-12 : 15:48:48
|
| Why not just put them in the same job step? The only way for the other step to see it would be to store the value somewhere that is permanent such as in a table or a file. Variables go out of scope.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
 |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-05-12 : 15:53:08
|
quote:
Originally posted by tkizer
Why not just put them in the same job step? The only way for the other step to see it would be to store the value somewhere that is permanent such as in a table or a file. Variables go out of scope.
My only purpose for keeping each procedure in it's own step is to avoid using my own error handling... or is that a concern? If the first procedure fails will the second execute if coded to execute one after another? I supposed I could just wrap it all in a BEGIN TRY...END TRY...BEGIN CATCH...END CATCH statements (lights go off now...)... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-12 : 15:56:18
|
| Yes you'd need to use a transaction.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-05-12 : 16:01:20
|
I'm just going to code a wrapper procedure that the job will call instead (the obvious approach!! :)). As far as the transaction goes, I'm going to skip that. In my case there are just 3 simple steps:- Initiate a BACKUP DATABASE command on a remote server A
- Copy backup file(s) from remote server A to remote server B
- Initiate a RESTORE DATABASE command on remote server B
All this will be done from a central server so in the entire process there are 3 servers in play. The process is written such that if a failure happens we can just rerun it. |
|
|
|
|
|
|
SQL Server Agent Job