Can a SQL Server Stored Procedure Call a SAS Program?

Reply
Contributor
Posts: 27

Can a SQL Server Stored Procedure Call a SAS Program?


Hi, All!

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!

Super User
Posts: 5,424

Re: Can a SQL Server Stored Procedure Call a SAS Program?

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.

Data never sleeps
Contributor
Posts: 27

Re: Can a SQL Server Stored Procedure Call a SAS Program?

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:

Super Contributor
Posts: 418

Re: Can a SQL Server Stored Procedure Call a SAS Program?

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

PROC Star
Posts: 1,167

Re: Can a SQL Server Stored Procedure Call a SAS Program?

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.

http://technet.microsoft.com/en-us/library/hh213688.aspx

Hope this helps,

  Tom

Contributor
Posts: 27

Re: Can a SQL Server Stored Procedure Call a SAS Program?

Thanks, Tom!  I will forward the link to my SQL partner and we'll see if we can get it to work.

Respected Advisor
Posts: 4,173

Re: Can a SQL Server Stored Procedure Call a SAS Program?

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.

Contributor
Posts: 27

Re: Can a SQL Server Stored Procedure Call a SAS Program?

Thanks, Patrick!  We discussed this idea earlier today.  I think either this suggestion or Tom's will be "correct."

Ask a Question
Discussion stats
  • 7 replies
  • 1171 views
  • 6 likes
  • 5 in conversation