04-03-2014 07:49 AM
Please help me on this matter.
I am connecting to Remote databases and then referencing it through local library assign but all the datasets are invisible. I can still run the sql query and extract the information but cannot view the tables at all
%let user1 = xxxxxxxx;
options remote = xxxx comamid = tcp;
filename rlink "...";
libname abc odbc dsn = "xxx" ;
libname abc server = user1;
Library abc is mapped but no dataset is visible but query can be made if we already know the table names.
Many Thanks in advance.
04-03-2014 07:57 AM
You have permission to view the database but you do not have permission to view/read the tables in the database. Have the database administrator grant you read access.
04-03-2014 07:58 AM
It may be that you do not have read privilege on the directory, just execute. If the remote server is UNIX, of course.
04-03-2014 08:05 AM
I have the read access rights to the database and database tables. I know the tables and can query but cannot see the tables. thats the whole problem
04-03-2014 08:20 AM
I understand your dilemna. I had the same issue. My issue had to be corrected by the database administrators. In my case I was running PC SAS and accessing the databases through a remote SAS session running on UNIX. The UNIX SAS session had read rights to the database and could bring the data out of the database. I personally did not have read access to the database and I could not see the tables in the database from my PC. I could not see the tables until my userid was given permission to read the database tables.
04-03-2014 08:49 AM
you use ODBC connection defined on the SAS server.
Maybe you should have a look on it. (odbc.ini). Do you access an Oracle database?
If yes, ask your SAS-administrator to change the username to uppercase.
You can define schema with your libname statement, too:
libname abc odbc dsn = "xxx" schema = "YYY";
It is important to use uppercase schema-name with odbc connection for Oracle.
Hopefully it helps.
04-03-2014 11:40 AM
Ya, I am using Oracle database only. I have tried second option by putting Schema is libname statement but that did not work
libname abc odbc dsn = "xxx" schema = "YYYY";
libname abc server = user ;
04-03-2014 12:45 PM
Maybe I'm missing something here. And certainly a more complete code example would help....
You have two library assignments, one within a RSUBMIT and one local. You say that you can successfully execute a query if you know the table. Is that within the remote submit or locally? The reason I ask is that I wonder why you don't have the ODBC option in the local libname, i.e. why you aren't using the ODBC engine locally, since you are using it remotely.
Like I said, maybe I'm missing something.
04-03-2014 02:17 PM
I'm not using ODBC but I am using a local Windows version of SAS to connect to UNIX SAS, Oracle and SAS libraries on the UNIX box.
Within the rsubmit / endrsubmit statements, I define the rfdata library and the userid and encrypted macros for the Oracle DB. These are run on the Unix box.
As mentioned before, for the work library on the remote box, I'm using slibref. I define rfdata on the windows box and use the remote engine to point to it.
These statement allow me to view the datasets using SAS Explorer in Windows SAS. I also do the same for the Oracle connection.
I'm not familiar with ODBC, its been about 15 years since I used it, but this should point you in the right direction.
libname rfdata '/net/rfdata' ;
libname work_r remote slibref=WORK server=rmtbox;
libname rfdata remote '/net/rfdata' server =rmtbox;
libname sandbox oracle user="&userid" pass="&pw" path='sanddb' schema=sandbox;
libname sandbox remote server=rmtbox;