ODBC and OLEDB

Reply
Contributor
Posts: 35

ODBC and OLEDB

In a Windows environment, is there a difference between an ODBC and an OLEDB connection to a Microsoft SQL server database?

Super User
Posts: 3,104

Re: ODBC and OLEDB

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.    

Contributor
Posts: 35

Re: ODBC and OLEDB

Thanks.

How do you completely define ODBC sources in SAS?  I'm one of the many who doesn't know.

Valued Guide
Posts: 3,208

Re: ODBC and OLEDB

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.

path=´(DESCRIPTION=

     (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.     

---->-- ja karman --<-----
Super User
Posts: 3,104

Re: ODBC and OLEDB

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.

proc sql;

  connect to odbc (noprompt = "server=ServerName;DRIVER=SQL Server;Trusted Connection=yes;DATABASE=DatabaseName;");

    create table as select .....;

Valued Guide
Posts: 3,208

Re: ODBC and OLEDB

thx saskiwi...

---->-- ja karman --<-----
Contributor
Posts: 32

Re: ODBC and OLEDB

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.

Ask a Question
Discussion stats
  • 6 replies
  • 776 views
  • 6 likes
  • 4 in conversation