BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
HugoVini
Obsidian | Level 7

I've installed MySQL connector and added the connection on ODBC Data Source Administrator, I did as well the test: "connection successful".

 

So I went to import data on data preparation -> Server -> ODBC. After filled the fields I tried to find tables but was sended to me that message:

 

No one table was returned from the library selected


NOTE: This session is executing on the X64_SR12R2 platform.

 

NOTE: Additional host information:

X64_SR12R2 WIN 6.3.9600 Server

NOTE: SAS Initialization used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

NOTE: The autoexec file, C:\SAS\Config\Lev1\SASApp\WorkspaceServer\autoexec.sas, was executed at server initialization.
1
2 The SAS System 12:08 Tuesday, April 16, 2019


1 The SAS System 12:08 Tuesday, April 16, 2019

1
2
/* *********************************************** */
/* Assign the libref for the data in a ODBC table */
/*
2 ! *********************************************** */


LIBNAME db_odbc ODBC PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES
2 ! DATAsrc="DataBase" USER=adm PASSWORD="{sas002}341F943245F55BDC2B048AB10EEBE51E" ;


NOTE: Libref DB_ODBC was successfully assigned as follows:
Engine: ODBC
Physical Name: DataBase
3 options VALIDVARNAME=ANY VALIDMEMNAME=EXTEND;

 

1 ACCEPTED SOLUTION

Accepted Solutions
HugoVini
Obsidian | Level 7

@Creeback @alexal 

 

I found the problem, there is no SAS odbc driver available, so I cannot connect in no one data base.

 

Thanks for the help

View solution in original post

16 REPLIES 16
Creeback
Fluorite | Level 6

The tables/views contains 32 characters or more? The adm user can access in objects in MYSQL?

HugoVini
Obsidian | Level 7

The table doesn't contains more than 32 characters.

 

I didnt understand that question: The adm user can access in objects in MYSQL?

 

I'm using ODBC with mysql driver connector, is it correct? Or should I use SAS connector to be possible acess my data base through the SAS Visual Analytics?

alexal
SAS Employee

@HugoVini ,


Please run the following program and let me know if you will get anything in the output from proc contents:

 

LIBNAME db_odbc ODBC PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES DATAsrc="DataBase" USER=adm PASSWORD="{sas002}341F943245F55BDC2B048AB10EEBE51E" ;
proc contents data=db_odbc._all_ nods; run;
HugoVini
Obsidian | Level 7

The query ran successfully but generated warnings. See the Messages tab for more details.

 

WARNING: No matching members in directory.

 

That was the answer.

 

Do you have any tutorial, explaining clearly how I access a data base from SAS VA data preparation? Maybe I did something wrong during my process.

alexal
SAS Employee

@HugoVini ,


Do you know what is weird? I do not see the SCHEMA= option in your LIBNAME statement. Do you know your database schema name? If so, add it to the LIBNAME statement and try again.

HugoVini
Obsidian | Level 7


LIBNAME db_odbc ODBC PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES SCHEMA="bi_teste" DATAsrc="DataBase" USER=adm PASSWORD="{sas002}341F943245F55BDC2B048AB10EEBE51E" ;
proc contents data=db_odbc._all_ nods; run;

 

I executed that command and received the same answer I told you before

alexal
SAS Employee

@HugoVini ,

 

Either schema isn't correct or you are using the wrong database. Did you try to connect to that database from a command line outside of SAS?

HugoVini
Obsidian | Level 7

Yes I did, I'm using Dbeaver to do some queries, everthing is working well and the schema's name is right

HugoVini
Obsidian | Level 7

@Creeback @alexal 

 

I found the problem, there is no SAS odbc driver available, so I cannot connect in no one data base.

 

Thanks for the help

Creeback
Fluorite | Level 6
Probably the user does not have grant in objects. That's my doubt..
Creeback
Fluorite | Level 6

In this case is MS SQL Server:

 

Data Source (ODBC Name)

 

Window_Data_Preparation.png

Window_Data_Preparation_Objects.png

 

work for me.

 

Pre-requisites:

 

GRANT and objects names less or equal than 32 characters.

HugoVini
Obsidian | Level 7

@Creeback 

 

Show me your ODBC details, I would like to know which driver are you using.

 

odbc.JPG

Creeback
Fluorite | Level 6

I don't have MySQL drivers, only MS SQL Server drivers.ODBC Drivers.png

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 16 replies
  • 2580 views
  • 0 likes
  • 3 in conversation