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 11:44 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
** Correction - osql has not been deprecated.
It is still working in SQL 2008, but according to BOL (This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use the feature. Use sqlcmd instead.)

Also osql and sqlcmd allow either trusted connections or embedded name and password.




Post #729943
Posted Friday, June 5, 2009 1:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 8:34 AM
Points: 7, Visits: 142
Lots of good banter. You might also want to see the results of the command. I like the architecture of Job.cmd calling Job.BAT that routes the output to a file.

Contents of job.cmd look like this:

c:\job.bat > c:\job.log 2>&1

The osql line could also use the -b switch will will abort on error.

Security concerns can be addressed with groups on the box. The type of groups and the permissions associated will depend on the purpose of the batch script.

Good info Divya
Post #730005
Posted Friday, June 5, 2009 4:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 23, 2012 9:46 AM
Points: 3, Visits: 28
Comming from a UNIX background, I find that OSQL is very helpfull in updating a remote MS server and monitoring activity to an application that notifies and downloads files to 7000+ clients. I installed UWIN, an UNIX environment on my XP laptop and put SQL statements into korn shells. The output can be placed into an ASCII file and manipulated through extensive UNIX tools.
Post #730083
Posted Friday, June 5, 2009 4:50 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:45 AM
Points: 2,826, Visits: 3,866
What about creating a set of stored procedures for this?

1. User executes "dbo.spuQueueJob @JobName" via OSQL to "manually schedule" the existing job by adding the job name to a queue table.
2. A Scheduled Task runs every 5 minutes (with admin privs) to see if a job was queued via the above proc. If yes, uses sp_start_job for the jobs found in the queue and deletes them from the queue table afterwards.

This way, you do not need to grant the user high privileges. Only the right to execute this dbo.spuQueueJob procedure.


Best Regards,
Chris Büttner
Post #730096
Posted Friday, June 5, 2009 6:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 8, 2009 6:41 PM
Points: 2, Visits: 7
The Script is very help full but it only work if the particular Backup is already configured on a particular server. it will be very helprull to run some other storeprocedure. No one is very familior how to write backup.

Ritesh
Post #730116
Posted Friday, June 5, 2009 8:22 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 1, 2013 7:53 AM
Points: 1,219, Visits: 73
I would not use the pause statement, if you do, the batch script will wait indefinitely for the user to depress a key.
Post #730125
Posted Friday, June 5, 2009 10:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 8, 2009 6:41 PM
Points: 2, Visits: 7
Yes you are right. if we are making backup from batch file then No pause in the script. by removing we can scheduled the backup automatically.
Post #730144
Posted Saturday, June 6, 2009 1:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:21 AM
Points: 1,205, Visits: 923
D Randall Pitkin (6/5/2009)
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

Why don't you post us a vbscript here so we can see how it works.
This script works fine for me because I can schedule it in the Windoews' scheduler. You see, my one client has a relatively small business and at this point cannot afford a SQL Server 2008 full version so they use the express version. Therefore I cannot schedule backups and other jobs in SQL Server so I'll use this script to run a scheduled job in Windows.
Thanks for this cscript and no matter what anyone else say if they don't think it is good the they must post something better. Amen


Manie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #730169
Posted Saturday, June 6, 2009 6:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 1, 2010 10:59 PM
Points: 2, Visits: 15
Hey..
can u able to explain the code in little bit brief once...

because It is not working in my system.. while try to connect the remote server and as well as local system also...

check below

ECHO Executing job
ECHO.
pause
osql -S "ws-1" -E -Q"exec msdb.dbo.sp_start_job 'BACKUPTEST' "

ECHO Job execution completed
pause
CLS
EXIT

Note : ws-1 is my server name when i open sql2005 it displays (ws-1\sqlexpress).. which one is correct...
Post #730215
Posted Saturday, June 6, 2009 10:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:21 AM
Points: 1,205, Visits: 923
krish270486 (6/6/2009)
Hey..
can u able to explain the code in little bit brief once...

because It is not working in my system.. while try to connect the remote server and as well as local system also...

check below

ECHO Executing job
ECHO.
pause
osql -S "ws-1" -E -Q"exec msdb.dbo.sp_start_job 'BACKUPTEST' "

ECHO Job execution completed
pause
CLS
EXIT

Note : ws-1 is my server name when i open sql2005 it displays (ws-1\sqlexpress).. which one is correct...

No, you did it wrong. You must not enter the server name but the instance name for example servername\instancename.
osql -S "Put instance name here e.g. serverName\instance" -E -Q"exec msdb.dbo.sp_start_job 'BACKUPTEST' "


Manie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #730278
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse