How to establish connection and read from SQL Server stored procedure.

[Stalk]

How to establish SQL server connection string in SAS  using the following credentials?

Stored Procedure:       SSSP_RES_V12_Load

Thank you

[SASKiwi]

This Oracle example can be easily modified for SQL Server: https://support.sas.com/kb/18/350.html

Which SAS/ACCESS product are you using? SAS/ACCESS to SQL Server or SAS/ACCESS to ODBC?

Please note you can't read a result set while running a stored procedure. The workaround is to have the stored procedure create a temporary table then read that in a following PASSTHRU query with a SELECT statement.

[Stalk · Accepted solution]

PROC SQL;

Connection string to exec stored procedure that works..

CONNECT TO ODBC(NOPROMPT="DRIVER={ODBC Driver 13 for SQL Server}; SERVER=&svr_nm.; database=&DB_nm.; Trusted_Connection=yes;");

CREATE TABLE WORK.Daily_copy AS

SELECT *

FROM CONNECTION TO ODBC (exec &sp.);

DISCONNECT FROM ODBC;

quit;