Executing SAS Program from SQL Server Management Studio

[nupedoc79]

I know how to access SQL server via SAS Access / Pass Through Connection to read SQL server database tables. I would like to know, is there a way to execute a SAS program via a SQL Server Management Studio script or store procedure?

I've written a SAS Logger / Adapter program which executes several SAS programs then reporting if the programs contained errors. 

My Vice-President however would like SQL Server management Studio to execute the SAS Logger / Adapter program. I'm looking for SAS code examples or documentation. 

Thanks, 

[Quentin]

Where is your SAS code executing (on a server?  on a PC?)

If your SSMS job is running somewhere that can see your SAS environment, it's possible you could do it with the SQL server equivalent of the SAS X command to execute the .sas file as a batch job.

Looks like xp_cmdshell might be the equivalent of X:

USE master;  
  
EXEC xp_cmdshell 'copy c:\SQLbcks\AdvWorks.bck  
    \\server2\backups\SQLbcks', NO_OUTPUT;  
GO  

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-tran...

But it seems a bit hokey to have SSMS starting a SAS job.  It might be better to look into scheduling options for the SAS job.  Or even scheduling options for running the SQL job and then running the SAS job.

[nupedoc79]

SAS program is running on a server. Your last two statements are on point and are what I advised my VP we should do.

Thanks for you

[SASKiwi]

SSMS is a tool for interacting with SQL Server databases. Why would you use it for running SAS jobs? It really is the wrong tool for the job.

I see no advantages in attempting this, only complications. I personally use SSMS for developing SQL that can then be dropped into SAS SQL Passthrough queries, but I do that part in SAS Enterprise Guide.

You still have to develop your SAS jobs accessing SQL server in a SAS IDE like SAS Enterprise Guide or SAS Studio so why not also run them from there? If you need to run SAS batch jobs, then SAS Management Console is the best tool for the job IMHO.