May 25, 2009 at 6:17 am
Comments posted to this topic are about the item Execute SQL job through batch file
--Divya
June 5, 2009 at 12:25 am
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
June 5, 2009 at 2:45 am
works for me 🙂
I also used psexec.exe to allow a user to execute the batch file on a remote server.
June 5, 2009 at 5:04 am
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
June 5, 2009 at 5:20 am
Might I also suggest a:
time /t
before and after execution -
for a little more information.
June 5, 2009 at 5:53 am
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..
June 5, 2009 at 6:19 am
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.
June 5, 2009 at 6:46 am
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.
June 5, 2009 at 7:19 am
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.
June 5, 2009 at 7:31 am
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).
June 5, 2009 at 7:41 am
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
June 5, 2009 at 7:43 am
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.
June 5, 2009 at 8:06 am
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.
June 5, 2009 at 8:12 am
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.
June 5, 2009 at 8:13 am
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