Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

How to call a batch file to execute from an SP Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 11:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:04 PM
Points: 383, Visits: 2,351
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.
Post #1433355
Posted Wednesday, March 20, 2013 11:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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
Posted Wednesday, March 20, 2013 11:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:04 PM
Points: 383, Visits: 2,351
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.
Post #1433367
Posted Wednesday, March 20, 2013 11:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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
Posted Wednesday, March 20, 2013 12:21 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:04 PM
Points: 383, Visits: 2,351
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.
Post #1433403
Posted Wednesday, March 20, 2013 12:31 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874

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
Posted Wednesday, March 20, 2013 12:55 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:04 PM
Points: 383, Visits: 2,351
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.
Post #1433427
Posted Wednesday, March 20, 2013 1:52 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:04 PM
Points: 383, Visits: 2,351
What are different ways you can call a batch file within an SP?

SueTons.


Regards,
SQLisAwe5oMe.
Post #1433460
Posted Wednesday, March 20, 2013 2:02 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:17 AM
Points: 859, Visits: 2,618
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
Posted Wednesday, March 20, 2013 2:34 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:04 PM
Points: 383, Visits: 2,351
Thanks Michael, appreciate it.

SueTons.


Regards,
SQLisAwe5oMe.
Post #1433490
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse