topic Re: temporary tables SQL Server not visible in SAS Data Management

temporary tables SQL Server not visible

Billybob73 — Fri, 18 Oct 2019 11:29:41 GMT

I have 4 questions. regarding this :

Question 1 :

I'm creating temporary tables in an SQL server database.

This is the way I do it :

LIBNAME sqldb ODBC DATASRC=<DB> SCHEMA=dbo authdomain=<AUTHDOMAIN>

PRESERVE_TAB_NAMES=YES CONNECTION=SHARED INSERTBUFF=1000 READBUFF=2500;

PROC SQL ;

CREATE TABLE sqldb.'##TEST'n(INSERTBUFF=1000) AS

select distinct <VARIABLE>

from SQLSERVERLIB.SQLSVRTABLE;

quit;

This works ! However when I check SQL Server Management Studio, I do not see #TEST in the tempdb

( I check for Databases\System Databases\tempdb\Temporary Tables folder )

What do I need to do differently to make this temp table appear in this folder ?

Question 2

I am getting the following error message :

ERROR: CLI execute error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'select'.

when I do this :

proc sql;

connect to odbc as test (dsn=<DB> authdomain=<AD> connection=shared);

execute(

create table ##T1 as

select * from ##TEST

) by test;

quit;

Why can't I do this ?

Question 3

LIBNAME sqldb ODBC DATASRC=<DB> SCHEMA=dbo authdomain=<AUTHDOMAIN>

PRESERVE_TAB_NAMES=YES CONNECTION=SHARED INSERTBUFF=1000 READBUFF=2500;

PROC SQL;

connect to odbc as test (dsn=<DB> authdomain=<AD> connection=shared);

EXECUTE (

create table ##T11

(bank_code VARCHAR(30)

)

) BY A;

quit;

data T11;

set sqldb.'##T11'n;

run;

This EXECUTE statement does work ! However why can't I get it from library SQLDB just like the sql in question 1 ?

Question 4

So far I've been using this lib statement to connect to the tempdb on MS SQL server :

LIBNAME sqldb ODBC DATASRC=<DB> SCHEMA=dbo authdomain=<AUTHDOMAIN>

PRESERVE_TAB_NAMES=YES CONNECTION=SHARED INSERTBUFF=1000 READBUFF=2500;

As you can see I'm referring to a specific database whereas the temp tables should be available across ALL databases.

Is there a different statement which does this ?

In Teradata I always used :

libname volalib teradata authdomain=<authdomain> mode=teradata server=<"server"> connection=global dbmstemp=yes;

Is there a similar MS SQL Statement ?

Thanks for you help !!

Regards

Re: temporary tables SQL Server not visible

Patrick — Sun, 20 Oct 2019 22:06:28 GMT

Just based on what's documented:

- Your connection needs to be GLOBAL - link

- The docu for INSERTBUFF also states: Microsoft SQL Server, Greenplum Details: You must specify INSERT_SQL=YES in order to use this option. - link

For below: Because you don't use a GLOBAL connection ##TEST does eventually no more exist.

create table ##T1 as
select * from ##TEST

Your Teradata example uses:

- connection=global but all your SQL Server examples use connection=shared

Re: temporary tables SQL Server not visible

Billybob73 — Sat, 19 Oct 2019 15:55:30 GMT

According to this link it should NOT be global, but shared :

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

Regards

William

Re: temporary tables SQL Server not visible

SASKiwi — Sat, 19 Oct 2019 21:56:41 GMT

@Billybob73 - Your link is to documentation for SAS 9.2 which is an incredibly old release. Is this really the SAS version you are using?

The current version is SAS 9.4 and @Patrick provided you the correct links to that. In SAS 9.4 CONNECTION = GLOBAL and DBMSTEMP = YES are the right options to use.

Re: temporary tables SQL Server not visible

Billybob73 — Sun, 20 Oct 2019 09:01:53 GMT

Hi,

Ok but the neither global nor shared does work unfortunately. My problem hasn't got to do with this.

What I'm looking for is a libname statement to SQL server allowing me to use temp tables across all databases that are available in the sql server instance.

The only thing that i've got working right now is temp tables for a specific database. As soon as I use another database in the explicit passthrough it says the temp table is not available.

Thanks

Regards

William

Re: temporary tables SQL Server not visible

Billybob73 — Sun, 20 Oct 2019 09:30:37 GMT

Another thing about this. I've read somewhere a connection to the TEMPDB should look something like this :

libname TMPLIB ODBC NOPROMPT='DRIVER=SQL Server; SERVER=<SERVERNAME>; DATABASE=TEMPDB; TRUSTED_CONNECTION = yes" schema=DBO CONNECTION=GLOBAL DBMSTEMP=YES;

My suspection is that when I get this working I can use temptables across ALL databases in an SQL server instance.

However right now I'm using :

libname TMPLIB ODBC authdomain=<authdomain> dsn=<databasename> schema=dbo PRESERVER_TAB_NAMES=YES CONNECTION=GLOBAL INSERT_SQL=YES INSERTBUFF=1000 READBUFF=2500 DBSLICEPARM=(THREADED_APPS, 4);

Please not that using DBMSTEMP=YES in the last libname statement causes joins with databases not to work so I have to omit this statement.

Thing is that the last libname statement works and I can join within explicit passthrough statements with database tables, views. But only for the database specified in the libname statement.

Maybe this context helps to solve this matter as I keep struggling with this I cannot find any documentation on this.

Rgds

Re: temporary tables SQL Server not visible

Patrick — Sun, 20 Oct 2019 22:17:05 GMT

If using DBMSTEMP=YES as libname option then ALL tables created under this libref are temporary. Or use a libref without DBMSTEMP and the ## notation. That should work as well.

For joining with a temporary table from the docu here:

To join a temporary table and a permanent table, you need a libref for each table and these librefs must successfully share a global connection.