Temporary tables on sql server

[AJ_Brien]

Hello,

I was able to create a temporary table on sql server and move my work dataset there. However, when I join that with another sql server table, I get this error:

ERROR: CLI describe error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid object name
'sqltest1.Table2'. : [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The metadata could not be
determined because every code path results in an error; see previous errors for some of these.

I don't understand the purpose of '##' but I read it on a couple of SAS documentation websites so I'm using it.

Appreciate the help!

Please find code below:

LIBNAME sqltest SQLSVR User="%sysget(USER)" Password="xx" DSN="xx" schema="xx" connection=shared;

proc sql;
     drop table sqltest."##Table1"n;
quit;

proc sql;
     create table sqltest."##Table1"n as 
           select   * from work.hb;
quit;

libname sqltest1 SQLSVR User="%sysget(USER)" Password="xx" DSN="xx" schema="xx" connection=shared;

 
proc sql;
   connect to SQLSVR as FA(User="%sysget(USER)" Password="xx" DSN="xx");
CREATE TABLE abc AS 
SELECT  * FROM connection to fa

(select a1.*, 
a.var1, 
a.var2

From ##Table1 a1
inner join sqltest1.Table2 a
on a1.ID=a.ID

);
DISCONNECT FROM FA;
QUIT;

Logs:

GOPTIONS ACCESSIBLE;
LIBNAME sqltest SQLSVR User="%sysget(USER)" Password="xx " DSN="xx" schema="xx" connection=shared;
NOTE: Libref SQLTEST was successfully assigned as follows: 
      Engine:        SQLSVR 
      Physical Name: xx
proc sql;
drop table sqltest."##Table1"n;
WARNING: File SQLTEST.'##Table1'n.DATA does not exist.
WARNING: Table SQLTEST.'##Table1'n has not been dropped.
quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.07 seconds
      user cpu time       0.05 seconds
      system cpu time     0.00 seconds
      memory              85.46k
      OS Memory           25764.00k
      Timestamp           10/02/2019 11:16:06 AM
      Step Count                        14  Switch Count  0
      Page Faults                       0
      Page Reclaims                     36
      Page Swaps                        0
2                                                          The SAS System                           09:55 Wednesday, October 2, 2019

      Voluntary Context Switches        47
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           8
      

proc sql;
create table sqltest."##Table1"n as
select   * from work.hb;
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: Table SQLTEST.'##Table1'n created, with 19 rows and 3 columns.
quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.04 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              5338.21k
      OS Memory           31144.00k
      Timestamp           10/02/2019 11:16:06 AM
      Step Count                        15  Switch Count  1
      Page Faults                       0
      Page Reclaims                     58
      Page Swaps                        0
      Voluntary Context Switches        37
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           8
      

libname sqltest1 SQLSVR User="%sysget(USER)" Password="xx" DSN="Xx" schema="xx"
! connection=shared;
NOTE: Libref SQLTEST1 was successfully assigned as follows: 
      Engine:        SQLSVR 
      Physical Name: Xx
proc sql;
connect to SQLSVR as FA(User="%sysget(USER)" Password="xx." DSN="Xx");
CREATE TABLE abc AS
SELECT  * FROM connection to fa
 (select a1.*,
a.var1,
   a.var2

From ##Table1 a1
inner join sqltest1.Table2 a
on a1.ID=a.ID
);
ERROR: CLI describe error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid object name 
       'sqltest1.Table2'. : [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The metadata could not be 
       determined because every code path results in an error; see previous errors for some of these.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
DISCONNECT FROM FA;
NOTE: Statement not executed due to NOEXEC option.
  QUIT;

I tried using From sqltest."##Table1"n a1 instead of From ##Table1 a1, that gave me the same log but with the following error:

ERROR: CLI describe error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near
'##Table1'. : [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The batch could not be
analyzed because of compile errors.

[Kurt_Bremser]

Remove the second libname statement. It could mean that the connection is dropped, causing the temporary table to be deleted.

[AJ_Brien]

Libnames sqltest and sqltest1 have different schemas.
I tried doing this too after deleting the 2nd libname:
From ##Table1 a1
inner join schema2.Table2 a
on a1.ID=a.ID

This gives me this error:
ERROR: CLI describe error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid column name 'ID'. :
[SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The batch could not be analyzed because of compile errors.

[Tom]

You didn't include the same options on your CONNECT statement as you did on your LIBNAME statement(s).  Importantly you didn't include 

connection=shared

You also didn't test whether you could see the temporary table using the second libref.  Instead you appear to have tried to use the SAS libref in your SQL Server code.

Try something like:

LIBNAME sqltest SQLSVR User="%sysget(USER)" Password="xx" DSN="xx" schema="xx" connection=shared;
proc sql;
create table sqltest."##Table1"n as 
  select * from work.hb
;
create table using_libref as select * from sqltest."##Table1"n;
connect using sqltest;
CREATE TABLE using_passthru AS 
SELECT  * FROM connection to sqltest
  (select a1.* from ##Table1 a1)
;
CREATE TABLE using_passthru_join AS 
SELECT  * FROM connection to sqltest
  (select a1.var1,b1.var2 from ##Table1 a1
     inner join some_other_sql_server_table b1
     on a1.var1 = b1.var1
   )
;
quit;

[AJ_Brien]

Thank you for this.

My sqltest."##Table1"n gets created perfectly.
table using_libref also gets craeted.

However, TABLE using_passthru_join gives the following: ERROR: The SQLTEST engine cannot be found.
ERROR: A Connection to the sqltest DBMS is not currently supported, or is not installed at your site.

I guess it is some sql server thing that needs to be installed. I found another code which uses #### instead of ##, that code runs well too, but when I try to replace #### in my code, it continues to give all the same errors that it did for ##. So am not sure wat's going on at the moment with this.

[Tom]

Make sure the libref used in the LIBNAME and CONNECT USING statements match the name used in the CONNECTION TO clause.