CODEWITHSUNDEEP
sas
Maz
Maz

Reputation: 1719

Empty library while connecting SAS to Oracle database via OBC

I'm trying to connect SAS to a new database on Oracle via ODBC connection. I cannot connect to the engine directly as I don't have this license to do so.

I have set up the database correctly and I can access the views on Power BI with the Oracle connection.

My next step is to add this to the ODBC Data Source Administrator. When I add this to System DSN and test the connection here, it works fine.

The next step is connecting SAS to this ODBC and I have tried to do this via a libname statement.

libname mylib odbc datasrc="DATASRC" USER="userid" password="password" schema=XXDATAXX;

This statement returns a correct assignment of the library and makes the connection. I have tested it is actually working by putting in a wrong user id/ password and it does indeed not assign.

My issue is that when it does assign correctly, the SAS library is empty and I see nothing. Has anyone had this error occur before or have some ideas?

Things I have also tried is:

Upvotes: 1

Views: 721

Answers (1)

Richard
Richard

Reputation: 27508

You may need to specify driver specific name value pairs in the ACCESS engine NOPROMPT= option. Oracle drivers should recognize parameter name Initial Catalog=. The valid parameter names and values depend on the ODBC driver you are utilizing and the data base system it is connecting you to.

Untested:

libname mylib odbc 
  datasrc="DATASRC" 
  USER="userid" 
  password="password"  
  schema=XXDATAXX
  NOPROMPT='Provider=OraOLEDB.Oracle;Initial Catalog=myDataBase;'
;

See SAS Doc "LIBNAME Statement for the ODBC Engine"

NOPROMPT=<'>ODBC-connection-options<'>

  • specifies connection options for your data source or database. Separate multiple options with a semicolon. If you do not specify enough correct connection options, an error is returned. No dialog box is displayed to help you complete the connection string.

https://www.connectionstrings.com/oracle/ is also a good reference for different noprompt connection strings to try.

Upvotes: 1

Related Questions