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

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
Trusted Advisor
Posts: 1,050

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

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

Respected Advisor
Posts: 3,059

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

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 ;



Grand Advisor
Posts: 9,567

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

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

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.

Grand Advisor
Posts: 9,567

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

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.

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

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