Schedulling Batch Jobs based on triggers from SQL server.

[SAPPER]

Hello,
I am looking for a way to schedule(Trigger) a SAS job based on the availability of a table on the SQL server.
The table on the SQL server is created every night and we donot want to wait until a predefined time everynight.
Can we trigger the SAS job right after the table is created on the SQL server?
we donot have a SAS CONNECT license.
The SQL server job that creates the table is scheduled using SQL-Scheduler and we schedule the SAS jobs using "Active Batch" (3rd party tool).

Is there a way where SAS can "look" to the SQL server and determine if the table is created "new"?(like checking every 5 minutes after a predefined time??)
Any suggestions/comments are appreciated.

Thanks
-SAPPER

[sfsdtegsdsdgdffhgfh]

you can create trigger file at the end of the job that creates sql table and look for the trigger file to kick off sas job.

[SAPPER]

Let me understand this in a correct way.
You are asking me to create a file on SQL server and ask SAS server to read this trigger file on SQL server?

Do you have any documentation/notes on how to do this?
-Thanks
SAPPER

[Patrick]

Hi

I think what sastech is telling you is that the scheduler used for the sql server jobs should create a trigger file (some empty file with a given name) and put this trigger file to a place where he scheduler used for the SAS jobs is looking for (some listener process).

These are things one could expect schedulers are able to do.

Alternatively: I assume your SAS job should read this new DB table - meaning you've got SAS\Access installed. The SAS process could of course query some sytem table and wait for a current table creation/modification date. If the condition is not satisfied then wait whatever minutes and try later. This second approach would only be a workaround. Things like scheduling and dependencies should be managed by schedulers.

HTH
Patrick

[SAPPER]

Hello Patrick,
Thanks for the update.
We have SAS Access at our site.
The problem is making the two Schedulers "Talk" with each other.
The SQL job is scheduled using the SQL scheduler and SAS jobs are kicked off using a 3rd part Scheduler (ActiveBatch).
I will try to go through the documentation of both the Schedulers.
Just wanted to see if anybody out there was doing this.
-Thanks
Sapper

[Patrick]

Hi

Yes, I've seen this in more than one project.

A process maintained with one scheduler creates staging files (text based data extracts) and then on the "SAS" side another scheduler is waiting for these files (listener) and kicks of SAS processes as soon as defined conditions are met.

i.e. Seen with TIDAL and LSF, Control-M and Autosys

The first scheduler creating marker files for which the second scheduler is waiting for is the way people often implement processes which use more than one scheduler.

HTH
Patrick

[Jehan]

Hi Patrick/ALL,

I am working on scheduling a SAS job in Autosys. My client is saying they need their tasks such access to stored procedures. Had anyone of you experienced it via Autosys?

[SASKiwi]

SAS can run SQL Server stored procedures regardless of how the SAS job is scheduled or run.

[Jehan]

This is what I want to understand.. how I can call that SAS job via Autosys. Is there any back-end instead of just seeing the GUI alone?

[sfsdtegsdsdgdffhgfh]

Patrick thanks for explanation. Sapper hope it works for you.