BookmarkSubscribeRSS Feed
mklybor
Calcite | Level 5

while trying to register tables on a newly defined ODBC connection I get the error:

ERROR: CLI error trying to establish connection: [Vertica][ODBC] (10430) Not enough information provided to establish a connection

       to the data source and specified to not prompt for more information.

ERROR: Error in the LIBNAME statement.

here is the libname:

LIBNAME VGPAMAS VERTICA  DATABASE=GP_AMAS  DSN=Vertica_GP_AMAS  SCHEMA=public  USER=dbadmin

1        ! PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ;

I can connect to this database from the server that is running the sas mid tier using ISQL, so the ODBC setup seems OK.

the ODBC env variables are defined in sasenv_local and the workspace manager was validated

the libname has the correct odbc DSN and user.

what else could be provided that is missing? I've gone through everything I could find in the manuals re: odbc and vertica and can't find what might be missing.

4 REPLIES 4
JBailey
Barite | Level 11

Hi,

Were you able to get this to work? If not, can you share the contents of your DSN and a more complete SAS log?

JBailey
Barite | Level 11

I am not sure how I forgot this, but I wrote an ODBC related paper for SAS Global Forum. You may find it useful. Here is a link: http://support.sas.com/resources/papers/proceedings14/SAS039-2014.pdf

I tried running "%put %superq(sysdbmsg)" after running my LIBNAME statement. It didn't display the connection string that was used by the driver. If you are on UNIX it may work. I am using a Windows environment.

Another trick you can use is to enable ODBC tracing or the SASTRACE= option. Either one of these will show you what is being sent to the database.

options sastrace=',,d,d' sastraceloc=saslog nostsuffix;

I have no way of proving this, but I imagine that there is something missing in your DSN. The SGF paper discusses how to find the DSN definition. You can try a DSNless connection by using a LIBNAME statement similar to this one.

/* If this works, your problem is probably in the DSN */

LIBNAME myvert VERTICA SERVER=mySRV DATABASE=testdb SCHEMA=public

                       USER=myuser PASSWORD=mypasswd;

I was able to reproduce the error message you received by omitting the SERVER=option in the above  LIBNAME statement.

JBailey
Barite | Level 11

Here is an additional thought: If you are using a Library defined in metadata you may want to try displaying the generated LIBNAME statement. It may be an issue with your metadata definition.

jakarman
Barite | Level 11

Registering tables is requiring a running Workspace-server proces able to access the external table.

Some DBA oriented people are setting up the connection getting the external password from the SAS-metadata.

There can be a pitfall with this approach not knowing wich password is coming in.

There can be a lot to review on this.

- Verify you can access the tables from EGuide using the workspace server.

- You can add/try to register those tables using EGuide 5.1 (not SMC)

When there is an error message try to snapshot that.

The last possible circumvention is:

- pre allocate the librarie using the autoexec_usermods

- Define the libarie as SAS-Base type not as Vertica

All options specific to Vertica will be ignore in that case.

When something goes wrong with the SQL code generation that should also be by-passed.

Work than back to determine the point that is causing your issues.

---->-- ja karman --<-----

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 2100 views
  • 0 likes
  • 3 in conversation