R help - Jul 2011 - Connecting to Empress DB using RODBC

Hi there,
I am using the RODBC library to connect to an Empress database.
I have installed the ODBC data source with the server DNs number and port, and
named the source "Trawl".
It is the odbcDriverConnect that seems to have the problem, and I suspect one of
the settings in my Data Source is wrong, or that my syntax to identify the
database is wrong. I have not set CodeSet or ODBC Version.
 
Here are the error messages.
1: In odbcDriverConnect("Trawl") :
  [RODBC] ERROR: state 08001, code -256, message [Empress Software][ODBC
DLL]Unable to connect to data source
2: In odbcDriverConnect("Trawl") :
  [RODBC] ERROR: state 01S00, code 0, message [Microsoft][ODBC Driver Manager]
Invalid connection string attribute
3: In odbcDriverConnect("Trawl") : ODBC connection failed
Can anyone point me in the right direction? Is there a specific syntax for
naming the database other than its name? It does bring up a GUI where I can
choose my data source and login, but then it just gives the errors above.
 
Any help is greatly appreciated.
Steve
 

Please consider the environment before printing this email.

NIWA is the trading name of the National Institute of Water & Atmospheric
Research Ltd.

	[[alternative HTML version deleted]]

On Jul 11, 2011, at 9:16 PM, Steve Parker wrote:
> Hi there,
> I am using the RODBC library to connect to an Empress database.
> I have installed the ODBC data source with the server DNs number and port,
and named the source "Trawl".
> It is the odbcDriverConnect that seems to have the problem, and I suspect
one of the settings in my Data Source is wrong, or that my syntax to identify
the database is wrong. I have not set CodeSet or ODBC Version.
> 
> Here are the error messages.
> 1: In odbcDriverConnect("Trawl") :
>  [RODBC] ERROR: state 08001, code -256, message [Empress Software][ODBC
DLL]Unable to connect to data source
> 2: In odbcDriverConnect("Trawl") :
>  [RODBC] ERROR: state 01S00, code 0, message [Microsoft][ODBC Driver
Manager] Invalid connection string attribute
> 3: In odbcDriverConnect("Trawl") : ODBC connection failed
> Can anyone point me in the right direction? Is there a specific syntax for
naming the database other than its name? It does bring up a GUI where I can
choose my data source and login, but then it just gives the errors above.
> 
> Any help is greatly appreciated.
> Steve

I would repost your query to r-sig-db:

  https://stat.ethz.ch/pipermail/r-sig-db/

but also include information on your OS (presumably some version of Windows),
the version of R and the version of RODBC, being sure that you are running the
latest of each (R 2.13.1 and RODBC 1.3-2).

Also include the actual function calls you are making along with the error
messages, being sure to mask your userID and password where included. We
can't tell you if your syntax is wrong if you don't include it. The
above errors could be your syntax or perhaps an ODBC configuration error

See vignette("RODBC") for general information on creating a proper
Windows DSN for your database.

If Empress has any kind of ODBC client or if you can use something like Excel or
MS Query to connect via ODBC, you can test your connection to the database
independent of R. That will help assess if your problem is your basic ODBC
configuration or if there is something specific to R/RODBC such as your syntax
or perhaps a driver issue.

HTH,

Marc Schwartz