topic Re: ODBC connection error to mysqlworkbench through BaseSAS 9.4 in Administration and Deployment

ODBC connection error to mysqlworkbench through BaseSAS 9.4

Siva_Harish — Thu, 03 Dec 2020 07:47:35 GMT

Hello community,

Iam getting below error when I was trying to connect to odbc server .

Can anyone help me to fix this?

Thanks in advance!!

Re: ODBC connection error to mysqlworkbench through BaseSAS 9.4

DavidHD — Thu, 03 Dec 2020 07:52:56 GMT

Hey @Siva_Harish ,

You need SAS/ACCESS to ODBC with the correct SQL Server ODBC driver installed on the same server.

Kind Regards,

David

Re: ODBC connection error to mysqlworkbench through BaseSAS 9.4

Kurt_Bremser — Thu, 03 Dec 2020 08:20:40 GMT

Please post logs by copy/pasting the text into a window opened with the </> button. They are much easier to read and we can insert comments to point to mistakes.

Similarly, use the "little running man" button right next to it for posting SAS code.

Re: ODBC connection error to mysqlworkbench through BaseSAS 9.4

Siva_Harish — Thu, 03 Dec 2020 09:17:19 GMT

I want to configure My SQL odbc 5.3 driver 32 through odbc data source administrator(32-bit) .However we are not able to get in my system(above image for your reference)

Any thoughts !!

Re: ODBC connection error to mysqlworkbench through BaseSAS 9.4

Siva_Harish — Thu, 03 Dec 2020 09:21:39 GMT

proc sql threads noprint;
connect to ODBC (dsn=&DSN. user=&USER. password=&PWD.);
execute (delete from legal.m_f_tymetrix_lineitem_test 
where cast(date_format(Invoice_Processing_Date, '%Y%m') as signed) in (&mon_yrt.)) by odbc;
execute (commit ) by odbc;
disconnect from odbc;
quit;

When we ran the sas code we got below error

Re: ODBC connection error to mysqlworkbench through BaseSAS 9.4

DavidHD — Thu, 03 Dec 2020 09:28:54 GMT

What do you mean by "However we are not able to get in my system"? If the drive is not installed on the system that your SAS installation is running on then SAS won't be able to connect to your data source.

Re: ODBC connection error to mysqlworkbench through BaseSAS 9.4

Kurt_Bremser — Thu, 03 Dec 2020 09:31:20 GMT

The ODBC source must be defined on the host on which SAS runs. As much as I can read from the log picture, the ODBC source is not defined.

Please tell us a little about your SAS setup:

single machine (SAS 9.4 on your desktop, Display Manager or Enterprise Guide with Local server)
client/server, if yes, which operating system on the server

If in doubt, run PROC SETINIT and post the log.

And please post the logs as text, using the </> button, and not as pictures.

Re: ODBC connection error to mysqlworkbench through BaseSAS 9.4

Siva_Harish — Thu, 03 Dec 2020 10:07:32 GMT

Original site validation data
Current version: 9.04.01M3P062415
Expiration:   31DEC2020.
Grace Period:  45 days (ending 14FEB2021).
Warning Period: 45 days (ending 31MAR2021).
System birthday:   28FEB2020.
Operating System:   W32_WKS .
Product expiration dates:
---Base SAS Software
  31DEC2020
---SAS/STAT
  31DEC2020
---SAS/GRAPH
  31DEC2020
---SAS/ETS
  31DEC2020
---SAS/FSP
  31DEC2020
---SAS/OR
  31DEC2020
---SAS/AF
  31DEC2020
---SAS/IML
  31DEC2020
---SAS/QC
  31DEC2020
---SAS/SHARE
  31DEC2020
---SAS/CONNECT
  31DEC2020
---SAS/EIS
  31DEC2020
---SAS/SHARE*NET
  31DEC2020
---MDDB Server common products
  31DEC2020
---SAS/Secure 168-bit
  31DEC2020
---SAS/Secure Windows
  31DEC2020
---SAS Enterprise Guide
  31DEC2020
---OR OPT
  31DEC2020
---OR PRS
  31DEC2020
---OR IVS
  31DEC2020
---OR LSO
  31DEC2020
---SAS/ACCESS Interface to DB2
  31DEC2020
---SAS/ACCESS Interface to Oracle
  31DEC2020
---SAS/ACCESS Interface to PC Files
  31DEC2020
---SAS/ACCESS Interface to ODBC
  31DEC2020
---SAS/IML Studio
  31DEC2020
---SAS Workspace Server for Local Access
  31DEC2020
---SAS/ACCESS Interface to Netezza
  31DEC2020
---High Performance Suite
  31DEC2020

Re: ODBC connection error to mysqlworkbench through BaseSAS 9.4

Kurt_Bremser — Thu, 03 Dec 2020 10:16:25 GMT

So you have a single workstation installarion and license with all the necessary parts. You need to correctly define the ODBC source (DSN) in Windows, so you can then use it from SAS.