topic Re: Libname for ODBC in SAS Programming

Libname for ODBC

SASdevAnneMarie — Fri, 15 Oct 2021 10:58:12 GMT

Hello Experts,

Normally, I have the ODBC license :

The version of my ODBC is :

Unfortunately, my libname doesn't work :


libname mydata odbc complete="driver=Mysql ODBC 10.0 ANSI Driver;
user=xxxxx; pwd=xxxx; database=Ma_base; Server=localhost";

The log is :

Could you help me please ?

Thank you !

Re: Libname for ODBC

AMSAS — Fri, 15 Oct 2021 10:06:03 GMT

When you say it doesn't work, what do you mean?
Are you getting an error message?

Post the SAS log so we can see what happened

Re: Libname for ODBC

SASdevAnneMarie — Fri, 15 Oct 2021 10:58:45 GMT

Thank you, Amsas,

I updated my message.

Re: Libname for ODBC

AMSAS — Fri, 15 Oct 2021 12:28:10 GMT

Throwing the error message into Google Translate, I get "driver summary odbc data source not found and driver name not specific".
Looking at the ODBC engine documentation makes me think something is incorrect in your COMPLETE connection options.

Has this worked in the past?
Has anything changed?
You could try submitting the libname statement without the user, pwd, database and server parameters, you should then get a dialog box requesting the parameters, maybe that would give you a more detailed error or a clue.

The other option is to open a TS track for this and a Subject Matter Expert will help you.

Please include the following:
- SAS Version you are running (the following code will report SAS version information)
%put &sysvlong ;
%put &sysscpl ;
%put &syssite ;
- Include everything you posted here, and the text version of the error (easier to work with)

- Has this worked in the past
- When was it last working

- Has anything changed since the last time it was working

- Can you access other ODBC connections/databases

Re: Libname for ODBC

SASdevAnneMarie — Fri, 15 Oct 2021 13:10:07 GMT

Thank you, AMSAS!
I submit this libname for first time.
What do you mean by "without the user, pwd, database and server parameters"

When I submit this code :
libname mydata odbc complete="driver=Mysql ODBC 10.0 ANSI Driver;
database=Ma_base";
I have the same error on the log, I don't have a dialog box

Re: Libname for ODBC

Reeza — Fri, 15 Oct 2021 14:43:42 GMT

First can you confirm you're running this on a desktop installation with your local ODBC or is this on a SAS Server (ie EG/Studio) system?

Re: Libname for ODBC

SASdevAnneMarie — Fri, 15 Oct 2021 15:10:18 GMT

Thank you for the message, Reeza.
It is on the server.

Re: Libname for ODBC

Reeza — Fri, 15 Oct 2021 15:24:08 GMT

Including your ODBC connection from the screenshot? Or is that your local ODBC?
I think you'll need to work with your IT team.

Re: Libname for ODBC

AMSAS — Fri, 15 Oct 2021 15:38:29 GMT

I was going on what the documentation states:

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

specifies connection options for your data source or database. Separate multiple options with a semicolon. When connection succeeds, the complete connection string is returned in the SYSDBMSG macro variable. If you do not specify enough correct connection options, you are prompted with a dialog box that displays the values from the COMPLETE= connection string. You can edit any field before you connect to the data source. This option is not supported on UNIX platforms. See your ODBC driver documentation for more details.

@SASdevAnneMarie wrote:

Thank you, AMSAS!
I submit this libname for first time.
What do you mean by "without the user, pwd, database and server parameters"

When I submit this code :
libname mydata odbc complete="driver=Mysql ODBC 10.0 ANSI Driver;
database=Ma_base";
I have the same error on the log, I don't have a dialog box

So I was expecting that when you removed some of the ODBC connection options that you'd get prompted with a dialog box

Re: Libname for ODBC

SASdevAnneMarie — Fri, 15 Oct 2021 15:49:14 GMT

It's my local ODBC. EG is on a SAS Server.

Re: Libname for ODBC

Reeza — Fri, 15 Oct 2021 15:52:02 GMT

So EG can't access your ODBC then, your IT team needs to configure your connection for you.
Someone else will correct me if I'm wrong here. You can use EG locally as well but then you won't have access to data or power of the server.

Re: Libname for ODBC

SASKiwi — Fri, 15 Oct 2021 23:29:25 GMT

To be a little more specific the required ODBC driver needs to be installed on the remote SAS application server and this server must also be able to communicate with the database server being connected to. If this is a database running on a PC then this could be tricky.