topic Re: Unable to query from MS SQL Server view to SAS EG in SAS Enterprise Guide

Unable to query from MS SQL Server view to SAS EG

learner_sas — Mon, 30 Mar 2015 15:16:41 GMT

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

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

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

TomKari — Mon, 30 Mar 2015 16:49:06 GMT

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

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

SASKiwi — Tue, 31 Mar 2015 09:36:18 GMT

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

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

Ksharp — Tue, 31 Mar 2015 12:11:07 GMT

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

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

learner_sas — Tue, 31 Mar 2015 13:03:47 GMT

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 ;

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

Ksharp — Tue, 31 Mar 2015 13:10:19 GMT

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

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

learner_sas — Tue, 31 Mar 2015 14:06:03 GMT

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

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

learner_sas — Tue, 31 Mar 2015 15:01:53 GMT

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.