Creating OLE DB Connection for DI Studio Tables

[jwhite]

One of our near future goals is to try and have DI Studio write to tables on MS SQL server.

I've been making it a game attempt to follow this document, but haven't found success.

http://support.sas.com/techsup/technote/ts783.pdf

I have some questions as I think one of these is where I'm getting tripped up...and they're related to SAS Management Console.

1) In Management Console, when I create the OLE DB server, i need to provide a Datasource. Is this not a database on the SQL server we wish to connect to. Example: "SQL Server\Database"

2) The document says to provide "SQLNCI11" as the 'Provider'. Is this always the case or should I look for something else?

The error I'm getting is "ERROR: Error trying to establish connection: Unable to create Data Source.: Class not registered". This appears in the log right after the log writes out the Provider and Datasource which is why I believe I have one of these wrong.

Any suggestions?

Thanks!

-Jeff

[JBailey]

Hi @jwhite

I think the error is occurring because the SQL Server Native Client 11 (SQLNCI11) OLE DB provider is not installed on the machine where SAS is running.

You can try this, which may help you get up and running more quickly. Use the Microsoft OLE DB Provider for SQL Server provider. I think it is included on the machine by default (I may be wrong about this). Here is an example LIBNAME statement:

libname myoledb oledb init_string="provider=Microsoft OLE DB Provider for SQL Server;
data source=mysqlserver.mycompany.com;
user id=myuser;password=SomePW1";

The following code can help but I think you have to run it on the machine where your SAS server is running.

libname myoledb oledb prompt=yes;

This will invoke the Data Link Properties dialog. Do you see SQL Server Native Client 11.0? 

If you don't see it then someone will need to install the SQL Server Native Client 11.0. If you do see it there is a different problem.

The following SAS code may help you...

/*
  Good for exploring...
*/
libname myoledb oledb prompt=yes;

/*
  This works...
*/
libname myoledb oledb init_string="provider=SQL Server Native Client 11.0;
data source=mysqlserver.mycompany.com;
user id=myuser;password=SomePW1";

/* 
  This works...
*/
libname myoledb oledb init_string="provider=SQLNCLI11;
data source=mysqlserver.mycompany.com;
user id=myuser;password=SomePW1";

/*
  ERROR: Error trying to establish connection: Unable to create Data Source.: Class not registered
*/
libname myoledb oledb init_string="provider=SQLNCLI13;
data source=mysqlserver.mycompany.com;
user id=myuser;password=SomePW1";

/*
  This works and used a different OLE DB provider
*/
libname myoledb oledb init_string="provider=Microsoft OLE DB Provider for SQL Server;
data source=mysqlserver.mycompany.com;
user id=myuser;password=SomePW1";

Good luck,

Jeff