topic Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console in SAS Enterprise Guide

Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

sasprofile — Sun, 23 Aug 2015 05:22:21 GMT

Hi friends,

I have an issue when am trying to access Oracle database from SAS.

I created a ODBC Connection to connect to Oracle DB and added the connection entries to tnsnames.ora on sSolarisserver.

and tested the connection with the libname statement on server and on EG and it has successfully assigned.

but While registering the tables in SAS management console(after creating the library).I am getting the following error:

"No tables were retrieved from this query.Your connection information were incorrect"

When I opened the SAS log,

NOTE: SAS Initialization used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: The autoexec file, /sas/biconfig/Lev1/SASMain/WorkspaceServer/autoexec.sas, was executed at server initialization. No server

log was specified. Add the log option to the server startup command to see details of the autoexec execution (refer to

"Specifying Logging Options" under "Server Startup Command" in the Server Administrator's Guide).

1 LIBNAME GDIW ORACLE PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES MULTI_DATASRC_OPT=IN_CLAUSE DBINDEX=YES

1 ! PATH=pde_pdiw SCHEMA=ph USER=pdrew PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ;

NOTE: Libref PDIW was successfully assigned as follows:

Engine: ORACLE

Physical Name: pde_pdiw

Library is successfully assigned,am not sure why am getting this error.

I have license for SAS/ACCESS Interface to ORACLE and SAS/ACCESS Interface to ODBC and able to resgister tables

from SAS Management Console for all other Oracle librraies

Please help me with this,I would really appreciate all your help

Thanks in advance.

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

Patrick — Sun, 23 Aug 2015 08:51:56 GMT

Defining libraries in the autoexec is the old pre-SAS9 way of doing things. With SAS Metadata you need also to define a database server. " /sas/biconfig/Lev1/SASMain" indicates that you're still using SAS9.1.3 but assuming that you've just maintained old path names here the link to the relevant SAS9.4 docu (read page 114 - 118.): http://support.sas.com/documentation/cdl/en/bidsag/68193/PDF/default/bidsag.pdf

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

SASKiwi — Sun, 23 Aug 2015 09:10:06 GMT

Your LIBNAME statement says you are using the ORACLE engine. Yet you say you are using ODBC connections in SMC. If you are using only ODBC in SMC, the LIBNAME statement in your post has nothing to do with what you are defining in SMC, and as Patrick says is being assigned via a SAS AUTOEXEC file.

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

jakarman — Sun, 23 Aug 2015 10:50:24 GMT

Defining libraries can be done in many ways. See: http://blogs.sas.com/content/sgf/2013/05/15/pre-assign-sas-libraries-if-so-which-method/

Some products and the work of the users like data mining make it very impractical data access to be managed by a godfather DBA and not aligned IT staff.

You library is correctly assigned in the autoexec with access to all users open. Needing a secured access having sensitive data to Oracle you would need to better isolated credentials access leaving it all to Oracle.

When you want to register those tables you are doing that on behalf of an DBA admin role within the SAS environment. That process is not necessary using the same APPserver where the users are running. To overcome that someone at SAS has chosen the way SMC works is to assign the libname first with all definitions that should exist in SAS metadata and use that for importing the metadata. What is going along with this is that an open shared account to access Oracle must exist in the SAS meta data. They failed in checking this with common security guidelines using sensitive data.

You can try the metallib procedure leaving the data management process to the data-owner http://support.sas.com/documentation/onlinedoc/guide/EG43MetaLibraries.pdf

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

sasprofile — Sun, 23 Aug 2015 12:34:23 GMT

Thank you all for your prompt responses

am using SAS 9.2 on Solaris 10 server

and I did not setup anything new for ODBC connection I have just added connection entries

in tnsnames.ora file for new data base requirement on Solaris machine.

and I followed same steps I did for other oracle libraries before and am able to register tables for other oracle libraries but not for this library.

so what steps I need to follow to overcome this issue now

please help

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

jakarman — Sun, 23 Aug 2015 14:01:56 GMT

Your autoexec connection is working. When users are using a libname in their sas-code that will work also.

What doesn't work is that automatic implied library connection used by the SMC importing the data. It could be an external identified=yes and oracle type def is missing. There must be some logging in a appserver somewhere. Can you find that one?

But making is more easy why not using the proc metalib eguide code? Avoiding SMC is avoiding a lot of the mentioned trouble.

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

sasprofile — Sun, 23 Aug 2015 17:20:01 GMT

SO if i run below code from EG then all the tables will be registered from source.

please let me know if there is anything wrong in the code.

proc metalib;

omr (library="My oracle library name");

run;

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

jakarman — Sun, 23 Aug 2015 18:38:26 GMT

SAS(R) 9.2 Language Interfaces to Metadata See also the restrictions (mentioned before).

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

sasprofile — Sun, 23 Aug 2015 20:31:16 GMT

I had ran the below code from EG to register the tables in Oracle library but still I don't see any tables even though it has executed without any errors.

proc metalib;

omr (library="ORACLE CN_IW");

run;

This is the log file I got in the EG and am not sure where its still going wrong

1 ;*';*";*/;quit;run;

2 OPTIONS PAGENO=MIN;

3 %LET _CLIENTTASKLABEL='Program';

4 %LET _CLIENTPROJECTPATH='';

5 %LET _CLIENTPROJECTNAME='';

6 %LET _SASPROGRAMFILE=;

8 ODS _ALL_ CLOSE;

9 OPTIONS DEV=ACTIVEX;

NOTE: Procedures may not support all options or statements for all devices. For details, see the documentation for each procedure.

10 GOPTIONS XPIXELS=0 YPIXELS=0;

11 FILENAME EGSR TEMP;

12 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=HtmlBlue

12 ! STYLESHEET=(URL="file:///C:/Program%20Files/SASHome9.3/SASEnterpriseGuide/5.1/Styles/HtmlBlue.css") NOGTITLE NOGFOOTNOTE

12 ! GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

14 GOPTIONS ACCESSIBLE;

15 proc metalib;

16 omr(libraRy="ORACLE CN_IW");

17 RUN;

NOTE: A total of 0 tables were analyzed for library "ORACLE CN_IW".

NOTE: Metadata for 0 tables was updated.

NOTE: Metadata for 0 tables was added.

NOTE: Metadata for 0 tables matched the data sources.

NOTE: 0 other tables were not processed due to error or UPDATE_RULE.

NOTE: PROCEDURE METALIB used (Total process time):

real time 0.34 seconds

cpu time 0.03 seconds

20 GOPTIONS NOACCESSIBLE;

21 %LET _CLIENTTASKLABEL=;

22 %LET _CLIENTPROJECTPATH=;

23 %LET _CLIENTPROJECTNAME=;

24 %LET _SASPROGRAMFILE=;

26 ;*';*";*/;quit;run;

27 ODS _ALL_ CLOSE;

30 QUIT; RUN;

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

SASKiwi — Sun, 23 Aug 2015 21:06:47 GMT

In my experience it is quite possible to assign without error a LIBNAME to an external database that doesn't show any tables. It is usually the result of the SCHEMA / DATABASE names not being correct. I note you are using Unix so your schema/database names may be case-sensitive.

I suggest you try creating a LIBNAME statement in EG which matches the one you are trying to create in SMC, and see if any tables are visible in the EG server list. This is how I normally troubleshoot database connections.

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

sasprofile — Sun, 23 Aug 2015 21:14:31 GMT

I copied the libname statement of the library from SMC and ran it in EG and its assigning successfully without errors but tables are not registering.

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

SASKiwi — Sun, 23 Aug 2015 21:36:48 GMT

Please post your LIBNAME statement.

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

sasprofile — Sun, 23 Aug 2015 22:18:27 GMT

The schema was in lower case "ph" and I updated the schema for Oracle Library to upper case "PH" and am able to register the tables

but when am trying to open the registered data sets its throwing below error.

SAS Enterprise Guide cannot open the date file: "ORACLE CN_IW.PC_ADD_DAILY_DLOC

Error opening data "PC_ADD_DAILY_DLOC".

[Error] File CN_IW.PC_ADD_DAILY_DLOC does not exist.

[Error] The datasource table CN_IW.PC_ADD_DAILY_DLOC does not exist.

Libname statement is below I got from SMC for the Oracle Library

LIBNAME GDIW ORACLE PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES DBINDEX=YES PATH=pde_pdiw SCHEMA=PH USER=pdrew PASSWORD="{SAS002}9C943B50407B77954D67D4302E92999C" ;

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

SASKiwi — Sun, 23 Aug 2015 22:37:25 GMT

Are you able to open any tables in CN_IW or do they all give you the same error as PC_ADD_DAILY_DLOC?

If you run your GDIW LIBNAME in EG then Expand GDIW in the server list does it show PC_ADD_DAILY_DLOC? If so what happens if you try to open this table? Do you get the same error as when opening via CN_IW?

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

sasprofile — Sun, 23 Aug 2015 22:51:37 GMT

When I changed Multi data source optimization to IN_CLAUSE from None then am able to register tables and able to open tables without any issues from EG.

I did as per below steps

For the Oracle Library under properties-->options-->Advanced options-->Optimization--->I selected to IN_CLAUSE from None for Multi data source optimization

then am able to open the data sets from EG registering.

I think it might have solved

but i have one question when I got oracle connection information i have been given lower case schema "ph" not the upper case.

but when am entering "ph" schema its not working, its working only when am entering it in upper case.

so am not sure whether the user had given wrong information or oracle library wont accept schema in lower case

Re: Throwing Errors for Oracle Library when Registering Tables in SAS Management Console

SASKiwi — Mon, 24 Aug 2015 02:53:04 GMT

Good to hear you are making progress.

There are explicit warnings in the documentation that values for SCHEMA etc can be case sensitive. For example:

http://support.sas.com/documentation/cdl/en/acreldb/68028/HTML/default/viewer.htm#n1h1m1ethqxwryn1wu68m46hdp1u.htm

So unless the case matches, for you upper-case PH, you don't get any tables.