topic Re: How to establish connection and read from SQL Server stored procedure. in SAS Programming

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

Stalk — Mon, 08 Feb 2021 18:50:22 GMT

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

Stored Procedure: SSSP_RES_V12_Load

Thank you

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

SASKiwi — Mon, 08 Feb 2021 00:59:35 GMT

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.

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

Stalk — Mon, 08 Feb 2021 18:55:30 GMT

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;