Re: [R] Error in sqlCopy in RODBC
The exact syntax I am running in R is as follows: 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) Both RemoteChannel and LocalChannel seem to be functioning as expected for getting data into R via the sqlQuery function. It is writing data that is giving me problems, as I am running into the same error with both the sqlSave and sqlCopy functions. There are two scenarios - when NewTable does not exist in the local database, and when it does. If NewTable does not exist, then I get the error: Error in sqlSave(destchannel, dataset, destination, verbose = verbose, : unable to append to table ‘NewTable’ If NewTable exists, then I get the error: Error in sqlSave(destchannel, dataset, destination, verbose = verbose, : [RODBC] ERROR: Could not SQLExecDirect 42000 102 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '16'. My select statement functions exactly as I would expect it to when I run it as a query both in the SQL Management studio, or if I run it as a query using sqlQuery, so I am fairly confident any syntax problem is not with that query. If anyone has any ideas or suggestions, I would be very appreciative. -- View this message in context: http://www.nabble.com/Error-in-sqlCopy-in-RODBC-tp20691929p20774378.html Sent from the R help mailing list archive at Nabble.com. __ R-help@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.
Re: [R] Error in sqlCopy in RODBC
BKMooney wrote: 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) This is confusing: did you get an error trying my code, or did you get an error with your code? RODBC is very context dependent, and chances are close to zero that you get a useful reply when you do not provide an example or even not run the example provided in your environment. Did you try the select statement directly in a query? '2008-11-25' or #2008-11-25#? Dieter -- View this message in context: http://www.nabble.com/Error-in-sqlCopy-in-RODBC-tp20691929p20715135.html Sent from the R help mailing list archive at Nabble.com. __ R-help@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.
Re: [R] Error in sqlCopy in RODBC
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-tp20691929p20697101.html Sent from the R help mailing list archive at Nabble.com. __ R-help@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.
Re: [R] Error in sqlCopy in RODBC
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@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.
[R] Error in sqlCopy in RODBC
Hi All, I am trying to copy portions of tables from one SQL database to another, using sqlCopy in the RODBC package. RemoteChannel = connection to remote database LocalChannel = connection to local database LocalTable = table in my local database to receive data from the remote database query - select query in SQL sqlCopy(RemoteChannel, query, LocalTable, destchannel=LocalChannel, safer=TRUE) I am currently getting an error: Error in sqlSave(destchannel, dataset, destination, verbose = verbose, : table 'LocalTable' already exists I need to append the data retreieved through the query, to the table LocalTable. It was my understanding that when safer=TRUE, it would append the new data to an existing table, or create a new table otherwise. This error seems to suggest otherwise. Any ideas? All your help is greatly appreciated! [[alternative HTML version deleted]] __ R-help@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.
