Execute SQL job through batch file

Question

Post reply

Execute SQL job through batch file

Divya Agrawal

Hall of Fame

Points: 3144
More actions
May 25, 2009 at 6:17 am

#200795

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

Viewing 15 posts - 1 through 15 (of 37 total)

You must be logged in to reply to this topic. Login to reply

D Randall Pitkin SSC Enthusiast Points: 132 More actions · Answer 1

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

phingers Ten Centuries Points: 1041 More actions · Answer 2

works for me 🙂

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

johnvanbeek SSC Enthusiast Points: 102 More actions · Answer 3

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

ian.hadlington SSC Veteran Points: 232 More actions · Answer 4

Might I also suggest a:

time /t

before and after execution -

for a little more information.

David Sorauer SSCrazy Points: 2874 More actions · Answer 5

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

mpv-1060508 SSC Eights! Points: 885 More actions · Answer 6

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.

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 7

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.

mpv-1060508 SSC Eights! Points: 885 More actions · Answer 8

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.

pgineste SSC Veteran Points: 239 More actions · Answer 9

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

jpratt-797544 SSC Eights! Points: 900 More actions · Answer 10

Whatever others say, I think this is great. I have always needed the ability to execute a SQL Agent job from a web page in an on-demand fashion, and the batch file approach is a good runner up. I knew about OSQL, but not the sp_start_job SP. I will surely investigate this and other similar ways to fire the job on demand.

FYI, you can always handle security on a batch file by setting "Run As" permissions on a shortcut to the batch file. That way, the batch file can be put in a secure area, and the shortcut can be set to run as the required security account.

Another FYI, if you set your middle mouse wheel click to act as a double-click, you can execute the batch file with a single click. :w00t:

J Pratt

Martin Vrieze SSCrazy Points: 2760 More actions · Answer 11

I apply this technique to run very sophisticated database update processes on the systems I manage. In my case, we run a combination of SQLCMD and BCP to execute large database updates and reports from those updates.

It is very easy to make changes to the processes and it provides the flexibility of executing on demand when the process simply cannot be scheduled reliably.

BTW...I agree with the comments by the user about using OSQL...be sure to check books online for the new SQLCMD command line syntax and write your batch files to that spec instead of the OSQL spec.

djjwu Old Hand Points: 316 More actions · Answer 12

To tell the truth, I don't think this solution is highly recommended. As we could see you are using window authentication to connect to SQL in your batch file. If user window account has less privilege or even has no access privilege to SQL, I don't think this solution will work.

JStiney Say Hey Kid Points: 671 More actions · Answer 13

The use of cmd files to execute sql is a powerful tool that is underutilized, if the lack of posts is any indicator.

I use cmd files to run almost all SQL Server jobs.

The following example shows how to run a database space report on multiple servers using SQL in a central library. It eliminates the need to create duplicate SQL on each server. All output is piped to the reports directory so you have a record of the job and most errors that occur, if needed.

I create a directory structure on each server as follows:

• Dbjobs

Cmd

Reports

Sql

echo *** VPCJSTINEY: Database Space *** > c:\dbjobs\reports\dbspace.txt

call c:\dbjobs\cmd\sqlcmdcen.cmd master dbspace dbspace

echo off

rem *** run sqlcmd: parms - database = %1, input = %2, output = %3 ***

sqlcmd -S localhost -E -w100 -d%1 -i \\fargo\c$\mssqlcen\sql\%2.sql >> c:\dbjobs\reports\%3.txt

The SQL script is not included as it is much longer.

NateT SSC-Addicted Points: 402 More actions · Answer 14

Isn't SQLCMD the executable this should be using? We use lots of on-demand batch files. Another subtopic may be security: do you use trusted or SQL authentication and what rights does the user have to objects in the system when executing the batch file.

Nate TeWinkel
UFS Inc. - DBA / Operations Analyst / Programmer