Execute SQL job through batch file

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Divya Agrawal » Execute SQL job through batch file

38 posts, Page 1 of 4

1 2 3 4 »»»

Execute SQL job through batch file

Rate Topic

Display Mode

Topic Options

Author

Message

Divya Agrawal

Posted Monday, May 25, 2009 6:17 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:59 AM
Points: 139, Visits: 469

Comments posted to this topic are about the item Execute SQL job through batch file

--Divya

Post #722937

D Randall Pitkin

Posted Friday, June 05, 2009 12:25 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, August 28, 2012 1:52 PM
Points: 2, Visits: 14

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

Paul Manning-255673

Posted Friday, June 05, 2009 2:44 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 2:59 AM
Points: 25, Visits: 127

works for me :)

I also used psexec.exe to allow a user to execute the batch file on a remote server.

Post #729484

johnvanbeek

Posted Friday, June 05, 2009 5:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 8:50 AM
Points: 2, Visits: 46

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

ian.hadlington

Posted Friday, June 05, 2009 5:20 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 4:21 AM
Points: 40, Visits: 258

Might I also suggest a:

time /t

before and after execution -

for a little more information.

Post #729581

David Sorauer

Posted Friday, June 05, 2009 5:53 AM

SSC-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

mpv-1060508

Posted Friday, June 05, 2009 6:19 AM

Grasshopper

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. Wink

Regardless, thanks for the tip. It will come in handy.

Post #729624

SA-1

Posted Friday, June 05, 2009 6:46 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 7:35 AM
Points: 1,140, Visits: 933

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

mpv-1060508

Posted Friday, June 05, 2009 7:19 AM

Grasshopper

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

pgineste

Posted Friday, June 05, 2009 7:31 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 10:18 AM
Points: 3, Visits: 36

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 »

38 posts, Page 1 of 4

1 2 3 4 »»»

Permissions