topic Re: Connect SAS to SQL without SAS/Access in SAS Procedures

Connect SAS to SQL without SAS/Access

VSht — Fri, 10 Jul 2020 04:48:48 GMT

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.

Re: Connect SAS to SQL without SAS/Access

ketpt42 — Fri, 10 Jul 2020 05:07:13 GMT

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

Re: Connect SAS to SQL without SAS/Access

VSht — Fri, 10 Jul 2020 05:28:51 GMT

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

Re: Connect SAS to SQL without SAS/Access

ketpt42 — Fri, 10 Jul 2020 05:42:34 GMT

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?

Re: Connect SAS to SQL without SAS/Access

VSht — Fri, 10 Jul 2020 05:45:55 GMT

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

Re: Connect SAS to SQL without SAS/Access

ChrisHemedinger — Fri, 10 Jul 2020 11:45:52 GMT

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.

Re: Connect SAS to SQL without SAS/Access

SASKiwi — Sun, 12 Jul 2020 21:20:33 GMT

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-to/m-p/252265

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.

Re: Connect SAS to SQL without SAS/Access

VSht — Tue, 14 Jul 2020 05:04:45 GMT

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.