I am writing to from an ODBC to a SQL Server table via the RODBC
package, specifically the function sqlSave
. It seems that the default var types is charvar(255)
for this function. I tried to use the argument of varTypes that is listed within the documentation but it fails.
Here is the table called spikes20
with the Class structure, this in turn is what I am trying to save via sqlSave
sapply(spikes20, class)
Date Day EWW PBR BAC CHTP FB SPY
"Date" "factor" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
Here is the code which attempts to write to the SQL Server
require(RODBC)
varTypes = c(as.Date="Date")
channel <-odbcConnect("OptionsAnalytics", uid="me", pwd="you")
sqlSave (channel, spikes20, tablename = NULL, append=TRUE, rownames = FALSE, colnames = TRUE, safer = FALSE, addPK = FALSE, varTypes=varTypes )
The error message that I get says:
Warning messages:
In sqlSave(channel, spikes20, tablename = NULL, append = TRUE, rownames = FALSE, : column(s) as.Date 'dat' are not in the names of 'varTypes'
I tried to change the varType to:
varTypes=c(Date="Date")
then the error message becomes:
Error in sqlSave(channel, spikes20, tablename = NULL, append = TRUE, rownames = FALSE, :
[RODBC] Failed exec in Update
22007 241 [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.
Any help will be appreciated. It seems I cannot decipher to use varTypes correctly...
First, are you really trying to append to a table named NULL?
As far as issues with varTypes goes, in my experience I have had to provide a mapping for all of the variables in the data frame even though the documentation for the varTypes argurment says:
"an optional named character vector giving the DBMSs datatypes to be used for some (or all) of the columns if a table is to be created"
You need to make sure that the names of your varTypes vector are the column names and the values are the data types as recommended here. So following their example you would have:
tmp <- sqlColumns(channel, correctTableName)
varTypes = as.character(tmp$TYPE_NAME)
names(varTypes) = as.character(tmp$COLUMN_NAME)
varTypes = c(somecolumn
="datetime") works for me.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With