topic Re: SAS Microsoft SQL connection issue in SAS Programming

SAS Microsoft SQL connection issue

BNT1 — Wed, 20 Dec 2017 14:15:12 GMT

Hello everybody,

I would like to access a Microsoft SQL database thorugh SAS, but do receive the following error after running the commands.

A connection to the database through Windows ODBC driver is no problem at all. A connection via Excel is also working.

So the user and database is working.

Just through SAS I get the following messages:

When I try it with a 'proc sql' statement:

"The ODBC engine cannot be found etc"

"A connection to the ODBC DBMS is currently not supported, or it is not installed at your site"

"ERROR: Java repository file(s) are missing or inaccessible."

"ERROR: Unable to initialize the ODS classpath during SAS startup."

"ERROR: Exception text: For one or more plug-in requests, no matching Plug-ins were found in

Repository"

Trying to connect via 'libname' statement is also not possible.

Similar error codes.

I searched a lot for it, but haven't found a solution yet.

I think it might be a problem with the license or installation.

Is the module "SAS/ACCESS Interface to Microsoft SQL database" or "to ODBC" mandatory to perform these operations?

OS: Microsoft Windows 7 x64

Output PROC SETINIT:

Current version: 9.04.01M5P091317

Product expiration dates:

---Base SAS Software

---SAS/STAT

---SAS/GRAPH

---SAS/Secure 168-bit

---SAS/Secure Windows

---SAS Enterprise Guide

---SAS/ACCESS Interface to DB2

---SAS Workspace Server for Local Access

---High Performance Suite

Output PROC PRODUCT_STATUS

For Base SAS Software ...

Custom version information: 9.4_M5

Image version information: 9.04.01M5P090617

For SAS/STAT ...

Custom version information: 14.3

For SAS/GRAPH ...

Custom version information: 9.4_M5

For High Performance Suite ...

Custom version information: 2.2_M6

For SAS/ACCESS Interface to DB2 ...

Custom version information: 9.4_M2

Thanks and best Regards

Re: SAS Microsoft SQL connection issue

kiranv_ — Wed, 20 Dec 2017 14:30:21 GMT

yes it is. Below is short and sweet article which explains role of SAS/Access and help you think through the issue.

https://www.sas.com/content/dam/SAS/en_us/doc/factsheet/sas-access-103392.pdf

Re: SAS Microsoft SQL connection issue

JBailey — Wed, 15 Aug 2018 18:44:24 GMT

Hi @BNT1

The error message indicates that that you are trying to connect to Microsoft SQL Server via PROC SQL. This requires that one of the following SAS/ACCESS products be installed and configured:

SAS/ACCESS Interface to Microsoft SQL Server
SAS/ACCESS Interface to ODBC
SAS/ACCESS Interface to OLE DB
SAS/ACCESS Interface to PC Files (hard to believe but true)

The best choices for SQL Server would be either the ODBC or MS SQL Server SAS/ACCESS products.

It appears that you have SAS/ACCESS Interface to DB2 licensed. This product enables your SAS environment to connect to IBM DB2 servers.

For more information:

An Insider's Guide to SAS/ACCESS Interface to ODBC

An Insider's Guide to Fine-Tuning Your CREATE TABLE Statements Using SAS Option - Not as useful as the previous paper

SAS 9.4 and SAS Viya 3.4 Programming Documentation / SAS /ACCESS for Relational Databases

Hope this helps (somebody),

@JBailey