09-16-2015 10:10 AM - edited 09-16-2015 10:20 AM
What is the syntax for executing a SQL stored procedure on the SAS Grid?
The following code returns the following error on the grid:
ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source
name not found and no default driver specified
libname testdat odbc complete="driver=SQL Server; Server=YYY-111\SERV1; database=BIGDAT " access=readonly connection=sharedread schema=SAS; run; proc sql noprint; create table tabout as select onevar, twovar from connection to odbc (exec SAS.usp_GetData) disconnect from odbc; quit; run;
On the other hand, this works to run a SQL DB query on the grid:
libname testdat odbc complete="driver=SQL Server; Server=YYY-111\SERV1; database=BIGDAT " access=readonly connection=sharedread schema=SAS; run; proc sql noprint; create table tabout as select onevar, twovar from SAS.ThisData quit; run;
This indicates that the libname statement excecutes successfully (log shows "... successfully assigned ...") but I need to edit the PROC SQL code. The stored procedure exists and the first segment of code executes the stored procedure outside of the grid after adjusting the LIBNAME statement to using "datasrc=dummysrc" instead of the "complete = ..." option shown. Thank you.
09-16-2015 04:21 PM
09-16-2015 04:53 PM
You have to use the SQL Passthru EXECUTE statement run an SQL stored procedure. Here is an example:
You can return an output parameter as the example shows, but not a result set as Fried Egg says. The workaround is to create a temporary view with the stored procedure then select from the view in a SELECT statement following the EXECUTE.
09-22-2015 09:34 AM - edited 09-22-2015 09:41 AM
Thank you for your responses. I hope to review them soon to see how they relate to the following solution that worked for us. This solution was provided by SAS Techincal Support employee, Brian Newborn. Thanks, Brian!
Replace "Trusted_Connection=Yes" with "UID=USERNAME;PWD=USERPASS" if you're not using Windows Authentication.
connect to odbc (noprompt="driver=SQL Server;Server=YYY-111\SERV1;database=BIGDAT;Trusted_Connection=Yes");
create table tabout as select onevar, twovar from connection to odbc(exec SAS.usp_GetData);
disconnect from odbc;
He added the following:
Using Explicit Pass-through would not be unique to just Grid. You would need it to run the stored procedure anywhere.
Explicit Pass-Through allows you to pass SQL statements (as you write them) directly to the SQL Server database. Using a libname still passes SQL statements, but it is considered Implicit Pass-Through so SAS will do its best to construct and pass queries, joins, etc to the database for execution. It just would not know what to do with a stored procedure.