Execute SQL job through batch file

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

    --Divya

  • 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

  • works for me 🙂

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

  • 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

  • Might I also suggest a:

    time /t

    before and after execution -

    for a little more information.

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

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

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

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

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

  • 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

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

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

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

  • 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

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

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