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
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.
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
With SQL Server you usually need to specify DATABASE and SCHEMA as LIBNAME options if they are not defined in your DATASOURCE.
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 ;
Can you make a ODBC connection and plus SCHEMA=dbo ?
We do not have SAS ODBC license therefore we have to go through OLEDB route
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.