[R] Connecting to Empress DB using RODBC

[Marc Schwartz]

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