使用RODBC::sqlSave保存含POSIXct列的data.table至SQL Server遇TDS排序规则错误

使用RODBC包的sqlSave函数将包含POSIXct类型列的data.table保存到SQL Server时，仅保留POSIXct列与另一列并指定varTypes=c(lastrun='datetime')可成功保存，但添加额外列后触发SQL Server错误："遇到无效的表格数据流(TDS)排序规则"。可复现问题的代码如下：

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
#> Parameters:
#> no: 1: ID 1/***/no: 2: lastrun 2022-10-21 10:11:13/***/no: 3: currentrun N/***/
#> Error in RODBC::sqlSave(c_chan, c_sr[, .(ID, lastrun, currentrun)], "tmp03", : [RODBC] Failed exec in Update
#> 42000 4012 [Microsoft][SQL Server Native Client 11.0][SQL Server]An invalid tabular data stream (TDS) collation was encountered.

#Fourth example, all except datetime column - SUCCESS
RODBC::sqlSave(c_chan, c_sr[,.(ID, runby, currentrun)], 'tmp04', rownames = FALSE, verbose=TRUE)
#> Query: CREATE TABLE "tmp04"  ("ID" int, "runby" varchar(255), "currentrun" varchar(255))
#> Query: INSERT INTO "tmp04" ( "ID", "runby", "currentrun" ) VALUES ( ?,?,? )
#> Binding: 'ID' DataType 4, ColSize 10
#> Binding: 'runby' DataType 12, ColSize 255
#> Binding: 'currentrun' DataType 12, ColSize 255
#> Parameters:
#> no: 1: ID 1/***/no: 2: runby NA/***/no: 3: currentrun N/***/
#> no: 1: ID 2/***/no: 2: runby ABC/***/no: 3: currentrun Y/***/

RODBC::sqlDrop(c_chan, 'tmp04')
RODBC::sqlDrop(c_chan, 'tmp03')
RODBC::sqlDrop(c_chan, 'tmp02')
RODBC::sqlDrop(c_chan, 'tmp01')
RODBC::odbcClose(c_chan)

1. 调整列顺序，将datetime列放在最后 #

RODBC在处理混合类型列时，若datetime列不是最后一列，易出现TDS协议的类型绑定冲突。调整列顺序后再保存：

# 调整列顺序，将lastrun移至末尾
c_sr_adjusted <- c_sr[,.(ID, currentrun, lastrun)]
RODBC::sqlSave(c_chan, c_sr_adjusted, 'tmp03', varTypes=c(lastrun='datetime'), rownames = FALSE, verbose=TRUE)

2. 将POSIXct列转换为字符型后再指定SQL类型 #

把POSIXct格式转为SQL Server兼容的字符串格式（YYYY-MM-DD HH:MM:SS），再通过varTypes指定为datetime：

c_sr_converted <- copy(c_sr)
c_sr_converted[, lastrun := format(lastrun, "%Y-%m-%d %H:%M:%S")]
RODBC::sqlSave(c_chan, c_sr_converted[,.(ID, lastrun, currentrun)], 'tmp03', varTypes=c(lastrun='datetime'), rownames = FALSE, verbose=TRUE)

3. 替换为odbc包（推荐） #

RODBC是较旧的包，odbc包对SQL Server的兼容性更好，处理日期类型和混合列更稳定：

library(odbc)
library(data.table)

# 建立连接（需根据实际环境修改参数）
con <- dbConnect(odbc::odbc(), 
                 Driver = "SQL Server", 
                 Server = "你的服务器地址", 
                 Database = "目标数据库名", 
                 UID = "用户名", 
                 PWD = "密码")

# 写入数据
dbWriteTable(con, "tmp03", c_sr[,.(ID, lastrun, currentrun)], overwrite = TRUE)

# 关闭连接
dbDisconnect(con)

原因分析 #

该错误源于RODBC在绑定参数时，datetime类型与后续字符类型的TDS数据流排序规则不兼容。当datetime列不是最后一列时，RODBC的参数绑定逻辑易出现混乱，引发SQL Server的TDS解析错误。

内容的提问来源于stack exchange，提问作者sch56