Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Execute SQL job through batch file Expand / Collapse
Author
Message
Posted Friday, June 5, 2009 7:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 3, 2013 12:27 PM
Points: 21, Visits: 42
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.
Post #729696
Posted Friday, June 5, 2009 7:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 8:19 AM
Points: 327, Visits: 123
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.
Post #729701
Posted Friday, June 5, 2009 8:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 9:39 AM
Points: 12, Visits: 199
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.
Post #729739
Posted Friday, June 5, 2009 8:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 2, 2012 7:05 AM
Points: 75, 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.



Post #729744
Posted Friday, June 5, 2009 8:13 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 9:15 AM
Points: 10, Visits: 62
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
Post #729746
Posted Friday, June 5, 2009 8:27 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #729765
Posted Friday, June 5, 2009 8:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 4:53 PM
Points: 266, Visits: 2,589
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.
Post #729807
Posted Friday, June 5, 2009 9:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:59 PM
Points: 2, Visits: 18
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



Post #729846
Posted Friday, June 5, 2009 9:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:03 PM
Points: 12, Visits: 144
hello friend,
Why don't post the vbscript if you have any with you.
Appreciate your idea and help



Post #729854
Posted Friday, June 5, 2009 11:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 4:53 PM
Points: 266, Visits: 2,589
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.
Post #729932
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse