April 30, 2021 at 4:50 pm
True dat! Things like a Cmd Task, etc, work fine... well, kind of. With the understanding that I've not recently spent much time (I'm thinking precisely zero in the last decade) trying to figure out how to do it, how do you pass parameters between job steps in the same job without having to save to a permanent table in a database somewhere? How do you process the resulting output for a CmdExec task when you do a simple thing like a bare-bones DIR command to get a list of file names?
And no... I'm not being sarcastic or ironic here. I don't know of an easy way to pass results of one job step to another... not even a parameter. If you know, I'm all ears because then I could actually make alternative recommendations to the use of xp_CmdShell.
Heh.. and shifting gears a bit, I don't respond very well to suggestions of using SSIS. 😀
Sorry - I don't follow...how are you passing results of one job step to another job step when using xp_CmdShell? If your concern is being able to write a 'procedure' and use it with different parameters - that is easy to do using a PS script. Of course, it won't be stored in SQL Server and will need to be stored in the file system - but that shouldn't be too much of an issue.
You can also store all of the code in the job step - and set variables at the top to make it dynamic if you wanted.
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
April 30, 2021 at 5:20 pm
Sorry - I don't follow...how are you passing results of one job step to another job step when using xp_CmdShell?
That's just it... I'm not. 😀 I don't need to because the only thing the job does is kick off a stored procedure. I don't need for t T-SQL task/step to feed a Cmd Exec task/step to feed another T-SQL Task/Step, etc, etc, etc.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 30, 2021 at 5:55 pm
Jeffrey Williams wrote:Sorry - I don't follow...how are you passing results of one job step to another job step when using xp_CmdShell?
That's just it... I'm not. 😀 I don't need to because the only thing the job does is kick off a stored procedure. I don't need for t T-SQL task/step to feed a Cmd Exec task/step to feed another T-SQL Task/Step, etc, etc, etc.
Okay - so what would be different with using a PS script in a single job step? If the purpose of calling out to the OS is to get some data that can then be used in further operations within SQL, then xp_cmdshell is probably the better option. But - if the goal is to call out to the OS to do some task external to SQL - the PS script is probably the better option.
Not trying to say one is better or worse...just that there are other options.
In this instance - calling out to Robocopy to copy backup files to a NAS doesn't need xp_cmdshell. A separate job step - using the PS subsystem to execute Robocopy with the right source, destination - and flags is easy to set up. It can be extended by wrapping the call in a script where you can then capture the output, parse it - and save the data in a table or log file - as needed.
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
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply