August 25, 2008 at 11:48 am
Jason Wisdom (8/25/2008)
Steve Jones - Editor (8/25/2008)
I don't think you can do this dynamically directly from a job. You can call a stored proc in the job, build the parameter in the proc and call xp_cmdshell, or possibly another non-scheduled job and alter the job step in real time.Maybe use sp_update_jobstep to do this.
Okay, so can a stored procedure run a CmdExec batch file, passing a parameter to that batch file?
Not using xp_cmdshell.
What do you think the difference between these two things is?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 25, 2008 at 12:55 pm
rbarryyoung (8/25/2008)
Jason Wisdom (8/25/2008)
Steve Jones - Editor (8/25/2008)
I don't think you can do this dynamically directly from a job. You can call a stored proc in the job, build the parameter in the proc and call xp_cmdshell, or possibly another non-scheduled job and alter the job step in real time.Maybe use sp_update_jobstep to do this.
Okay, so can a stored procedure run a CmdExec batch file, passing a parameter to that batch file?
Not using xp_cmdshell.
What do you think the difference between these two things is?
One uses xp_cmdshell, the other asks if it is possible to not use xp_cmdshell.
August 25, 2008 at 12:59 pm
You can use an Active X step and write vbscript code.
Okay. We could be very close to a solution here.
How can an ActiveX step, using VBScript, execute a DOS batch file?
August 25, 2008 at 1:21 pm
Jason Wisdom (8/25/2008)
You can use an Active X step and write vbscript code.
Okay. We could be very close to a solution here.
How can an ActiveX step, using VBScript, execute a DOS batch file?
You can use wshShell.Run.
Yet at this point I'm more curious than anything ... what exactly is it you are doing that is requiring all this? $10 says there is an easier way ...
August 25, 2008 at 1:45 pm
Yet at this point I'm more curious than anything ... what exactly is it you are doing that is requiring all this? $10 says there is an easier way ...
I have 4 DOS Batch files, which I need to run manually about 1x/month.
These batch files accept a date as a parameter.
Instead of opening a CMD shell, navigating to the folder, remembering each filename, etc.,
I would rather fire off ONE command.
I just need to send one date value to the 4 batch files.
That is all.
August 25, 2008 at 1:53 pm
So why bother with SQL? Sounds like VBScript would be a much better, quicker, and cleaner solution. Is the date derived from a table or something?
August 25, 2008 at 2:06 pm
Adam Bean (8/25/2008)
So why bother with SQL? Sounds like VBScript would be a much better, quicker, and cleaner solution. Is the date derived from a table or something?
I don't need SQL. I just need something that can run 4 batch files & pass a date parameter.
The date does not come from a table - I receive a phone call, "Run it for this date", and I hand-type it in.
ActiveX looks good - how do I execute a DOS command through an ActiveX script? (Please do not say xp_cmdshell 🙂 )
August 25, 2008 at 2:18 pm
Jason Wisdom (8/25/2008)
Adam Bean (8/25/2008)
So why bother with SQL? Sounds like VBScript would be a much better, quicker, and cleaner solution. Is the date derived from a table or something?I don't need SQL. I just need something that can run 4 batch files & pass a date parameter.
The date does not come from a table - I receive a phone call, "Run it for this date", and I hand-type it in.
ActiveX looks good - how do I execute a DOS command through an ActiveX script? (Please do not say xp_cmdshell 🙂 )
Then you don't need SQL at all ... VBScript is what you'd want. As stated earlier, you can use command line from a vbscript - wshShell.Run. Than you can schedule if you'd like via windows scheduler or run them by hand. You good at writing VBScript? It's about the only other language (if you can call it that :)) other than SQL I'm decent at ... If you need a hand I could probably build you a test script that you could alter appropriately.
August 25, 2008 at 2:37 pm
Jason Wisdom (8/25/2008)
rbarryyoung (8/25/2008)
Jason Wisdom (8/25/2008)
Okay, so can a stored procedure run a CmdExec batch file, passing a parameter to that batch file?Not using xp_cmdshell.
What do you think the difference between these two things is?
One uses xp_cmdshell, the other asks if it is possible to not use xp_cmdshell.
Heh. No, I am asking: what is the security difference between xp_CmdShell and a CmdExec Job step?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 26, 2008 at 5:09 am
Heh. No, I am asking: what is the security difference between xp_CmdShell and a CmdExec Job step?
If xp_cmdshell or CmdExec are run by an account with sysadmin authority, they both run using the permissions of the sql server service account. If they are run by an account that does NOT have sysadmin, they run as described below...
In SQL Server 2005 and above, the CmdExec step runs under a subsystem proxy account. Each job can use a different proxy account, so permissions can be tailored for the task in hand. Often, the job owner is set up as the proxy so there is a single container for the permissions that the job uses.
xp_cmdshell always runs under the xp_cmdshell proxy. This applies if xp_cmdshell is run within a stored procedure or directly in a job step - it will always use the same xp_cmdshell proxy regardless of the permissions of the caller. This means the xp_cmdshell proxy must have permissions for all of the things it is asked to do, which means it will tend to have too much permission for any given task.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 26, 2008 at 8:25 am
Then you don't need SQL at all ... VBScript is what you'd want. As stated earlier, you can use command line from a vbscript - wshShell.Run. Than you can schedule if you'd like via windows scheduler or run them by hand. You good at writing VBScript? It's about the only other language (if you can call it that :)) other than SQL I'm decent at ... If you need a hand I could probably build you a test script that you could alter appropriately.
Hi...
Actually, if you would create me a basic VBScript, that would make my day. 😀
Basically I'm just lookign to run a static path with a given date.
Say....
BEGIN
Set dtmRunDate = '2008-05-15' (for example)
Set strRunPath1 = 'C:\scripts\runbatch1.bat ' + dtmRunDate
Set strRunPath2 = 'C:\scripts\runbatch2.bat ' + dtmRunDate
Set strRunPath3 = 'C:\scripts\runbatch3.bat ' + dtmRunDate
Set strRunPath4 = 'C:\scripts\runbatch4.bat ' + dtmRunDate
EXEC(strRunPath1)
EXEC(strRunPath2)
EXEC(strRunPath3)
EXEC(strRunPath4)
END
Get what I mean?
Thank you again. 🙂
August 26, 2008 at 8:29 am
Heh. No, I am asking: what is the security difference between xp_CmdShell and a CmdExec Job step?
Okay. I get what you're asking now. 🙂
xp_cmdshell runs from a T-SQL query window (or Stored Procedure, etc.), is disabled by default, and needs an advanced sp_configure to enable it.
CmdExec is not only enabled by default, it CAN'T be disabled, although it is set by default to require sysadmin privs to run it. It may only be invoked in a Job (Sql Agent). Can't run it from a SP or T-SQL.
I could be wrong about the CmdExec. I've only used it for jobs, and yes I restrict it to sysadmin acct's. In fact I set my Jobs to run as SA because I've seen turnover in the past where an AD account (old DBA) owns jobs, the AD account gets disabled, and all hell breaks loose the next day.
But I digress.......
August 26, 2008 at 8:30 am
Can't use sp_update_jobstep to change the commend you run?
August 26, 2008 at 8:51 am
Steve Jones - Editor (8/26/2008)
Can't use sp_update_jobstep to change the commend you run?
So....
Step 1 would be sp_update_jobstep to change steps 2-5.
Step 2 would run Script 1
Step 3 would run Script 2
Step 4 would run Script 3
Step 5 would run Script 4
Step 6 sends out an email success confirmation, including the date value in its title/body.
I would still need for Step 1, sp_update_jobstep, to contain a local variable that has the date value.
How do I do this?
August 26, 2008 at 10:44 am
Jason, did my email go through?
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply