r - 无法找到函数 ‘dbWriteTable’ 的继承方法，用于签名 ‘"RODBC", "character", "data.frame"'

Question

我正在尝试通过 api 调用提取数据另存为 r dtaframe，然后将 r dataframe 复制到 MS Sql 服务器。 api 调用和与 Sql 服务器的连接完美无缺。但是，每当我尝试将 r 数据帧复制到 sql server 时，我都会收到一条错误消息。我已经尝试了我找到的所有选项，但没有任何效果。感谢您提供任何帮助。

library(RSocrata)
REData <- read.socrata("https://data.ny.gov/resource/i8hd-gucs.json", app_token = token, email = "email", 
                   password = password,
                   stringsAsFactors = FALSE)


DBI::dbWriteTable(con, name = "tblDosActiveRealEstateSalesAgent", 
data.frame(REData), field.types = NULL, row.names = FALSE, append = TRUE, 
              allow.keywords = FALSE)


 Error in (function (classes, fdef, mtable)  : 
 unable to find an inherited method for function ‘dbWriteTable’ for signature ‘"RODBC", "character", "data.frame"’

require(RODBC)
con <- odbcDriverConnect("Driver=ODBC Driver 13 for SQL Server;server=172.xx.xx.xx,1433;Database=ReportServer;uid=sa;Pwd=password")

类(class)信息:

R version 3.4.3 (2017-11-30)
Platform: i386-w64-mingw32/i386 (32-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1

Matrix products: default

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United States.1252    LC_MONETARY=English_United States.1252 LC_NUMERIC=C                          
[5] LC_TIME=English_United States.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] RSQLite_2.1.0    jsonlite_1.5     RSocrata_1.7.4-7 odbc_1.1.5       RODBC_1.3-15    

loaded via a namespace (and not attached):
 [1] Rcpp_0.12.16    digest_0.6.13   mime_0.5        plyr_1.8.4      R6_2.2.2        DBI_0.8         httr_1.3.1      rlang_0.2.0     curl_3.2       
[10] blob_1.1.1      tools_3.4.3     bit64_0.9-7     bit_1.1-12      hms_0.4.2       yaml_2.1.18     compiler_3.4.3  pkgconfig_2.0.1 memoise_1.1.0

Answer 1

如果您因为尝试将 MS SQL 与 R 和 DBI 结合使用而走到这里，这可能会有所帮助:

对我来说问题是 DBI 的方法在对象标识符周围添加了引号。将 QUOTED_IDENTIFIER 设置为 ON 解决了​​这个问题。

dbExecute(myConnection, "设置 quoted_identifier")