topic Re: Get SYNONYMS via SAS/Access to ODBC in SAS Data Management

Get SYNONYMS via SAS/Access to ODBC

user24feb — Tue, 10 Nov 2015 12:34:59 GMT

Hello,

I would like to access the available synonyms via odbc.

If I use this code, I get the tables and views, but I can't find the synonyms:

Libname sqlserv ODBC Schema=DBO DSN=sassql;

Proc SQL;
  Create Table Work_Cont_Dict As Select * From Dictionary.Tables Where Libname eq UpCase('sqlserv');
Quit;

* proc datasets gives the same result - only views and tables;
Proc Datasets Library=sqlserv; 
  *Contents Out=Contents Data=_ALL_ Details; * if I remove the comment, it gives me an error;
Run;

How can I get a list of available synonyms?

Thanks&kind regards

Re: Get SYNONYMS via SAS/Access to ODBC

LinusH — Tue, 10 Nov 2015 12:54:58 GMT

If I look through SAS/ACCESS doc, it seems there are only support for synonyms in Netezza and Oracle.

So my guess is that you nedd to use explicit SQL pass-thru to access those.

Re: Get SYNONYMS via SAS/Access to ODBC

user24feb — Tue, 10 Nov 2015 14:10:11 GMT

Thanks. I needed something like:

Proc SQL NoPrint;
  Connect To ODBC (DSN=sassql);
  Create Table Synonyms As Select * From Connection To ODBC 
  (SELECT * FROM SYS.SYNONYMS) Order By name ;
  Disconnect From ODBC;
Quit;