How do I create a volatile table in teradata using OLEDB connector to SAS

Question

This is not a purely Teradata Question. I am not asking to create a volatile table in Teradata. This is a question for someone who uses OLEDB connection to Teradata from SAS. I am aware Volatile tables can be created in a heartbeat using SQL assistant or even Teradata Interface to SAS. But if there are users who are NOT on the SAS grid and they don't have SAS i/f to teradata installed amd they use OLEDB to connect SAS and Teradata.

Here is a snippet of code that Runs well using OLEDB which gives some idea what we are talking about.

Below code will run well:

proc sql;
connect to OLEDB(Provider='MSDASQL' Extended_Properties='DRIVER={Teradata};DBCNAME=UDWPROD;AUTHENTICATION=ldap' UID="&DMID" PWD="&DMPWD");
  create table out.TB as 
  select a.*, b.C7
  from connection to OLEDB
(select 
DB.C1,
DB.C2,
from 
DB
) as a inner join mytb as b
on DB.C9=b.C9
and (intnx('year',b.C7,-1,'same') le a.fst_srvc_dt lt intnx('year',b.C7,1,'same'));
%put &sqlxmsg ;
disconnect from OLEDB ;
quit;

Along the same lines we tried to run this but either there is a syntax error ( hopefully) or it doesn't like it ( bummer on that ..wont be too good ) :

  proc sql;
  connect to OLEDB(Provider='MSDASQL' Extended_Properties='DRIVER={Teradata};DBCNAME=SITEPRD;AUTHENTICATION=ldap' UID="&DMID" PWD="&DMPWD");
  execute (create multiset volatile table idlist (my_id integer, mydate date)
  ON COMMIT PRESERVE ROWS) by teradata;
  execute (COMMIT WORK) by teradata;
  insert into idlist
  select distinct MyId_sas, mydate
  from mysource;
quit; 3:52 PM 
And got this output: 3:52 PM 
proc sql;
28     connect to OLEDB(Provider='MSDASQL' Extended_Properties='DRIVER={Teradata};
28 ! DBCNAME=SITEPRD;AUTHENTICATION=ldap' UID="&DMID" PWD="&DMPWD");
SYMBOLGEN:  Macro variable DMID resolves to ConfusedUser
SYMBOLGEN:  Macro variable DMPWD resolves to Youbetcha!

29     execute (create multiset volatile table idlist (my_id integer, mydate date)
30     ON COMMIT PRESERVE ROWS) by teradata;
ERROR: The TERADATA engine cannot be found.
ERROR: A Connection to the teradata DBMS is not currently supported, or is not installed at
       your site.
31     execute (COMMIT WORK) by teradata;
ERROR: The TERADATA engine cannot be found.
ERROR: A Connection to the teradata DBMS is not currently supported, or is not installed at
       your site.
32     insert into idlist
33     select distinct MyId_sas, mydate
34     from mysource;
ERROR: File WORK.idlist.DATA does not exist.
NOTE: SGIO processing active for file WORK.mysource.DATA.
35   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           9.19 seconds
      cpu time            1.75 seconds 

This is what's presently installed AFAIK for SAS

NOTE: PROCEDURE SETINIT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
Operating System:   WX64_SV .
Product expiration dates:
---Base SAS Software
        30DEC2016
---SAS/STAT
        30DEC2016
---SAS/GRAPH
        30DEC2016
---SAS/Secure 168-bit
        30DEC2016
---SAS/Secure Windows
        30DEC2016
---SAS/ACCESS Interface to PC Files
        30DEC2016
---SAS/ACCESS Interface to ODBC
        30DEC2016
---SAS/ACCESS Interface to OLE DB
        30DEC2016
---SAS Workspace Server for Local Access
        30DEC2016
---High Performance Suite
        30DEC2016

How do you get it to work?

Answer 1

You have two mistakes. First you defined a connection to OLEDB and then tried to execute commands on a connection named TERADATA that wasn't defined. Either add AS TERADATA to your CONNECT statement so that the connection is named or change the EXECUTE statement to use the OLEDB connection name instead.

Also your insert statement at the end is going to create a table in the SAS WORK library. Did you expect it to be able to insert or read from the OLEDB connect? If you want to insert data from SAS into a Teradata table then you need to create libref that points to Teradata. There is no need to "create" the table first. SAS will happily create the table for you.

libname TERADATA OLEDB ... connection details ... ;
proc sort data=mysource(keep=myid_sas mydate) nodupkey out=TERADATA.idlist;
  by _all_;
run;

Answer 2

I don't have a teradata instance to reference, but I think your issue is that you do not create the oledb connection with a reference name, then you try to reference it as "teradata".

Try this:

connect to OLEDB as teradata (Provider='MSDASQL' Extended_Properties='DRIVER={Teradata};DBCNAME=SITEPRD;AUTHENTICATION=ldap' UID="&DMID" PWD="&DMPWD");