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 1234»»»

Execute SQL job through batch file Expand / Collapse
Author
Message
Posted Monday, May 25, 2009 6:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 6:52 AM
Points: 143, Visits: 553
Comments posted to this topic are about the item Execute SQL job through batch file

--Divya
Post #722937
Posted Friday, June 5, 2009 12:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 10:48 AM
Points: 2, Visits: 21
OLD!! osql is depricated, and not all users will have osql, isql or sqlcmd available
Better to use a small vbs script
which allows both Trusted connections and embedded user name and password
Post #729437
Posted Friday, June 5, 2009 2:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 28, 2014 2:43 AM
Points: 47, Visits: 207
works for me :)

I also used psexec.exe to allow a user to execute the batch file on a remote server.
Post #729484
Posted Friday, June 5, 2009 5:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 13, 2013 2:25 AM
Points: 2, Visits: 47
Hi,

The ECHO 'Job execution completed' is a little misleading.
I think it should be 'Job started', since sp_start_job starts the job, it does not wait until the job is finished.

Cheers,
John
Post #729563
Posted Friday, June 5, 2009 5:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 18, 2014 6:14 AM
Points: 40, Visits: 337
Might I also suggest a:

time /t

before and after execution -

for a little more information.
Post #729581
Posted Friday, June 5, 2009 5:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 11:27 PM
Points: 170, Visits: 553
What about discussing security implications? Such as:

Permissions required in SQL Server to execute
Where does the batch file live and what rights are required for the share?
How do you stop users editing the batch file?

etc..
Post #729604
Posted Friday, June 5, 2009 6:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 27, 2012 6:32 AM
Points: 21, Visits: 97
I'm sorry, but he doesn't deliver on his promise to "... execute the job easily with a single click." As you will notice at the end of the article he instructs you to "Just double click on it..." I am very disappointed that it actually takes twice as many clicks.

Regardless, thanks for the tip. It will come in handy.
Post #729624
Posted Friday, June 5, 2009 6:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 2:58 PM
Points: 1,141, Visits: 944
I would agree with some of the other feedback about including security considerations not just at a database level but also at the file system level. Otherwise, I can't think of this script being useful other than in a sandbox environment.
Post #729640
Posted Friday, June 5, 2009 7:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 27, 2012 6:32 AM
Points: 21, Visits: 97
There are a number of batch file compilers you could use to turn the batch file into an .exe file. This is an effective way of keeping users out of the batch file.
Post #729668
Posted Friday, June 5, 2009 7:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 4, 2014 6:45 AM
Points: 3, Visits: 60

Hello,

In fact as mentioned in this forum, the sp_start_job is not synchronous, so you don't know if the job has failed of not.

Moreover, to execute a job you must be associated to a windows connection which belongs, for example to a local windows group GRP_FOR_JOB.

This group GRP_FOR_JOB must have SQLAgentOperatorRole role on msdb database.

Furthermore, if you want to wait for the failure or succes of the job, you must use stored procedures that I have implemented (and that can be found on internet).

Post #729683
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse