topic Re: getting missing ODBC driver error when attempting SQL passthrough in Administration and Deployment

getting missing ODBC driver error when attempting SQL passthrough

quickbluefish — Wed, 09 Jul 2025 13:51:25 GMT

Not totally sure this should be under admin or not. At my old job, I used SQL passthrough quite a lot for pushing operations to MS SQL Server without issue, but at my new job, I am a bit of an outcast for using SAS and local IT does not seem interested in digging into this issue. The attempted connection is now to AWS Redshift, and I'm using syntax like:

proc sql;
connect to redshift (user=XXXX password=XXXX
    server=XXXX port=XXXX database=XXXX);

* .... ;

disconnect from redshift;
quit;

...and I'm getting the error:

ERROR: CLI error trying to establish connection: [DataDirect] [ODBC lib] Specified driver could not be loaded.

What I'd like to know is - is the solution to this something that the sys admin needs to resolve, or is it something I'm doing / not doing. Any help is greatly appreciated!

Re: getting missing ODBC driver error when attempting SQL passthrough

SASKiwi — Wed, 09 Jul 2025 22:52:32 GMT

The error you are getting suggests to me that the SAS/ACCESS Interface to Redshift has not been correctly configured. Do you know of anyone where you work who is using SAS with Redshift or are you the first to try? It would also be work confirming that SAS/ACCESS Interface to Redshift is installed and licensed although it most likely is based on the error message:

proc product_status;
run;

proc setinit;
run;

Re: getting missing ODBC driver error when attempting SQL passthrough

quickbluefish — Wed, 09 Jul 2025 23:06:07 GMT

@SASKiwi - thank you! I will try checking the status as you suggested. Others (a small minority, at least) do use SAS, but I suspect everyone just uses the libref interface to Redshift rather than using passthrough - I'm searching for someone, though. The former (libref method) does work OK - it's just not efficient for big tasks and obviously doesn't allow me to use native SQL.

Re: getting missing ODBC driver error when attempting SQL passthrough

Patrick — Wed, 09 Jul 2025 23:46:20 GMT

@quickbluefish If you've got a working libname/libref to access Redshift then you can use such a libref also in your connect statement.

Try:

libname rlref redshift .....;

proc sql;
connect using rlref;

* .... ;

disconnect from rlref;
quit;

Re: getting missing ODBC driver error when attempting SQL passthrough

quickbluefish — Thu, 10 Jul 2025 00:51:53 GMT

THIS IS AMAZING. I spent hours trying to get this to work and another hour on the phone with tech support to no avail - this solution works perfectly! Thank you!!