Execute SQL script using Windows Task Scheduler

Say, you have a SQL script ready, and you want that to run automatically without you kick starting it every single time. You can do this via ‘adding the script to Windows Task Scheduler’, and configuring it.

Example script (saved as test.sql):

set echo on
set time on
set timing on

spool C:\some_folder\logfile\test.log;

drop table test_table;

create table test_table
(name varchar2(30), age int);

insert into test_table (name, age)
select ‘senthamizh selvan’, 20 from dual
union
select ‘Kanmani Anbodu’, 35 from dual;

commit;

spool off
exit;

Note: Spool commands where I’m writing the logs has the ‘Full Path’ of the log file.

We are going to use ‘sqlplus’ – a command line utility for Oracle Database / Query and Windows Task Scheduler to automate this SQL.

You can find ‘Task Scheduler’ application in the ‘Administrative Tools’ section (in Control Panels) for Windows server.

Open ‘Task Scheduler’ and Choose’Create Basic Task’ under Action.

Give a Name to your task, and Click ‘Next’

 

Choose a schedule -daily, weekly, etc ….

And Click Next

I chose ‘Daily’ and gave the ‘Time’ – Click Next once done

Click Next

Enter ‘sqlplus’ and in the arguments add “username/password@databse_name @C:\somefolder\….\test.sql”

Make sure you enter the full path of the location to the file in the arguments.

Click Finish

And your job is ready to run.

Click on ‘Task Scheduler Library’ to confirm if your Job is there in the scheduler

You can ‘right-click’ on the job to ‘Run’ manually once to verigy if its executing to completion or if you want to change any schedule/command/arguments – click on properties, and you will be able to change and Edit as needed.

Also, you can make the job to run in the server, even when you are not logged into the server, by choosing that option from the ‘General’ tab. But to do so, you should be an administrator or have the ‘batch’ access privilege.

See if you can make you ‘batch access’ by doing this

This policy is accessible by opening the Control Panel , Administrative Tools , and then Local Security Policy . In the Local Security Policy window, click Local Policy , User Rights Assignment , and then Logon as batch job

or you will be need to check with your system administrator

 

Also, you can check your log file to confirm you are seeing what you are expecting to !!