Architecting, installing and maintaining your SAS environment

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

Reply
Super Contributor
Posts: 266

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

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?

 

SAS Employee
Posts: 102

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

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.

Ask a Question
Discussion stats
  • 1 reply
  • 353 views
  • 2 likes
  • 2 in conversation