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



sqlsmokey
sqlsmokey
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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
James Wilkie
James Wilkie
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 Visits: 3889
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
Ritesh Modi-467689
Ritesh Modi-467689
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: 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
RobJoh
RobJoh
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1237 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.
Ritesh Modi-467689
Ritesh Modi-467689
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: 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.
Manie Verster
Manie Verster
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1308 Visits: 986
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

:-PManie 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)
krish270486
krish270486
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: 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...
Manie Verster
Manie Verster
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1308 Visits: 986
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' "

:-PManie 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)
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