SAS/ACCESS^® 9.1.3 Interface to OLE DB can call MS SQL Server stored procedures with temporary tables.

When you call the same stored procedure in SAS^® 9.2, the following error is returned:

ERROR: Prepare error: ICommand::Execute failed. : Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
SQL statement: spTempTablTest #DatesMinp.

SAS 9.2 forces the use of the dynamic cursor with a global connection, but the cursor does not work.

Currently, there is no a way to override this behavior.

Operating System and Release Information

%let ConnType=global; 


libname dblib
    oledb Provider=SQLOLEDB     
    PROVIDER_STRING="Server=SQL_Server;Database=data;Trusted_Connection=yes"
    connection=&ConnType 
    ;

data dblib.'#TableName'n;
   format date datetime20.;
   date = "31JAN2009 00:00"DT ;  
run;



proc sql; 
    Connect to oledb AS dblib ( 
        PROVIDER=SQLOLEDB OLEDB_SERVICES=NO 
        PROVIDER_STRING="SQL_Server;Database=data;Trusted_Connection=yes"
                connection=&ConnType 
     ); 

    /* using temp table works in selects  */

    create table A as Select * from   connection to dblib ( select * from #TableName);

    /* now call sql server stored proc to select from temp table */
    
    create table B as Select * from   connection to dblib ( spTempTablTest '#TableName' );
        execute ( drop table #TableName ) by dblib;

quit;

libname dblib Clear;

A fix for this issue for SAS/ACCESS Interface to OLE DB 9.21 is available at: