topic MS SQL connection with SAS using PROVIDER= SQLNCI10.1 vs SQLOLEDB in Administration and Deployment

MS SQL connection with SAS using PROVIDER= SQLNCI10.1 vs SQLOLEDB

RahulG — Mon, 04 Jan 2016 06:46:48 GMT

I want to connect SAS with MS SQL server database. I found that if I use PROVIDER =SQLOLEDB then SQL date column is read as char in SAS.

If I use PROVIDER=SQLNCI10.1 then SAS read SQL date column as SAS date.

Refer to http://support.sas.com/kb/39/188.html

My question - what is this PROVIDER=SQLNCI10.1 option is doing that makes SAS to read SQL date as date correctly?

What are other difference would be there if i use PROVIDER=SQLNCI10.1 instead of PROVIDER =SQLOLEDB?

Re: MS SQL connection with SAS using PROVIDER= SQLNCI10.1 vs SQLOLEDB

Mark_sas — Tue, 05 Jan 2016 18:26:56 GMT

Microsoft introduced better date/time data types in this version of their Microsoft SQL Server Native Client OLE DB Provider. That's why SAS is able to properly input them as SAS dates. You can find more information about the additional data types on Microsoft's site here. This provider is one of the ones shipped with SQL Server 2008 R2, and is described here. You can view its capabilities, as well as those of previous and subsequent versions from this site.