SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

odbc error opening connection

Reply
Occasional Contributor
Posts: 11

odbc error opening connection

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.

SAS Employee
Posts: 215

Re: odbc error opening connection

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?

SAS Employee
Posts: 215

Re: odbc error opening connection

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.

SAS Employee
Posts: 215

Re: odbc error opening connection

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.

Trusted Advisor
Posts: 3,215

Re: odbc error opening connection

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 --<-----
Ask a Question
Discussion stats
  • 4 replies
  • 953 views
  • 0 likes
  • 3 in conversation