topic Re: Can't see the dataset after Libname OLEDB? in SAS Data Management

Can't see the dataset after Libname OLEDB?

ybz12003 — Thu, 18 Oct 2018 14:51:45 GMT

Hello:

I ran the follow codes below. I found out that I can't open the table "18RSV" in the library RSV properly with error message after running the codes. Please see the details below.

Libname RSV OLEDB 
		provider="sqloledb" 
		properties=("Data Source"="Inquire/2018"
					"Persist Security Info"="True" 
					"User ID"="test" 
					"Password"="&pin" 
					"Initial Catalog"="RSV")
		schema=dbo;

PROC CONTENTS DATA=RSV."18RSV"n OUT=RSV18 NOPRINT;RUN;

So the error message is shown that The table RSV."18RSV"n either does not exist or cannot be displayed. However, I checked the RSV18 table is existed. In addition, I found all of the variables properties in "18RSV" are shown after right clicking the "View Columns". I found out the RSV is in OLEDB engine instead of V9. I am not sure whether it cause the problem.

I tried pull the data from RSV to work library, the table doesn't work.

data work.RSV18;
	set RSV."18RSV"n; 
run;

Please advise how to fix it, thanks.

Re: Can't see the dataset after Libname OLEDB?

ballardw — Thu, 18 Oct 2018 17:55:16 GMT

Run this bit of code:

proc options option=validmemname;
run;

If the log does not show something like

 VALIDMEMNAME=EXTEND

Then you need to set the validmemname option to extend:

options validmemname=EXTEND;

to allow non-standard dataset names.

Check the documentation on the option for details on restrictions to special characters and such.

Re: Can't see the dataset after Libname OLEDB?

ybz12003 — Fri, 19 Oct 2018 15:25:53 GMT

So I run the proc option after libname OLEDB, the log window show notes below.

9 LIBNAME RSV OLEDB

10 provider="sqloledb"

11 properties=("Data Source"="Inquire\2018"

12 "Persist Security Info"="True"

13 XXXXXXXXX=XXXXXXX

14 XXXXXXXXXX="&pin"

15 X XXXXXXXXXXXXXXXXX=XXXXXX)

16 schema=dbo;

NOTE: Libref RSV was successfully assigned as follows:

Engine: OLEDB

Physical Name: sqloledb

17 proc options option=validmemname;

18 run;

SAS (r) Proprietary Software Release 9.4 TS1M3

VALIDMEMNAME=COMPAT

Specifies the rules for naming SAS data sets, SAS data views, and item stores.

NOTE: PROCEDURE OPTIONS used (Total process time):

real time 0.03 seconds

cpu time 0.00 seconds

Re: Can't see the dataset after Libname OLEDB?

SASKiwi — Fri, 19 Oct 2018 22:10:07 GMT

Are you using Enterprise Guide? If so expand the RSV library listed in the Server List window. Do you see any table names?