Use SAS provider in OPENROWSET

• Greg Snidow

  SSCoach

  Points: 16108

  More actions

  November 9, 2012 at 11:45 am

  #284690

  Greetings all. SAS (analytical software) comes with 4 data providers which conform to the Microsoft OLE DB standard. I was able to open and read a SAS dataset (.sas7bdat) from Excel with the following...

  Dim cn As ADODB.Connection

  Dim rs As ADODB.Recordset

  Set cn = New ADODB.Connection

  Set rs = New ADODB.Recordset

  With cn

  .Provider = "sas.LocalProvider"

  .Properties("Data Source") = "c:\Imports"

  .Open

  End With

  rs.CursorType = adOpenStatic

  rs.LockType = adLockReadOnly

  rs.Open "pilotdata", cn, adOpenStatic, adLockReadOnly, adCmdTableDirect

  If this works, I am thinking I could also use OPENROWSET, and indeed the provider appears in the list of providers under linked servers. I have tried everything in every possible combination I could think of to no avail, I just can't figure out the syntax. I know it will be impossible to test without having SAS installed on your machine, but I know some of you are VB gurus as well as TSQL, and I'm hoping someone will be able to translate my VB into OPENROWSET syntax. Thank you.

  Greg
  _________________________________________________________________________________________________
  The glass is at one half capacity: nothing more, nothing less.

• Greg Snidow

  SSCoach

  Points: 16108

  More actions

  November 9, 2012 at 12:07 pm

  #1557770

  Maybe this will help. I was able to use the sas.LocalProvider with the data import wizard by supplying only 'c:imports' as the data source, and 'pilotdata' as the location in the data link properties dialog. Is there a way to view the code that is being generated?

  Greg
  _________________________________________________________________________________________________
  The glass is at one half capacity: nothing more, nothing less.