02-27-2014 11:39 PM
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.
04-17-2014 10:16 AM
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
I was able to reproduce the error message you received by omitting the SERVER=option in the above LIBNAME statement.
04-22-2014 11:34 AM
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.
04-17-2014 11:08 AM
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.