Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Execute SQL job through batch file


Execute SQL job through batch file

Author
Message
jpratt-797544
jpratt-797544
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 79
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
Martin Vrieze
Martin Vrieze
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 125
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
djjwu
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 226
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
JStiney
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 446
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
NateT
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 67
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45329 Visits: 39936
The use of cmd files to execute sql is a powerful tool that is underutilized...


I absolutely agree with that. And, old or not, the idea is "there". A bit more detail could certainly have been added to the article but it provides the basis of a very underutilized technique.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
JJ B
JJ B
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 2846
Example of where I could probably use this (or a similar) technique: We use SQL Server 2005. I have several SSIS packages that fail at random intervals and at random steps/tasks. The failure notice is that the poor thing just failed to acquire a connection to our own database right there in the same server as SSIS. The scheduled job that fails one night is likely to run fine the next next night with no changes. Once, however, I got the same error an entire week before the job started working again. (We re-boot our server every week.) Sometimes I get the same error when I manually run the job, and and then a minute later it works.

Fine. We can usually live with this buggy behavior. However, this problem unfortunately happened once when I was on vacation. It would have been good if I had had a solution such as the one shown in this article so that my users could have manually run the job themselves when they needed it.

I understand that security issues have to be worked out, but giving the users the ability to run a job on demand in a production environment could have some very practical purposes in my agency.
D Randall Pitkin
D Randall Pitkin
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 23
JJ B (6/5/2009)
Example of where I could probably use this (or a similar) technique: We use SQL Server 2005. I have several SSIS packages that fail at random intervals and at random steps/tasks. The failure notice is that the poor thing just failed to acquire a connection to our own database right there in the same server as SSIS. The scheduled job that fails one night is likely to run fine the next next night with no changes. Once, however, I got the same error an entire week before the job started working again. (We re-boot our server every week.) Sometimes I get the same error when I manually run the job, and and then a minute later it works.

Fine. We can usually live with this buggy behavior. However, this problem unfortunately happened once when I was on vacation. It would have been good if I had had a solution such as the one shown in this article so that my users could have manually run the job themselves when they needed it.

I understand that security issues have to be worked out, but giving the users the ability to run a job on demand in a production environment could have some very practical purposes in my agency.


Why does anyone live with buggy behavior. Solve the Issue. Get Profiler running and find the Table lock or constraint that is throwing the error. It's not Buggy Behavior it's bad development and lack luster administration.

On Topic:
sp_start_job requires permission to execute higher level processes.

The best way to get a simple user to be able to fire a Job on Demand is:
1) Build a table in a public database [Jobs] (Job_name,Server,Database,RequestUser,Requestdate,StartDate,CompleteDate,JobStatus)
Then a user can insert a record when they want the job to execute. via any Client(WEB,Winform,access,SSMS,etc.....)
Create a Second Job call USer Jobs running under sql agent as a domain sysadmin
with a schedule to run every 5 minutes (or whatever meets your needs)

Cursor Select Job_name from Jobs where StartDate is null
exec msdb..sp_start_job @Job_name=@Job_name

The table can be as elaborate as you need
The user jobs Job can update the status and manage Jobs with any business rules.

I am sorry if I have offended, this is my second post here.
I won't be back
vvkp
vvkp
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 164
hello friend,
Why don't post the vbscript if you have any with you.
Appreciate your idea and help



JJ B
JJ B
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 2846
Why does anyone live with buggy behavior. Solve the Issue.

Not everyone has the luxury of solving every problem. If you are overwhelmed with tasks, you have to prioritize. If this problem affected us in a more serious way, I would definitely work on it. But I have much bigger priorities. So, having a quick batch file I can throw at users for the once a year I actually take a real vacation on the off chance that they will need it (it's only ever happened once in years that a vacation and failure coincided), then that is the better use of time at this one person show.

FYI: While you could be right that there is some kind of bad design that requires a fix on my part, I think you are wrong for this situation. The very nature of this problem as originally described (different SSIS, a first generation piece of software, packages doing very different things, random failure times and frequencies, random points of failure, and I'll add: relatively small databases on an underutilized server with plenty of resources and no block reports from my monitoring system) screams a bug in SSIS/SS05 or some other kind of problem with our server/network as opposed to failures in all my databases and queries creating blocking/locking problems.

Doesn't really matter either way since I don't have the time to address it. But my instincts on these things have proven rather accurate in the past. If I'm right, not only do I not have time to deal with this one task of the hundreds I'm juggling, it would be a waste of time. I'm pushing to upgrade to SS08. I've heard lots of reports that SS08 is more stable than SS05. My problem may disappear. If so, that's one more reason for me not to spend time on it right now.

Prioritize. It's a good thing.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search