how to schedule a sql job using batch file

Microsoft Sql server 2005 standard editi...
December 3, 2009 at 01:20:21
Specs: Windows XP
I want to write a script that will go to an ftp server search for a particular file every 15 minutes and if it finds it it should start a sql job immediately.
can any one help to write the script..
Thanks in advance!!!!!!!!!

See More: how to schedule a sql job using batch file

Report •

#1
December 3, 2009 at 04:33:46
"if it finds it it should start a sql job immediately."

Does the script get the file or simply start the sql if the file is there?


=====================================
Helping others achieve escape felicity

M2


Report •

#2
December 3, 2009 at 04:56:42
The checking of the SQL file should occur in the script, which is scheduled every whatever time. The "job" IS the script, the call to SQLPLUS then is conditional inside that script.

In Windows, should be easy :

if not exist C:\command.sql echo INFO: no action taken cause no C:\command.sql file found, this may be normal

if exist C:\command.sql sqlplus username/password @C:\command.sql


Report •

#3
December 3, 2009 at 10:44:17
to mechanix
'if it finds the file' means it does not have to get it just that if it exists or not.
so if you can help me in figuring this out it will be great!
Thanks

Report •

Related Solutions

#4
December 3, 2009 at 21:35:40
script file (ftpscript) for ftp (based on test.txt f/e):

open myconn.net
user me
passwd
binary
cd whatever
ls test.txt
quit

bat:
setlocal enabledelayedexpansion
set main=test.txt
set exec=sqlproc
ftp -n -s\workdir\ftpscript > ftpout
for /f "tokens=* delims=" %%a in (ftpout) do (
set cc=%%a
set cc=!cc:~0,-1!
if /i "!cc!" equ "!main!" call !exec!)

this is more cumbersome than downloading the file and testing "if exist", but if file is large, avoids bandwidth & time consumption since the file's not needed anyhow.
I would use "at" daemon (time/task scheduler) to set up the 15-minute intervals (assum. tsched is activated).
(not tested!)


Report •

#5
December 3, 2009 at 22:04:39
This checks for filename.ext on the ftp server.

==============================
@echo off & setLocal EnableDELAYedExpansion

> ss.ftp echo o mysite.com
>> ss.ftp echo user
>> ss.ftp echo pass
>> ss.ftp echo ls filename.ext remotels.txt
>> ss.ftp echo bye

ftp -s:ss.ftp

if exist remotels.txt echo do the SQL thing


=====================================
Helping others achieve escape felicity

M2


Report •

#6
January 22, 2010 at 07:36:24
Or you could use the scheduler of the server itself, to run a script that just checks for a file, and uses it if it is there ... no FTP involved.

Rajusingh, would the OS administrator not allow that, or is there another reason you want to initiate all from a client ?


Report •

Ask Question