R help - Feb 2008 - SAS ODBC

All,

I'm trying to connect to a remote SAS server using SAS's 9.1 ODBC driver
and the RODBC package. I'm running R-2.6.1 on Win XP. I can successfully
connect to the database, but no matter which table I query, I get back
an empty table with only the column headers. For example:

	> sqlQuery(con, "select * from PRICES.DB_PRICES")

	[1] COMMOD_CURVE START_DATE   PRICING_DATE PRICE       
	<0 rows> (or 0-length row.names)

sqlTables also returns back an empty table:

	> sqlTables(con)

	[1] TABLE_QUALIFIER TABLE_OWNER     TABLE_NAME      TABLE_TYPE
REMARKS        
	<0 rows> (or 0-length row.names)

And sqlFetch doesn't work either:

	> sqlFetch(con, "PRICES.DB_PRICES")

	Error in odbcTableExists(channel, sqtable) : 
	  'PRICES.DB_PRICES': table not found on channel

Are their other options I should be using with RODBC to pull the data? I
have tested the connection in MS Access and can successfully display
data (using File->Get External Data...->ODBC->...)

Any suggestions would be great, thanks in advance for your help.

John Scillieri
>>> This e-mail and any attachments are confidential, may contain
legal,
professional or other privileged information, and are intended solely for the
addressee.  If you are not the intended recipient, do not use the information
in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP2

See the help page for odbcConnect.  I have no experience with that ODBC 
driver, but a few contain errors and need believeNRows = FALSE.

On Tue, 5 Feb 2008, Scillieri, John wrote:
> All,
>
> I'm trying to connect to a remote SAS server using SAS's 9.1 ODBC
driver
> and the RODBC package. I'm running R-2.6.1 on Win XP. I can
successfully
> connect to the database, but no matter which table I query, I get back
> an empty table with only the column headers. For example:
>
> 	> sqlQuery(con, "select * from PRICES.DB_PRICES")
>
> 	[1] COMMOD_CURVE START_DATE   PRICING_DATE PRICE
> 	<0 rows> (or 0-length row.names)
>
> sqlTables also returns back an empty table:
>
> 	> sqlTables(con)
>
> 	[1] TABLE_QUALIFIER TABLE_OWNER     TABLE_NAME      TABLE_TYPE
> REMARKS
> 	<0 rows> (or 0-length row.names)
>
> And sqlFetch doesn't work either:
>
> 	> sqlFetch(con, "PRICES.DB_PRICES")
>
> 	Error in odbcTableExists(channel, sqtable) :
> 	  'PRICES.DB_PRICES': table not found on channel
That won't work: SQL tables cannot have periods in their names.

> Are their other options I should be using with RODBC to pull the data? I
> have tested the connection in MS Access and can successfully display
> data (using File->Get External Data...->ODBC->...)
>
> Any suggestions would be great, thanks in advance for your help.
>
> John Scillieri
>>>> This e-mail and any attachments are confidential, may contain
legal,
> professional or other privileged information, and are intended solely for
the
> addressee.  If you are not the intended recipient, do not use the
information
> in this e-mail in any way, delete this e-mail and notify the sender.
CEG-IP2
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595