topic Re: Explicit passthrough problems with temp tables to SQL server database in New SAS User

Explicit passthrough problems with temp tables to SQL server database

Billybob73 — Wed, 09 Oct 2019 14:31:35 GMT

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

Re: Explicit passthrough problems with temp tables to SQL server database

Reeza — Wed, 09 Oct 2019 15:08:45 GMT

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?

Re: Explicit passthrough problems with temp tables to SQL server database

SASKiwi — Wed, 09 Oct 2019 20:42:54 GMT

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;

Re: Explicit passthrough problems with temp tables to SQL server database

Billybob73 — Thu, 10 Oct 2019 05:19:03 GMT

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

Re: Explicit passthrough problems with temp tables to SQL server database

SASKiwi — Sat, 19 Oct 2019 22:43:44 GMT

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