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.
... View more
