SAS/ACCESS Pass through SQL - works locally, Errors on rsubmit

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

SAS/ACCESS Pass through SQL - works locally, Errors on rsubmit


Greetings,

I have some working code which connects to an oracle server via SAS/ACCESS SQL pass through facility.

We also have a remote server deployed.  I would like to access the data on oracle with the pass through facility and write the output to the remote SAS server in one step.  When I try and do this I get errors.

Here is the working local submit code.

libname Sandbox 'Z:\Reporting\David\SAS Files' ;

PROC SQL;

/* create a connection using the ODBC datasource
you created earlier

Dsn =
ODBC Datasource

Uid =
database user ID

Pwd =
database password

*/

connect to
odbc
as CAS (dsn = '******'
uid = ***** pwd = ************);

/* count the records in the table */

create table
Sandbox.CC_Activity_count 
as

select
*
from connection to CAS

(select
count(*)

from
bdw_dal.cc_Activity);

/* download the records in the table */

create table
Sandbox.CC_Activity 
as

select
*
from connection to CAS

(select
*

from
bdw_dal.cc_Activity);

quit;

This works, but I would rather write to the SAS server in one step instead of first to the local drive then back up to the SAS server.  I tried making the following changes. .

libname Rbox      '/sasdata2/statreporting/Sandbox' server=mynode1 ;

rsubmit;

libname Rbox      '/sasdata2/statreporting/Sandbox';

endrsubmit;

rsubmit;

PROC SQL;

/* create a connection using the ODBC datasource
you created earlier

Dsn =
ODBC Datasource

Uid =
database user ID

Pwd =
database password

*/

connect to
odbc
as CAS (dsn = '******'
uid = ******** pwd = *********);

/* count the records in the table */

create table
Rbox.CC_Activity_count 
as

select
*
from connection to CAS

(select
count(*)

from
bdw_dal.cc_Activity);

/* download the records in the table */

create table
Rbox.CC_Activity 
as

select
*
from connection to CAS

(select
*

from
bdw_dal.cc_Activity);

quit;

endrsubmit;

These are the Errors I get:

ERROR: Could not load /sas/SASFoundation/9.2/sasexe/sasodb (49 images loaded)

ERROR: ld.so.1: sas: fatal: libodbc.so: open failed: No such file or directory

ERROR: The SAS/ACCESS Interface to ODBC cannot be loaded. The SASODB   code appendage could not

be loaded.

ERROR: A Connection to the odbc DBMS is not currently supported, or is not installed at your site

Is this an issue with our deployment or configuration of our SAS server?  If so, what needs to be done to address the issues?

Thank you in advance,

David Bess


Accepted Solutions
Solution
‎08-23-2013 07:30 PM
Super User
Posts: 17,963

Re: SAS/ACCESS Pass through SQL - works locally, Errors on rsubmit

The SAS server needs to be able to see the SQL server and for some reason with this set of code it can't. So the question is how to make it. It really depends on your setup to a certain degree so you might be better talking to SAS Tech Support for this one. 

View solution in original post


All Replies
Solution
‎08-23-2013 07:30 PM
Super User
Posts: 17,963

Re: SAS/ACCESS Pass through SQL - works locally, Errors on rsubmit

The SAS server needs to be able to see the SQL server and for some reason with this set of code it can't. So the question is how to make it. It really depends on your setup to a certain degree so you might be better talking to SAS Tech Support for this one. 

Super Contributor
Posts: 644

Re: SAS/ACCESS Pass through SQL - works locally, Errors on rsubmit

You need SAS Access to Oracle or ODBC on your remote server, and it needs to be configured to access your Oracle database with userid etc available to you.  Your existing connection via ODBC is specific to your local server.  If you have the appropriate software installed this becomes an Oracle issue.

Richard

Valued Guide
Posts: 3,208

Re: SAS/ACCESS Pass through SQL - works locally, Errors on rsubmit

If you have an internal contact for the Server installation at your organization you should contact him.
Mostly there are a lot of requirements on how tools must be installed and administerd monitored and serviced in organizations.
For the databases like Oracle for the technical installation and the content it is the same. Oracle corp is not responsible for your dbms.

I don not believe SAS (including SAS Tech) ia accountable and responsible for all those levels of a business.

Even with outsourcing this still applies as it is then audited by a renewed SAS 70 guidelines.   

Options are:

