Explicit passthrough problems with temp tables to SQL server database

[Billybob73]

Hi,

I'm facing a problem with explicit pass throughs to SQL server database.

The following code works fine :

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

%let dsn = <mydatabase>;

%let authdomain = <myauthdomain>;

libname tempdb odbc dsn=&dsn. authdomain=&authdomain. connection=shared;

data test;

date = '01JAN2019'd ;

datetime = '01JAN2019:00:00'dt ;

run;

data tempdb.'#test'n;

set test;

run;

data test;

set tempdb.'#test'n;

run;

However when I do the following :

proc sql;

connect to odbc as test (dsn=&dsn. authdomain=&authdomain. connection=shared);

execute (

create table #datumtijd

(

datum DATE,

datumtijd DATETIME,

)

) by test;

quit;

data test;

set tempdb.'#datumtijd'n;

run;

I get the message :

ERROR: File TEMPDB.'#datumtijd'n.DATA does not exist.

Why is that ?

I would like to insert the data from '#test'n into '#datumtijd'n. Like this :

proc sql;

connect to odbc as test (dsn=&dsn. authdomain=&authdomain. connection=shared);

execute (

INSERT INTO #datumtijd

SELECT * FROM #test)

by test ;

disconnect from odbc;

quit;

Thanks for your help !!

Regards

BB

[Reeza]

I suspect that when you disconnect your temp tables are lost due to starting a new session/connection. Do you have a schema you can write to that will persist between sessions?

[SASKiwi]

Check what is happening in SQL Server Management Studio in the tempdb database.

What happens if you continue to use your LIBNAME database connection like so:

proc sql;
  connect using tempdb;
<put your passthru query here>
quit;

[Billybob73]

The temp table does not dissappear after the data step. Test keeps existing.
In the passthrough i don t use a disconnect......

[SASKiwi]

@Billybob73  - I guess your latest thread supersedes this one. Note, I've posted a question regarding your SAS version there.