Accessing SQL Server temporary tables across SAS procedures

[ScottBass]

Hi,

SAS 9.3 on Windows

The SAS documentation http://support.sas.com/documentation/cdl/en/acreldb/65247/HTML/default/viewer.htm#p1h2yn2en7gkb8n1mb... states:

When you want to use temporary tables that persist across SAS procedures and DATA steps with ODBC, you must use the CONNECTION=SHARED LIBNAME option. When you do this, the temporary table is available for processing until the libref is closed.

This is my libname statement:

LIBREF:  TMP
CONNECT: NOPROMPT="Driver={SQL Server Native Client 
10.0};Server=XXXXX,#####;Database=MyDatabase;Trusted_Connection=yes;"
OPTIONS: schema=tmp connection=shared

i.e. libname &libref odbc &connect &options;

This is my test code:

proc sql;
   connect using tmp;
   execute(
select *
into #temp
from master.sys.tables;
   ) by tmp;

   select * from connection to tmp (
      select * from tmp.#temp
   );
quit;

proc sql;
   connect using tmp;
   select * from connection to tmp (
      select * from tmp.#temp
   );
quit;

data work.foo;
   set tmp.'#temp'n;
run;

The first step works, the second and third ones give these errors:

ERROR: CLI describe error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 
       '#temp'. : [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be 
       prepared.

ERROR: File TMP.'#temp'n.DATA does not exist.

What bit am I not understanding from "...the temporary table is available for processing until the libref is closed."

Thanks...

[RahulG]

Try creating with double hash i.e. global temporary table 

Change #temp to ##temp.

##temp would be available till conncetion is active.

[LinusH]

Have you seen this note:

http://support.sas.com/kb/48/566.html

Apart from @RahulG suggestion, check the ODBC driver version.

[ScottBass]

Ok, I think I can answer my own question.

I found this link, which was helpful: http://www.sascommunity.org/planet/blog/category/temp-table/

So I then created this test code (don't worry about the macro - it simply allocates the SQL Server libref via ODBC):

%libname_sqlsvr(libref=TMP,database=MyDatabase,options=schema=dbo connection=shared bcp=yes)
 
data tmp.'##class'n;
   set sashelp.class;
run;
 
proc sql;
   connect using tmp;
   execute(
      select *
      into ##systables
      from tempdb.sys.tables
   ) by tmp;
 
   select *
   from connection to tmp(
      select * from ##systables
   );
quit;
 
data class;
   set tmp.'##class'n;
run;
 
data systables;
   set tmp.'##systables'n;
run;
 
proc sql ;
   connect using tmp;
   create table systables as
   select *
   from connection to tmp (
      select * from ##systables
   );
   disconnect from tmp;
quit;
 
proc sql ;
   connect using tmp;
   create table class as
   select *
   from connection to tmp (
      select * from ##class
   );
   disconnect from tmp;
quit;

Things worked as expected/desired for #class, but not for #systables.  Even though the first SQL query against ##systables showed that ##systables exists (for a brief moment in time), I could never access it in a subsequent step.

I also did a search and replace of ## with #, then re-ran the process.  What was interesting is that, even though I never could see #class in tempdb via SSMS (because it is a different connection), I could access it from my SAS program (because the libref is keeping the connection open).  This is useful, as my temporary files could contain data that, while not super-sensitive, I'd prefer others cannot see via SSMS.  However, in this scenario, the final proc sql step failed; I can access #class via the libref, but not via explicit pass-through.

I tested with both connection=shared and connection=global with the same results.

Then the light bulb lit:  explicit pass-through isn't going through the SAS library, so any temporary tables created via explict pass-through won't be available after the connection (proc sql step) ends.  This also means that any library options, such as connection=shared, don't apply.  

I had SSMS open, viewing tempdb.Temporary Tables, refreshing after each SAS step.  I could see ##class, but never could see either ##systables, #systables, or #class.

Let me know if this analysis is flawed.  Sorry for the false alarm.  I hope this may help someone who finds this via search someday.

[Patrick]

Hi Scott,

I guess your analysis is principally right. Question is now: Could the libname and the pass-through connection use the same connection so that you'll always get access to the temporary data.

Based on the example in the following link that should at least be possible for Oracle under SAS9.4

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a003113602.htm 

Cheers,

Patrick

[ScottBass]

Thanks @Patrickfor your reply.

I couldn't work out how to share/persist a connection on SQL Server when using explicit pass-through, which would be a requirement for my application.  I learned a bit more about SQL Server temporary tables and their support in SAS this thread, but I'm not quite there yet.

Sample code:

proc sql;
   connect using tmp;
   execute (
      select * into ##systables from tempdb.sys.tables;
   ) by tmp;

   select * from connection to tmp (select * from ##systables);
quit;

proc sql;
   connect using tmp;
   select * from connection to tmp (select * from ##systables);
quit;

If anyone knows how to get the 2nd query to work, please let me know.  AFAIK, I need a way to persist the first connection, and have the 2nd query re-use that same connection.

Bonus points if you can get this to work with #systables instead of ##systables.

Oh yeah...SAS 9.3 on Windows Server.