- The license on the server is not having those options or using eg SAS/access Oracle (a better one)

  You could check that by running:  "Proc setinit noalias ; run; '

- The installation on the server-side is missing the correct configuration In SAS or de DBMS connection (TNSNAMES)

  IF and when only if the TNSNAMES is the problem you can give all connection information in SAS in the Path= option.   

---->-- ja karman --<-----
Occasional Contributor
Posts: 9

Re: SAS/ACCESS Pass through SQL - works locally, Errors on rsubmit

Thanks everyone,

I should clarify my role and what I am trying to accomplish.  I am not just a user getting an error.  I am one of the business owners of our enterprise wide SAS adminstration.  I am trying to get this stood up and working so that everyone can leverage it.  I can work with our SAS server admin and/or our infrastructure engineers as needed to get everything set up.  But first I need to do some research and collect and analyze some requirements to get th ball rolling.  No one in house really has deep experance with sas adminstration.  With this in mind, anything you can tell me about what needs to be done to get this up and running is greatly aperciated.

Thanks Again,

David Bess

Super User
Posts: 17,963

Re: SAS/ACCESS Pass through SQL - works locally, Errors on rsubmit

Hi David,

Basically, you need to ask some questions about your server.

First, what environment (UNIX, Windows) and can it even see the Oracle server? If it can 'see' the server then it becomes a simple problem on how to change the code.

Sadly, what works on local doesn't always work on the server, particularly when working with different environments. I just think of the Server as a total separate install and how would I get it working there.

Hope that helps and isn't info you already know.

Cheers!

Occasional Contributor
Posts: 9

Re: SAS/ACCESS Pass through SQL - works locally, Errors on rsubmit

It is a solaris server.  I had firewall rules imlemented so the servers can see eachother.  I tested the rule using TELNET and it appears to be fuctioning properly.

We do not have an oracle client installed on the server.  Someone suggested that if we installed an oracle client on the server and configured the drivers the same way they are configured deskside this would may address the issue?  I wish I could find a white paper on this topic, it seems like a troublesome thing to nail down these requirements.  Should I just have an oracel client instaled on the server and then ask our server admin to configure an ODBC connection?  

Super User
Posts: 17,963

Re: SAS/ACCESS Pass through SQL - works locally, Errors on rsubmit

There's also Tech Support that's usually more than willing to help. 

I'd recommend searching lexjansen.com for such papers. You could also post in the SAS administrators forum for more details, but the install instructions are usually online somewhere.

Valued Guide
Posts: 3,208

Re: SAS/ACCESS Pass through SQL - works locally, Errors on rsubmit

DavidBess,

Please do the namings correct. a oracle interface by SAS/ACCESS-Oracle  is different as to SAS.ACCESS-ODBC

ODBC is Open Data Base Connectivity is a generic standard See: ODBC - Wikipedia, the free encyclopedia.    
For: SAS/Access  see SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Second Edition.   The special named access interface are having more aligned approaches to the mentioned DBMS.

You will find that for oracle and others the drivers on unix exist to be downloaded from the DBMS vendor (eg Oracle). An exception is Microsoft. For that you will need an ODBC driver on Unix (sun now oracle). For the confusion if you would use an oracle driver in Windows you would not need to use the ODBC (odbcadm32.exe) at Windows.

 

A part of the installation on Unix is described at the Unix configuration guide: http://support.sas.com/documentation/installcenter/en/ikfdtnunxcg/66380/PDF/default/config.pdf

For the confusion configuring  a ODBC connection is in this document installing the ODBDC driver.

You need to configure the connection (server name dbms sid etc) in an other step. The oracle client can use TNSNAMES for tthat but you can code it in the path option.  

---->-- ja karman --<-----
Occasional Contributor
Posts: 9

Re: SAS/ACCESS Pass through SQL - works locally, Errors on rsubmit

Jaap, thank you for this document

http://support.sas.com/documentation/installcenter/en/ikfdtnunxcg/66380/PDF/default/config.pdf

This had some helpful information which has really helped us troubleshoot.

Valued Guide
Posts: 3,208

Re: SAS/ACCESS Pass through SQL - works locally, Errors on rsubmit

Documenation of  the Installation: SAS 9.4 Install Center Documentation Other versions can be found at the location aside this.

Very advisable to use SAS Platform Admin knowledge within your organization when your enviroment has more strict requirements.

---->-- ja karman --<-----
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 1770 views
  • 3 likes
  • 4 in conversation