Desktop productivity for business analysts and programmers

Unable to query from MS SQL Server view to SAS EG

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

Unable to query from MS SQL Server view to SAS EG

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


Accepted Solutions
Solution
‎03-31-2015 11:01 AM
Frequent Contributor
Posts: 89

Re: Unable to query from MS SQL Server view to SAS EG

Posted in reply to learner_sas

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.

View solution in original post


All Replies
PROC Star
Posts: 1,334

Re: Unable to query from MS SQL Server view to SAS EG

Posted in reply to learner_sas

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

Super User
Posts: 4,018

Re: Unable to query from MS SQL Server view to SAS EG

Posted in reply to learner_sas

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

Frequent Contributor
Posts: 89

Re: Unable to query from MS SQL Server view to SAS EG

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 ;



Super User
Posts: 10,849

Re: Unable to query from MS SQL Server view to SAS EG

Posted in reply to learner_sas

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

Frequent Contributor
Posts: 89

Re: Unable to query from MS SQL Server view to SAS EG

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

Solution
‎03-31-2015 11:01 AM
Frequent Contributor
Posts: 89

Re: Unable to query from MS SQL Server view to SAS EG

Posted in reply to learner_sas

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.

Super User
Posts: 10,849

Re: Unable to query from MS SQL Server view to SAS EG

Posted in reply to learner_sas

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 756 views
  • 6 likes
  • 4 in conversation