10-17-2013 02:47 PM
We have a series of stored procedures on server A that build some tables. When those sp's finish, we'd like to kick off a batch SAS program on remote desktop B to do the QA reports. I've found a lot of ways to call sp's from SAS, but not vice-versa. Thanks!
10-17-2013 02:55 PM
Sounds like an odd set up...
For me it would sound more naturally to do all this from a single point - SAS. But you need the SAS/ACCESS to OLEDB/ODBC licence for that.
Do I understand you right that you wish to create reports in a SAS background process on a remote PC, and store the report as like HTML/PDF...?
I don't know to handle the remote desktop thing from SQL Server, but to start SAS programs, you just use call to SAS as DOS call, and use the name of the SAS program as a parameter. So if you in some way can launch a program on a remote PC from SQL Server, this should be feasible.
10-17-2013 02:59 PM
Thanks, Linus. Yes, everywhere else I've been, we would do this from SAS. At my current place, though, they built everything using stored procedures years ago and just hired us SAS folk in the last year or so. It is what it is. :smileysilly:
10-17-2013 03:08 PM
why don't you just run the stored procedures on the Sql server as is through a SAS odbc connection?
that way you're running the stored procs in sql, but returning the results to sas, which is exactly what you have asked for... I think i'm missing something in your question
10-17-2013 03:23 PM
Microsoft's PowerShell is good for stuff like this.
I did a quick search, and found the following, which seems to indicate that you can execute a PowerShell script from inside SQL Server. Running your SAS job from PowerShell should be easy.
Hope this helps,
10-17-2013 05:27 PM
You write that your machine B is a "remote desktop" and not a Server. If that's the case then it could be difficult to remotely start a process on it from Server A - and then also have this process execute with the right credentials.
An alternative way to go:
- Once process on machine A finishes a token file gets written to a network drive accessible for both Machine A and Machine B
- On machine B have a little script running checking for existence of this token file (eg. running every 15 minutes using Windows Task Scheduler or similar).
- If the token file exists then batch submit SAS reporting process on machine B and once finished move token file to an archive directory.
Implementing this way would also have the advantage that if machine B is down the process on machine A can still finish and once Machine B gets "online" again the scheduled script will pick up the token file and execute the reporting SAS program.