Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to call a batch file to execute from an SP


How to call a batch file to execute from an SP

Author
Message
SQLisAwE5OmE
SQLisAwE5OmE
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 3062
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.
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478
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
SQLisAwE5OmE
SQLisAwE5OmE
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 3062
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
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478
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
SQLisAwE5OmE
SQLisAwE5OmE
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 3062
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
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478

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
SQLisAwE5OmE
SQLisAwE5OmE
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 3062
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
SQLisAwE5OmE
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 3062
What are different ways you can call a batch file within an SP?

SueTons.

Regards,
SQLisAwe5oMe.
Michael L John
Michael L John
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2054 Visits: 7399
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
SQLisAwE5OmE
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 3062
Thanks Michael, appreciate it.

SueTons.

Regards,
SQLisAwe5oMe.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search