ODBC libref Issue

[darrylovia]

All

I need to see all the tables in my connection to a SQL Server database using SAS/ACCESS to ODBC.

Details:
The ODBC datasource has been created on the SAS Server (Windows Server).
I am able to connect via the libname statement
"libname testdb ODBC DATASRC='test-dev' user='testdb' pw=XXXXXXXXXX readbuff=1000;" And I can run SAS procedures on known tables, for example when I run "proc contents data=testdb.inv_stage;
run;" I get results that I expect.

The Problem:
In EG under the Server List, when I open the Libraries and I double click by libname "testdb", I do not see any tables.

I do not have this problem when I connect to ORACLE using SAS/ACCESS for ORACLE. I can see all the tables/views in the ORACLE db in my SAS session.

The SQL Server database has a plethora of tables/views, and not having them all listed under the libname in EG is inconvient.

Does anyone else have this problem or is there some LIBNAME option that I am missing.

Thanks
-D

[deleted_user]

Yes, I have had this problem.

I will assume that you have set up the EG library using the Enterprise Explorer and a metadata repository.

In the LIbrary Properties Options, you need to set the schema, for example

schema=dbo

the schema is the actual owner of the tables.

You probably didn't have this issue with Oracle because you probably used the same user id of the actual owner.

If a table is selectable by PUBLIC, it means that anyone can select against it, but it does not necessarily mean that EG will display the table. It's another minor annoyance for how EG and the libname statement works.

So, if you want to see absolutely all the tables and views in a database, you need a library for each schema.

I have not experimented to see if it is possible set up and use a single libref that is a concatenation of libraries for combining multiple schemas into one library view.

[darrylovia]

Chuck you rock!

by adding schema='dbo' to the LIBNAME statement, I can now see the tables in the DB.

"libname testdb ODBC DATASRC='test-dev' user='testdb' pw=XXXXXXXXXX readbuff=1000 schema='dbo';"

-Thanks
-D