Executing SQL stored procedure in SAS Grid - syntax?

Reply
New Contributor
Posts: 2

Executing SQL stored procedure in SAS Grid - syntax?

[ Edited ]

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.

Trusted Advisor
Posts: 1,301

Re: Executing SQL stored procedure in SAS Grid - syntax?

You cannot create a table out of a stored process and your syntax for a pass-through query is also incorrect.

Use the following for reference: http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001355234.htm
Super User
Posts: 3,233

Re: Executing SQL stored procedure in SAS Grid - syntax?

You have to use the SQL Passthru EXECUTE statement run an SQL stored procedure. Here is an example:

 

http://support.sas.com/techsup/notes/v8/7/450.html

 

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.

New Contributor
Posts: 2

Re: Executing SQL stored procedure in SAS Grid - syntax?

[ Edited ]

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.

 

proc sql;

   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;

quit;

 

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.

Ask a Question
Discussion stats
  • 3 replies
  • 1065 views
  • 1 like
  • 3 in conversation