Unable to query from MS SQL Server view to SAS EG

[learner_sas]

Hello Everybody,

I have my SAS connected to MS SQL Server using following Libname

LIBNAME libone OLEDB USER=test password=test datasource='www.medicine.com' PROVIDER='sqloledb2.1';

I can successfully connect to database  with following log message

NOTE: Libref libone was successfully assigned as follows:

      Engine:        OLEDB

      Physical Name: sqloledb2.1

But I can not query from the MS SQL Server views in my SAS EG. If I open my MS SQL Server Management Studio then I can see all the views.  I am wondering what I am missing here.

If I submit below code:

data test;

  set libone.test_view;

run;

I get following as my log with error

13       

14         GOPTIONS ACCESSIBLE;

15         data test;

16           set libone.test_view

;

ERROR: File libone.test_view.DATA does not exist.

17         run;

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.TEST may be incomplete.  When this step was stopped there were 0 observations and 0 variables.

WARNING: Data set WORK.TEST was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

      real time           0.03 seconds

      cpu time            0.00 seconds

Thank you,Vi

[TomKari]

I've seen three things cause this:

1. Pointing at a schema different from the one you want;

2. Permissions don't allow you to see the table.

3. I think that these objects are case-sensitive. Make sure you use upper/lower case correctly.

Hope it helps,

  Tom

[SASKiwi]

With SQL Server you usually need to specify DATABASE and SCHEMA as LIBNAME options if they are not defined in your DATASOURCE. 

[learner_sas]

I am able to insert dbo as my schema ( I have default as pointed out. Since I have OLEDB I used DATABASE(I think I can not use DSN)  but still I am not able to connect. Below is my code.

LIBNAME XXX OLEDB USER=usrone password=usrone database="dbname" datasource='server_name' PROVIDER='SQLOLEDB23.1'  SCHEMA=dbo ; gives error in the log but following code do not gives error after removing database options but still I can not see my tables and views.

LIBNAME XXX OLEDB USER=usrone password=usrone  datasource='server_name' PROVIDER='SQLOLEDB23.1'  SCHEMA=dbo ;

[Ksharp]

Can you make a ODBC connection and plus SCHEMA=dbo ?

[learner_sas]

We do not have SAS ODBC license therefore we have to go through OLEDB route

[learner_sas · Accepted solution]

I found it.

libname test oledb init_string='Provider=><SQLOLEDB.1 or SQLOLEDB.10>;Password=<yourpassword>;Persist Security Info=True;

         User ID=<yourusername>;Initial Catalog=<yourdatabasename>;Data Source=your_server_name' schema=<name of your schema usually dbo>;

this gave me write answer. Hope somebody will be able to use this code.

[Ksharp]

As SASKiwi pointed out . Sometimes you need a schema .

LIBNAME libone OLEDB USER=test password=test datasrc=xxx    schema=dbo ;

Talk to your Admin and find what is schema , default is dbo .

Xia Keshan