Cannot get odbc or oledb to access/display sql server tables

[JamesPower4]

Hello:  I have created an ODBC source on my PC to read from SQL Server.  When I test the ODBC connection it is successful; also I can view the SQL Server tables using Microsoft Access.  I'm using NT authentication.

However, I cannot create a SAS Libname that will also show or display the tables.  I've tried both the ODBC and OLEDB approaches.  In both cases the Libname is created successfully.  However, no tables are listed when I open that library.  Here's an example log, in which I cut code from a SAS technical paper ("Accessing a Microsoft SQL Server Database from SAS® under Microsoft Windows") and pasted it into SAS:

12 libname sqlsrv oledb;
NOTE: Libref SQLSRV was successfully assigned as follows:
Engine: OLEDB
Physical Name:
13 %put %superq(sysdbmsg); /* V9 syntax */
OLEDB: Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial
Catalog=NCCA;Data Source=p2626xorth005

The above example displays prompts for the necessary Libname options, and then stores the values in %sysdbmsg.

Any ideas about what I'm missing?

Using Windows 10, SAS 9.4., and not sure of the SQL Server version

Thanks in advance.

Jim Power

[Reeza · Accepted solution]

libname test odbc dsn='dsn Name' schema=dbo;

Try something like above, with the dsn being the name you put in the ODBC connection and the schema being the schema you're trying to access. If it's multiple schema's you'll need a new libname for each of them. 

---

@JamesPower4 wrote:

Hello:  I have created an ODBC source on my PC to read from SQL Server.  When I test the ODBC connection it is successful; also I can view the SQL Server tables using Microsoft Access.  I'm using NT authentication.

However, I cannot create a SAS Libname that will also show or display the tables.  I've tried both the ODBC and OLEDB approaches.  In both cases the Libname is created successfully.  However, no tables are listed when I open that library.  Here's an example log, in which I cut code from a SAS technical paper ("Accessing a Microsoft SQL Server Database from SAS® under Microsoft Windows") and pasted it into SAS:

12 libname sqlsrv oledb;
NOTE: Libref SQLSRV was successfully assigned as follows:
Engine: OLEDB
Physical Name:
13 %put %superq(sysdbmsg); /* V9 syntax */
OLEDB: Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial
Catalog=NCCA;Data Source=p2626xorth005

The above example displays prompts for the necessary Libname options, and then stores the values in %sysdbmsg.

Any ideas about what I'm missing?

Using Windows 10, SAS 9.4., and not sure of the SQL Server version

Thanks in advance.

Jim Power

---

[JamesPower4]

Reza:  Many thanks!  That was the problem - I wasn't specifying the schema.

Again, many many thanks.

Jim Power