Re: [R] Error in sqlCopy in RODBC

From: BKMooney <bkmooney_at_gmail.com>
Date: Wed, 26 Nov 2008 10:37:40 -0800 (PST)

I tried your suggestion...

library(RODBC)
channel = odbcConnectAccess("db.mdb")
sqlCopy(channel,"Select * from tab","newtab",destchannel=channel,   safer=TRUE,append=TRUE,rownames=FALSE,fast=FALSE) odbcClose(channel)

however, I am still running into errors, both when appending to an existing table, or creating a table if the destination table does not exist.

The code I am using is:

query <- " select * from tblHistorical where MyDate between '2008-11-21' and '2008-11-25' ; "
sqlCopy(RemoteChannel, query, "NewTable", destchannel=LocalChannel, safer=TRUE, append=TRUE, rownames=FALSE, fast=FALSE)

If I run this when "NewTable" does not yet exist in the database, it returns the error:
ERROR: Could not SQLExecDirect 42000 102 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '16'.

And when this runs when "NewTable" is already defined with correct data types, I get the error:
Error in sqlSave(destchannel, dataset, destination, verbose = verbose, : unable to append to table ‘NewTable’

It seems I can't get it to work in either scenario.

Any insight anyone could provide would be greatly appreciated.

Dieter Menne wrote:
>
>
>
> BKMooney wrote:

>> 
>> I am trying to copy portions of tables from one SQL database to another,
>> using sqlCopy in the RODBC package.
>> 
>> ...
>> I am currently getting an error:
>> Error in sqlSave(destchannel, dataset, destination, verbose = verbose,  :
>>   table 'LocalTable' already exists
>> 

>
> I can reproduce your error with my example file and fast=TRUE. You might
> try fast=FALSE
> and append=TRUE when things like this happens. The following works for me
>
> library(RODBC)
> channel = odbcConnectAccess("db.mdb")
> sqlCopy(channel,"Select * from tab","newtab",destchannel=channel,
> safer=TRUE,append=TRUE,rownames=FALSE,fast=FALSE)
> odbcClose(channel)
>
>
>
>
-- 
View this message in context: http://www.nabble.com/Error-in-sqlCopy-in-RODBC-tp20691929p20706667.html
Sent from the R help mailing list archive at Nabble.com.

______________________________________________
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.
Received on Wed 26 Nov 2008 - 18:39:42 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Thu 27 Nov 2008 - 09:30:29 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.

list of date sections of archive