04-02-2014 09:16 PM
Yes. The connection definitions are different. I've used both but we currently use ODBC as Microsoft have announced that OLEDB support will be dropped eventually. Performance is very similar.
BTW you can avoid using the ODBC Administrator tool under Windows for defining ODBC sources and completely define ODBC sources in SAS - not that many SAS users know about this.
04-03-2014 03:24 AM
Indeed interesting how to define ODBC completely in SAS.
I know it is possible with Oracle to do this. That is not using the tnsnames file but specifying all connection parameters in SAS along with the path= option.
(ADDRESS = (PROTOCOL = TCP) (HOST = <dns oracle-server>) (PORT = <port oracle-server>) )
(CONNECT_DATA = (SID=<ora sid>) )
This approach of defining the connection is also not very well documented as sas docs are always referencing that tnsnames.ini file.
Would expect the ODBC approach of specifying in the same way.
04-03-2014 02:33 PM
For Jaap's benefit here is how you define an ODBC connection completely in SAS, assuming you use Windows user authentification. You can do this for LIBNAMEs as well.
connect to odbc (noprompt = "server=ServerName;DRIVER=SQL Server;Trusted Connection=yes;DATABASE=DatabaseName;");
create table as select .....;
04-03-2014 06:28 AM
I asked our SAS technical representative this question some time ago. I was told that the functions of the two methods are the same and was advised to use the simpler ODBC. This also fits in better with other software we have which uses the same ODBC DDSNs.