Connect SAS to SQL without SAS/Access

[VSht]

Hi,

I was able to connect SAS and SQL Server in the past without SAS/ACCESS to ODBC, but cannot reproduce it now.

One piece of code that I've found:

libname mylib pcfiles server=localhost port=9621 dsn=MH user=UserID password=MyPassword ; run;

The error message that I get:

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified ERROR: Error in the LIBNAME statement.

We have a licence to Enterprise Guide and SAS/ACCESS Interface to PC Files (not to ODBC).

I would greatly appreciate any help with this.

[ketpt42]

Do you have an ODBC DSN named MH in your ODBC Data Sources widget?

[VSht]

Yes, we defined an odbc connection on my local machine called MH

[ketpt42]

I don't have a SQL Server DSN setup on my machine, but I was able to get this to work on a Teradata DSN. Did you create the DSN in the 32 or 64 bit ODBC Administrator? And does that match with the bitness of your SAS/ACCESS for PC Files version?

[VSht]

Thank you. This is an interesting question. I have not thought about it. I will have to ask our Administrator on Monday

[ChrisHemedinger]

Interesting -- I didn't know about this little loophole to connect via ODBC.

Make sure that your ODBC connection is defined in the same bit architecture as your SAS session.  So, 64-bit SAS? You need a 64-bit ODBC driver and data source defined.

[SASKiwi · Accepted solution]

There are a few old posts about this trick including this one: https://communities.sas.com/t5/SAS-Analytics-U/SQL-Server-ODBC-DSN-Connection-Error-CLI-error-trying...

You will need to ensure the SAS PC Files Server is running and that the ODBC DSN is defined in the Windows ODBC Administrator on the computer the SAS PC Files Server is running on. 

[VSht]

Just in case if it helps someone in the future, the error persisted until we set up the connection as System DSN using Administrator rights.