How to call a batch file to execute from an SP

Question

Post reply

How to call a batch file to execute from an SP

SQLisAwe5oMe

SSChampion

Points: 14052
More actions
March 20, 2013 at 11:15 am

#291206

Hi All,
Need your assistance please, I am not very good with scripting.
I have created a draft of SP, and I need syntax to make a call to a batch file(.bat) from within the SP. Once I have that I can incorporate it in the code and begin testing.
Can someone please provide sample script.
Thanks,
SueTons.
Regards,
SQLisAwe5oMe.

Viewing 15 posts - 1 through 15 (of 96 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Not really good idea to do so, but if you really need it, use xp_cmdshell.

xp_cmdshell 'c:\MyBatch.bat'

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 2

Eugene Elutin (3/20/2013)
Not really good idea to do so, but if you really need it, use xp_cmdshell.
xp_cmdshell 'c:\MyBatch.bat'

Thanks Eugene, what you mean by not really good idea?....what are the alternatives, if there is any? Please advise if you don't mind.

Thanks,

SueTons.

Regards,
SQLisAwe5oMe.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

SQLCrazyCertified (3/20/2013)
Eugene Elutin (3/20/2013)
Not really good idea to do so, but if you really need it, use xp_cmdshell.
xp_cmdshell 'c:\MyBatch.bat'
Thanks Eugene, what you mean by not really good idea?....what are the alternatives, if there is any? Please advise if you don't mind.
Thanks,
SueTons.

Why do you want to run batch file from within SQL Server procedure?

What this batch is going to do?

There is a great alternative: don't do it from sql.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 4

Eugene Elutin (3/20/2013)
SQLCrazyCertified (3/20/2013)
Eugene Elutin (3/20/2013)
Not really good idea to do so, but if you really need it, use xp_cmdshell.
xp_cmdshell 'c:\MyBatch.bat'
Thanks Eugene, what you mean by not really good idea?....what are the alternatives, if there is any? Please advise if you don't mind.
Thanks,
SueTons.
Why do you want to run batch file from within SQL Server procedure?
What this batch is going to do?
There is a great alternative: don't do it from sql.

Well, this SP should be pulling the job tables every 5 min or so and seeing if any are running longer than defined SLA for that job, so, if the SP finds that any job is running longer than its defined SLA, it should trigger the .bat job and .bat job will create an automated ticket using our monitoring tool. Let me know if you have a better idea.

SueTons.

Regards,
SQLisAwe5oMe.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

Well, this SP should be pulling the job tables every 5 min or so and seeing if any are running longer than defined SLA for that job, so, if the SP finds that any job is running longer than its defined SLA, it should trigger the .bat job and .bat job will create an automated ticket using our monitoring tool. Let me know if you have a better idea.
SueTons.

How are you going to schedule your sp to pull job tables every 5 min?

The best way will be if your sp will return some state, which will be a signal for the next step to run your batch file.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 6

Eugene Elutin (3/20/2013)
Well, this SP should be pulling the job tables every 5 min or so and seeing if any are running longer than defined SLA for that job, so, if the SP finds that any job is running longer than its defined SLA, it should trigger the .bat job and .bat job will create an automated ticket using our monitoring tool. Let me know if you have a better idea.
SueTons.
How are you going to schedule your sp to pull job tables every 5 min?
The best way will be if your sp will return some state, which will be a signal for the next step to run your batch file.

Actually, I am trying to help out a developer who created the SP, I don't have the SP handy. So, how can I incorporate xp_cmdshell 'c:\MyBatch.bat' within the SP? Maybe I am asking a bit too much, hope you don't mind.

SueTons.

Regards,
SQLisAwe5oMe.

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 7

What are different ways you can call a batch file within an SP?

SueTons.

Regards,
SQLisAwe5oMe.

Michael L John One Orange Chip Points: 27056 More actions · Answer 8

1. Take the call to xp_cmdshell out of the SP.

2. In the SP, test for the condition that would trigger the call to the batch file.

a. Return success if the condition is FALSE

b. Return failure if the condition is TRUE

3. Create a SQL Job.

a. Call the procedure in the first step.

b. In the advanced properties of the step,

1. Set the "on success action" to "Quit the job reporting success"

2. Set the "On Failure Action" to "Go to the next step"

c. In the second step, set the "type" to operating system and call the batch file.

By default xp_cmdshell is disabled in SQL 2005 and up. It's a big security issue. Batch files and T-SQL are not meant to play together. They are for two different uses.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 9

Thanks Michael, appreciate it.

SueTons.

Regards,
SQLisAwe5oMe.

Michael L John One Orange Chip Points: 27056 More actions · Answer 10

Glad to help!

Good luck!!!

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Sergiy SSC Guru Points: 110208 More actions · Answer 11

Or alternative method:

1. Create a procedure which returns a list of the jobs which "are running longer than defined SLA"

2. Add a bcp command to your .BAT file which runs that procedure and saves the output into a file.

3. Check the size of the file. If it is not empty - execute the rest of the .BAT (option - CALL ???.BAT ) to raise tickets for each line in the file.

4. Drop the file.

You may turn it into a Windows task running every 5 minutes or make it a service - your choice.

But you better stay away from calling .BAT files from SQL Server.

If something goes wrong during execution ("overwrite the file Y/N", "No such folder, do you want to create it?", "Delete read-only file, are you sure?", lack of disk space or anything else) - SQL does not have means to handle errors raised in cmd shell. You job will be stuck, will be sitting quietly wayting for command shell to complete it task which is waiting for console input in a session with hidden console, no long running jobs will be discovered, no tickets raised, everybody's happy, until customers start to complain about the system being down for half of the day. 🙂

From my experiense - this scenario materialises every time when somebody creates an SQL task calling a .BAT file.

No exclusions so far.

If they kill the job when it's not finished within, say, a minute or so, they end up killing the Windows server with thousands of command shells started on the background and waiting for a human input.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 12

Michael L John (3/20/2013)
1. Take the call to xp_cmdshell out of the SP.
2. In the SP, test for the condition that would trigger the call to the batch file.
a. Return success if the condition is FALSE
b. Return failure if the condition is TRUE
3. Create a SQL Job.
a. Call the procedure in the first step.
b. In the advanced properties of the step,
1. Set the "on success action" to "Quit the job reporting success"
2. Set the "On Failure Action" to "Go to the next step"
c. In the second step, set the "type" to operating system and call the batch file.

Nicely done and probably the way I'd do it, as well.

By default xp_cmdshell is disabled in SQL 2005 and up. It's a big security issue. Batch files and T-SQL are not meant to play together. They are for two different uses.

I'll have to say that's mostly old wive's tales, myths, and huge misunderstandings. xp_CmdShell is NOT an issue on a properly locked down system. The real problem is that few take the time to actually and properly lock down their system.

Consider this. Who can run xp_CmdShell? Most people will say only those with "SA" privs and they'd be partially correct. If someone is foolish enough to give a low prived user proxy privs to run it, that's another way. Most people very incorrectlyresort to that method when they want a user to be able to run a proc with xp_CmdShell in it and that's probably the worst way to do it.

But, I digress. If you give no one proxy privs and only DBAs have "SA" privs, who can run xp_CmdShell? The answer is only DBAs and an attacker that breaks in as one of those DBAs.

Now, knowing that, you have "wisely" (notice the quotes) turned off xp_CmdShell because it's a "security risk" (notice the quotes again). If an attacker does break into your system as a DBA, then he HAS "SA" privs and can turn it on. In fact, since he's actually expecting it to be off, his attack code is setup to automatically turn it on and he raises holy hell in you system after that.

So what did having xp_CmdShell turned off accomplish? [font="Arial Black"]NOTHING[/font]. It only slowed the attacker down for about 1 ms.

No, let's turn the table. You've very wisely (notice, no quotes) locked down your system so that no apps and no users have anything more than DBO privs (I usually try to restrict to just "PUPLIC" privs but DBO will do for this example). xp_CmdShell is turn on!

Remember, there are no proxied users that can run it. Who, besides, the DBAs, can use xp_CmdShell? Will an attacker that breaks in as a non-DBA user or app be able to run it EVEN IF IT'S TURNED ON?

[font="Arial Black"]NO![/font]

Now, let's just say that you have an app (or user) that must be allowed to run a stored procedure on an ad hoc, on demand basis that has a call to xp_CmdShell in it. What do you do? Give the user "SA" privs? We both know that's stupid. Setup a proxy and give just that user privs to run xp_CmdShell directly? That's even more stupid because it also gives the DBAs a false sense of security.

So what to do?

There are multiple methods (Google them... not trying to write an article here) to setup the stored procedure itself to have the privs to run xp_CmdShell and only in the manner prescribed in the proc. A user with only "PUBLIC" privs and "exec" privs on that proc can run the stored procedure just fine yet they cannot run xp_CmdShell directy. Shoot... they can't even see what's inside the proc. THAT's the right way to use xp_CmdShell!

Like I said, xp_CmdShell is NOT a security risk. Only poor security and a poor understanding of security is a security risk.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 13

Thank you so much for all of your feedbacks.

SueTons.

Regards,
SQLisAwe5oMe.

Michael L John One Orange Chip Points: 27056 More actions · Answer 14

I stand corrected.

BUT I also stand by the statement because unfortunately poor security seems to be the norm. It seems as if DBA's are so busy with everything else that security is overlooked.

I will amend the statement to be:

"xp_cmdshell CAN be a security risk"

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/