<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
T-SQL (SS2K8)
»
How to call a batch file to execute from an...
97 posts, Page 1 of 10
1
2
3
4
5
»
»»
How to call a batch file to execute from an SP
Rate Topic
Display Mode
Topic Options
Author
Message
SQLCrazyCertified
SQLCrazyCertified
Posted Wednesday, March 20, 2013 11:15 AM
Old Hand
Group: General Forum Members
Last Login: Today @ 3:45 PM
Points: 312,
Visits: 1,863
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.
Post #1433355
Eugene Elutin
Eugene Elutin
Posted Wednesday, March 20, 2013 11:20 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 11:22 AM
Points: 2,541,
Visits: 4,370
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!"
(So many miracle inventions provided by MS to us...)
How to post your question to get the best and quick help
Post #1433358
SQLCrazyCertified
SQLCrazyCertified
Posted Wednesday, March 20, 2013 11:31 AM
Old Hand
Group: General Forum Members
Last Login: Today @ 3:45 PM
Points: 312,
Visits: 1,863
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.
Post #1433367
Eugene Elutin
Eugene Elutin
Posted Wednesday, March 20, 2013 11:41 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 11:22 AM
Points: 2,541,
Visits: 4,370
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!"
(So many miracle inventions provided by MS to us...)
How to post your question to get the best and quick help
Post #1433374
SQLCrazyCertified
SQLCrazyCertified
Posted Wednesday, March 20, 2013 12:21 PM
Old Hand
Group: General Forum Members
Last Login: Today @ 3:45 PM
Points: 312,
Visits: 1,863
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.
Post #1433403
Eugene Elutin
Eugene Elutin
Posted Wednesday, March 20, 2013 12:31 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 11:22 AM
Points: 2,541,
Visits: 4,370
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!"
(So many miracle inventions provided by MS to us...)
How to post your question to get the best and quick help
Post #1433409
SQLCrazyCertified
SQLCrazyCertified
Posted Wednesday, March 20, 2013 12:55 PM
Old Hand
Group: General Forum Members
Last Login: Today @ 3:45 PM
Points: 312,
Visits: 1,863
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.
Post #1433427
SQLCrazyCertified
SQLCrazyCertified
Posted Wednesday, March 20, 2013 1:52 PM
Old Hand
Group: General Forum Members
Last Login: Today @ 3:45 PM
Points: 312,
Visits: 1,863
What are different ways you can call a batch file within an SP?
SueTons.
Post #1433460
Michael L John
Michael L John
Posted Wednesday, March 20, 2013 2:02 PM
Right there with Babe
Group: General Forum Members
Last Login: Today @ 4:22 AM
Points: 737,
Visits: 2,080
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
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Post #1433467
SQLCrazyCertified
SQLCrazyCertified
Posted Wednesday, March 20, 2013 2:34 PM
Old Hand
Group: General Forum Members
Last Login: Today @ 3:45 PM
Points: 312,
Visits: 1,863
Thanks Michael, appreciate it.
SueTons.
Post #1433490
« Prev Topic
|
Next Topic »
97 posts, Page 1 of 10
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
