topic Re: odbc error opening connection in SAS Data Management

odbc error opening connection

mklybor — Fri, 28 Feb 2014 04:39:25 GMT

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.

Re: odbc error opening connection

JBailey — Wed, 16 Apr 2014 20:32:14 GMT

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?

Re: odbc error opening connection

JBailey — Thu, 17 Apr 2014 14:16:30 GMT

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.

Re: odbc error opening connection

jakarman — Thu, 17 Apr 2014 15:08:40 GMT

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.

Re: odbc error opening connection

JBailey — Tue, 22 Apr 2014 15:34:59 GMT

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.