Data.table with one POSIXct column to save to SQL Server using RODBC::sqlSave. When saving with just this column and one other column, specifying varTypes=c(lastrun='datetime') this saves fine. But if I include an additional column we get SQL Server error:
An invalid tabular data stream (TDS) collation was encountered.
Where do I need to look to overcome this block?
See reprex below:
library(RODBC)
library(data.table)
c_sr <- data.table(ID = as.integer(c(1,2)),
lastrun = as.POSIXct(c('2022-10-21 10:11:13', '2022-10-21 11:10:09')),
runby=c(NA_character_, 'ABC'),
currentrun = c('N','Y'))
c_chan <- RODBC::odbcConnect('xopt_process')
#First example, no datetime column - SUCCESS
RODBC::sqlSave(c_chan, c_sr[,.(ID, runby)], 'tmp01', rownames = FALSE, verbose=TRUE)
#> Query: CREATE TABLE "tmp01" ("ID" int, "runby" varchar(255))
#> Query: INSERT INTO "tmp01" ( "ID", "runby" ) VALUES ( ?,? )
#> Binding: 'ID' DataType 4, ColSize 10
#> Binding: 'runby' DataType 12, ColSize 255
#> Parameters:
#> no: 1: ID 1/***/no: 2: runby NA/***/
#> no: 1: ID 2/***/no: 2: runby ABC/***/
#Second example, ID + datetime column - SUCCESS
RODBC::sqlSave(c_chan, c_sr[,.(ID, lastrun)], 'tmp02', varTypes=c(lastrun='datetime'), rownames = FALSE, verbose=TRUE)
#> Query: CREATE TABLE "tmp02" ("ID" int, "lastrun" datetime)
#> Query: INSERT INTO "tmp02" ( "ID", "lastrun" ) VALUES ( ?,? )
#> Binding: 'ID' DataType 4, ColSize 10
#> Binding: 'lastrun' DataType 93, ColSize 23
#> Parameters:
#> no: 1: ID 1/***/no: 2: lastrun 2022-10-21 10:11:13/***/
#> no: 1: ID 2/***/no: 2: lastrun 2022-10-21 11:10:09/***/
#Third example, ID + datetime column + additional column - FAIL
RODBC::sqlSave(c_chan, c_sr[,.(ID, lastrun, currentrun)], 'tmp03', varTypes=c(lastrun='datetime'), rownames = FALSE, verbose=TRUE)
#> Query: CREATE TABLE "tmp03" ("ID" int, "lastrun" datetime, "currentrun" varchar(255))
#> Query: INSERT INTO "tmp03" ( "ID", "lastrun", "currentrun" ) VALUES ( ?,?,? )
#> Binding: 'ID' DataType 4, ColSize 10
#> Binding: 'lastrun' DataType 93, ColSize 23
#> Binding: 'currentrun' DataType 12, ColSize 255
