R help - Jan 2007 - RODBC: sqlQuery is successful, but a similar sqlFetch returns error

Greetings guRus -- 

 

I have successfully queried a large (24,445 rows by 281 cols.) in-house
database using the following RODBC query (without the line breaks)

 

testout <- sqlQuery(channel, "select idSchedule,EXCL_Total from
dbo.vwC1198_2006_RawData_With_CMPL_EXCL")

 

This returns a dataframe of 24445 rows and two columns (as intended),
but the following command

 

testout <-
sqlFetch(channel,"dbo.vwC1198_2006_RawData_With_CMPL_EXCL",colnames
TRUE,rownames = "idSchedule")

 

returns the error message 

 

in odbcTableExists(channel, sqtable) : 

        'dbo.vwC1198_2006_RawData_With_CMPL_EXCL': table not found on
channel

 

The value of channel did not change between the two commands.

 

Based on the help files for sqlFetch and sqlQuery, it is not clear to me
why one command would find the table and the other would not.  I am
running R 2.4.1 on a Windows XP pro machine with 2 gig of memory.

 

Thanks for any suggestions or hints,

 

Ben Fairbank

 


	[[alternative HTML version deleted]]

'.' is invalid in an SQL table name.  I don't know what driver you
are
using (you seem very reluctant to tell us anything useful about your 
DBMS), but dbo.vwC1198_2006_RawData_With_CMPL_EXCL probably means table 
vwC1198_2006_RawData_With_CMPL_EXCL in database dbo, and that is valid as 
part of SQL, but not in your usage in sqlFetch.

On Tue, 16 Jan 2007, Ben Fairbank wrote:
> Greetings guRus --
>
>
>
> I have successfully queried a large (24,445 rows by 281 cols.) in-house
> database using the following RODBC query (without the line breaks)
>
>
>
> testout <- sqlQuery(channel, "select idSchedule,EXCL_Total from
> dbo.vwC1198_2006_RawData_With_CMPL_EXCL")
>
>
>
> This returns a dataframe of 24445 rows and two columns (as intended),
> but the following command
>
>
>
> testout <-
>
sqlFetch(channel,"dbo.vwC1198_2006_RawData_With_CMPL_EXCL",colnames
> TRUE,rownames = "idSchedule")
>
>
>
> returns the error message
>
>
>
> in odbcTableExists(channel, sqtable) :
>
>        'dbo.vwC1198_2006_RawData_With_CMPL_EXCL': table not found
on
> channel
>
>
>
> The value of channel did not change between the two commands.
>
>
>
> Based on the help files for sqlFetch and sqlQuery, it is not clear to me
> why one command would find the table and the other would not.  I am
> running R 2.4.1 on a Windows XP pro machine with 2 gig of memory.
>
>
>
> Thanks for any suggestions or hints,
>
>
>
> Ben Fairbank
>
>
>
>
> 	[[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at stat.math.ethz.ch 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