dbWriteTable fails for non-default schemas

[KimmoMW]

Following up on issue 91 and https://github.com/r-dbi/odbc/issues/175

, we haven't been able to get this working.

We're just trying to write to a non-default schema using the tableID's as suggested.

Code is:

[KimmoMW]

con <- DBI::dbConnect(odbc::odbc(), 
+                       Driver = "SQL Server", 
+                       Server = "ADS-UAT", 
+                       Database = "Sandbox", 
+                       Trusted_Connection = "True")
> table_id <- Id(schema = "Belmont", 
+                name = "IRIS")
> res <- dbWriteTable(conn = con,
+                     name = table_id,
+                     value = iris,
+                     append = TRUE)
Error in connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in%  : 
  nanodbc/nanodbc.cpp:2525: 24000: [Microsoft][ODBC SQL Server Driver]Invalid cursor state 

Another error on different machine is:

Error in connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in%  : 
  nanodbc/nanodbc.cpp:4266: 08S01: [Microsoft][ODBC SQL Server Driver]Communication link failure 

The odbc writes fine to a default schema, but haven't been able to write to a non-default schema. Any help would be most appreciated. Thanks.

[jimhester]

You need to use Id(schema = "Belmont", table = "IRIS") not name =.

[KimmoMW]

Thanks for that Jim. Have tried this but same error is happening as below:

library("DBI")
con <- DBI::dbConnect(odbc::odbc(), 
                                              Driver = "SQL Server", 
                                              Server = "ADS-UAT", 
                                              Database = "Sandbox", 
                                              Trusted_Connection = "True")
table_id <- Id(schema = "Belmont", 
                                  table = "IRIS")
res <- dbWriteTable(conn = con,
                                           name = table_id,
                                           value = iris,
                                          append = TRUE)
Error in connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in%  : 
  nanodbc/nanodbc.cpp:2525: 24000: [Microsoft][ODBC SQL Server Driver]Invalid cursor state 

[colearendt]

Interesting. Is your connection in a good state immediately prior to the dbWriteTable? I.e. does dbGetQuery(con, "SELECT 1;") succeed before dbWriteTable? Just curious if another query may be impacting.

What version of SQL Server are you connecting to? And are you connecting from a Windows machine to get Trusted_Connection? What type of ODBC driver are you using? I am unable to repro from linux using standard connection. However, did get an interesting error trying to append.

library("DBI")
cfg <- config::get(file = "~/wrk/conn.yml")
con <- do.call(DBI::dbConnect, cfg$mssql)
table_id <- Id(schema = "Belmont", 
               table = "IRIS")
res <- dbWriteTable(conn = con,
                    name = table_id,
                    value = iris,
                    append = TRUE, 
                    overwrite = FALSE)

head(dbReadTable(con, table_id))
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> 1          5.1         3.5          1.4         0.2  setosa
#> 2          4.9         3.0          1.4         0.2  setosa
#> 3          4.7         3.2          1.3         0.2  setosa
#> 4          4.6         3.1          1.5         0.2  setosa
#> 5          5.0         3.6          1.4         0.2  setosa
#> 6          5.4         3.9          1.7         0.4  setosa

res <- dbWriteTable(conn = con,
                    name = table_id,
                    value = iris,
                    append = TRUE, 
                    overwrite = FALSE)
#> Error: <SQL> 'CREATE TABLE "Belmont"."IRIS" (
#>   "Sepal.Length" FLOAT,
#>   "Sepal.Width" FLOAT,
#>   "Petal.Length" FLOAT,
#>   "Petal.Width" FLOAT,
#>   "Species" varchar(255)
#> )
#> '
#>   nanodbc/nanodbc.cpp:1587: 42S01: There is already an object named 'IRIS' in the database.,

Created on 2018-07-21 by the reprex package (v0.2.0).

[hakki13]

Hi, I have similar problems. I can't get anything to work.

library(odbc)
library(DBI)
con <- dbConnect(odbc(), 
             Driver = "SQL Server", 
             Server = "LAPTOPSQL", 
             Database = "Test", 
            Trusted_Connection = "True")

#Default works, will write Table1 under dbo
(dbWriteTable(con, "Table1", iris, overwrite = T))
[1] TRUE

#First Try
table_id <- Id(schema = "other",  table = "Table1")
dbWriteTable(conn = con, name = table_id, value = iris, append = FALSE, overwrite = TRUE)

Error in connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in%  : 
  nanodbc/nanodbc.cpp:2525: 24000: [Microsoft][ODBC SQL Server Driver]Invalid cursor state 

#Other solution
dbWriteTable(con, SQL("other.Table1"), iris, overwrite = T)

Error: Can't unquote other.Table1

other attached packages: [1] odbc_1.1.6 DBI_1.0.0

What can I do?

[KimmoMW]

I ended up just writing a server side process which moves the table from the default schema to desired schema. I call the stored p procedure using the rodbc package. The odbc passage moves the data quickly into the database. The rodbc triggers a fast server side process to move and delete the temporary default schema table.

[colearendt]

Yuck! That should definitely not be necessary - have you seen issue #91 ? A handful of other solutions there. I'll try to take a look and see if I can repro today.

What version of SQL Server are you connecting to? And are you connecting from a Windows machine to get Trusted_Connection? What type of ODBC driver are you using?

[hakki13]

My SQL Version is 14.0.1000.169, I'm connecting from windows 10. I don't know what type odbc driver, I have. I'm using odbc package in R. Package version are in my comment above.

[krlmlr]

For troubleshooting this, can you please try the following:

odbc:::connection_sql_tables(
  con@ptr,
  catalog_name = "",
  schema_name = "<your schema name>",
  table_name = "<your table name>"
)

The odbc package uses catalog_name = "%" but I'm not sure this is correct; the syntax above worked for me with Microsoft SQL Server.

(This won't fix the problems with creating the table, but we might be able to better understand what's going on.)

[hakki13]

Ok, Here is what I have. I created by hand "Table1" to Schema "other" in "Test" Catalog.

> odbc:::connection_sql_tables(
+   con@ptr,
+   catalog_name = "Test",
+   schema_name = "other",
+   table_name = "Table1"
+ )
  table_catalog table_name table_type table_schema table_remarks
1          Test     Table1      TABLE        other    

So looks like it will find it, but If I leave catalog_name empty as in your example:

> odbc:::connection_sql_tables(
+   con@ptr,
+   catalog_name = "",
+   schema_name = "other",
+   table_name = "Table1"
+ )
[1] table_catalog table_name    table_type    table_schema  table_remarks
<0 rows> (or 0-length row.names)

And "%" gives similar cursor state error, what I have in creating table.

> odbc:::connection_sql_tables(
+   con@ptr,
+   catalog_name = "%",
+   schema_name = "other",
+   table_name = "Table1"
+ )
Error in odbc:::connection_sql_tables(con@ptr, catalog_name = "%", schema_name = "other",  : 
  nanodbc/nanodbc.cpp:2525: 24000: [Microsoft][ODBC SQL Server Driver]Invalid cursor state 

[krlmlr]

Thanks. What about

odbc:::connection_sql_tables(
  con@ptr,
  catalog_name = NULL,
  schema_name = "<your schema name>",
  table_name = "<your table name>"
)

?

[hakki13]

Sorry for late response, here is what I get.

> odbc:::connection_sql_tables(
+   con@ptr,
+   catalog_name = NULL,
+   schema_name = "other",
+   table_name = "Table_1"
+ )
  table_catalog table_name table_type table_schema table_remarks
1          Test    Table_1      TABLE        other   

SO seems to work with NULL

[Patrick01]

I maybe had the same problem.

I had the change the table name to inclucde a "_" for the database table. This seemed to be a condition by the database.

[raffscallion]

Piping in to say I have the exact same situation as @hakki13. I am connecting to SQL Server 13.0.4223.10 from Windows 10 with odbc 1.1.6. I receive the same invalid cursor state error when trying to write to the non-default schema. Below is the result of the connection_sql_tables(). Note the potential extra wrinkle of the dot in the table_catalog.

odbc:::connection_sql_tables(con@ptr, 
catalog_name = NULL, 
schema_name = "analysis", 
table_name = "FieldBlankStats")

  table_catalog      table_name table_type table_schema table_remarks
1   Improve_2.1 FieldBlankStats      TABLE     analysis    

[raffscallion]

Hoping to provide a bit more to help with debugging.

# Cannot append to table in non-default schema - why?
library(DBI)
con <- dbConnect(odbc::odbc(), Driver = "SQL Server", server = "CL-SQL-APPDEV",
                      database = "CIA_1_0", port = 1433L)

table <- Id(catalog = "CIA_1_0", schema = "import", table = "test")

df <- data.frame(a = c(1, 3),
                 b = c(4, 5))
post <- dbWriteTable(con, name = table, value = df, append = TRUE)
#> Error: Can't unquote "CIA_1_0"."import"."test"

# It appears that something is not working with this round trip. Should this succeed?
q <- dbQuoteIdentifier(con, table)
u <- dbUnquoteIdentifier(con, q)
#> Error: Can't unquote "CIA_1_0"."import"."test"

DBI::dbDisconnect(con)

Created on 2018-09-20 by the reprex package (v0.2.0).

Session info

``` r devtools::session_info() #> Session info ------------------------------------------------------------- #> setting value #> version R version 3.5.1 (2018-07-02) #> system x86_64, mingw32 #> ui RTerm #> language (EN) #> collate English_United States.1252 #> tz America/Los_Angeles #> date 2018-09-20 #> Packages ----------------------------------------------------------------- #> package * version date source #> backports 1.1.2 2017-12-13 CRAN (R 3.5.0) #> base * 3.5.1 2018-07-02 local #> bit 1.1-14 2018-05-29 CRAN (R 3.5.0) #> bit64 0.9-7 2017-05-08 CRAN (R 3.5.0) #> blob 1.1.1 2018-03-25 CRAN (R 3.5.1) #> compiler 3.5.1 2018-07-02 local #> datasets * 3.5.1 2018-07-02 local #> DBI * 1.0.0.9000 2018-09-05 Github (r-dbi/DBI@dbc32a0) #> devtools 1.13.6 2018-06-27 CRAN (R 3.5.1) #> digest 0.6.15 2018-01-28 CRAN (R 3.5.1) #> evaluate 0.10.1 2017-06-24 CRAN (R 3.5.1) #> graphics * 3.5.1 2018-07-02 local #> grDevices * 3.5.1 2018-07-02 local #> hms 0.4.2 2018-03-10 CRAN (R 3.5.1) #> htmltools 0.3.6 2017-04-28 CRAN (R 3.5.1) #> knitr 1.20 2018-02-20 CRAN (R 3.5.1) #> magrittr 1.5 2014-11-22 CRAN (R 3.5.1) #> memoise 1.1.0 2017-04-21 CRAN (R 3.5.1) #> methods * 3.5.1 2018-07-02 local #> odbc 1.1.6 2018-06-09 CRAN (R 3.5.1) #> pkgconfig 2.0.1 2017-03-21 CRAN (R 3.5.1) #> Rcpp 0.12.18 2018-07-23 CRAN (R 3.5.1) #> rlang 0.2.2.9000 2018-08-20 Github (r-lib/rlang@2cf70c8) #> rmarkdown 1.10 2018-06-11 CRAN (R 3.5.1) #> rprojroot 1.3-2 2018-01-03 CRAN (R 3.5.1) #> stats * 3.5.1 2018-07-02 local #> stringi 1.1.7 2018-03-12 CRAN (R 3.5.0) #> stringr 1.3.1 2018-05-10 CRAN (R 3.5.1) #> tools 3.5.1 2018-07-02 local #> utils * 3.5.1 2018-07-02 local #> withr 2.1.2 2018-03-15 CRAN (R 3.5.1) #> yaml 2.1.19 2018-05-01 CRAN (R 3.5.1) ```

[raffscallion]

Here's some more info. I'm hoping this will elicit a response from the experts.

library(DBI)

# This works
id1 <- Id(schema = "my_schema", table = "my_table")
unquoted <- dbUnquoteIdentifier(ANSI(), id1)

# This works
id2 <- Id(catalog = "my_db", schema = "my_schema", table = "my_table")
unquoted2 <- dbUnquoteIdentifier(ANSI(), id2)

### Round-tripping

# This works
q1 <- dbQuoteIdentifier(ANSI(), id1)
rt1 <- dbUnquoteIdentifier(ANSI(), q1)

# This fails
q2 <- dbQuoteIdentifier(ANSI(), id2)
rt2 <- dbUnquoteIdentifier(ANSI(), q2)
#> Error: Can't unquote "my_db"."my_schema"."my_table"

Created on 2018-09-25 by the reprex package (v0.2.0).

The offending code appears to be these lines in quote.R:

if (is(x, "SQL")) { rx <- '^(?:(?:|"((?:[^"]|"")+)"[.])(?:|"((?:[^"]|"")*)")|([^". ]+))$' bad <- grep(rx, x, invert = TRUE) if (length(bad) > 0) { stop("Can't unquote ", x[bad[[1]]], call. = FALSE) }

If your Id contains a catalog, then the SQL object created by dbQuoteIdentifier cannot pass the regex test above. I'm not competent enough with regex to know how to fix it, but I'm hoping it's straightforward.

[dfalbel]

I am experiencing the same issue described here. I am using:

Microsoft SQL Server Version: 12.00.5207

And here is my session info:

> sessionInfo()
R version 3.5.0 (2018-04-23)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows Server >= 2012 x64 (build 9200)

Matrix products: default

locale:
[1] LC_COLLATE=Portuguese_Brazil.1252  LC_CTYPE=Portuguese_Brazil.1252    LC_MONETARY=Portuguese_Brazil.1252
[4] LC_NUMERIC=C                       LC_TIME=Portuguese_Brazil.1252    

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

other attached packages:
[1] bindrcpp_0.2.2 furrr_0.1.0    future_1.9.0   magrittr_1.5   odbc_1.1.6    

loaded via a namespace (and not attached):
 [1] progress_1.2.0    zoo_1.8-2         tidyselect_0.2.4  urca_1.3-0        purrr_0.2.5       listenv_0.7.0    
 [7] lattice_0.20-35   colorspace_1.3-2  blob_1.1.1        rlang_0.2.0       pillar_1.2.1      glue_1.2.0       
[13] withr_2.1.2       DBI_1.0.0         bit64_0.9-7       forecast_8.3      TTR_0.23-3        bindr_0.1.1      
[19] plyr_1.8.4        quantmod_0.4-13   timeDate_3043.102 munsell_0.4.3     gtable_0.2.0      devtools_1.13.6  
[25] codetools_0.2-15  memoise_1.1.0     tseries_0.10-44   lmtest_0.9-36     parallel_3.5.0    curl_3.2         
[31] xts_0.10-2        Rcpp_0.12.18      scales_0.5.0      bit_1.1-14        fracdiff_1.4-2    ggplot2_2.2.1    
[37] hms_0.4.2         digest_0.6.15     dplyr_0.7.6       grid_3.5.0        quadprog_1.5-5    tools_3.5.0      
[43] lazyeval_0.2.1    tibble_1.4.2      crayon_1.3.4      tidyr_0.8.1       pkgconfig_2.0.1   data.table_1.11.4
[49] prettyunits_1.0.2 assertthat_0.2.0  httr_1.3.1        rstudioapi_0.7    R6_2.2.2          globals_0.12.1   
[55] uroot_2.0-9       nlme_3.1-137      nnet_7.3-12       git2r_0.23.0      compiler_3.5.0 

Running:

sql_previsao <- DBI::Id(schema = "StageR", table = "Previsao")
DBI::dbWriteTable(con, sql_previsao, value = mtcars)

Error in odbc:::connection_sql_tables(con@ptr, catalog_name = "%", schema_name = "", : nanodbc/nanodbc.cpp:2525: 24000: [Microsoft][ODBC SQL Server Driver]Invalid cursor state

Do you suggest any quick-fix?

[mkuehn10]

I have been having this issue as well. I can get it to work by using an underscore in my table name. I have no idea why it works, but it does.

# This works
dbWriteTable(con,
             Id(schema = "my_schema", table = "test_"),
             my_data,
             overwrite = TRUE)

# This does not
dbWriteTable(con,
             Id(schema = "my_schema", table = "test"),
             my_data,
             overwrite = TRUE)
Error in connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in%  : 
  nanodbc/nanodbc.cpp:2525: 24000: [Microsoft][ODBC SQL Server Driver]Invalid cursor state

[fab64443]

Hi I'm using odbc 1.1.6 and DBI 1.0.0 and the bug still exists. But if I use odbc SQLTable branch (1.1.1.9000) it's working. Is the fix planned to be merged in the CRAN version soon ?

[fab64443]

I tried the same code, appending new rows in the no-default schema of SQL Server Database.

With the packages in the last version (CRAN) :

#>  install.packages('odbc',dependencies=F)

library(DBI)
library(odbc)

packageVersion('odbc')
packageVersion('DBI')

dbhandle <- dbConnect(odbc::odbc(), dsn = 'MISDAPRD', uid='MIS_DA_SSIS', pwd=unserialize(uncipherMISDA))
dbGetQuery(dbhandle,'select count(*) N from cci.testtable')

rawdata <- dbGetQuery(dbhandle,'select top(3) * from cci.testtable')
rawdata

dbWriteTable(dbhandle,value=rawdata,name=DBI::Id(schema='cci',table='testtable'),append=TRUE)

dbWriteTable(dbhandle,value=rawdata,name=DBI::Id(catalog='MISDA',schema='cci',table='testtable'),append=TRUE)

odbc::dbId(dbhandle,'testtable','cci','MISDA')

dbDisconnect(dbhandle)

Without catalog, something goes wrong in the dbWriteTable code. With catalog, the function is not able to unquote the full DB name (catalog.schema.table) .

[1] '1.1.6'
[1] '1.0.0'

N
10

datefrom    dateto  perf
2016-09-01  2017-09-30  -0.5533046
2016-09-01  2017-09-30  2.4489095
2016-09-01  2017-09-30  -0.5399339

Error in connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in% : nanodbc/nanodbc.cpp:4266: 24000: [Microsoft][ODBC Driver 13 for                                
Traceback:

1. dbWriteTable(dbhandle, value = rawdata, name = DBI::Id(schema = "cci", 
 .    table = "testtable"), append = TRUE)
2. dbWriteTable(dbhandle, value = rawdata, name = DBI::Id(schema = "cci", 
 .    table = "testtable"), append = TRUE)
3. dbWriteTable(conn, dbQuoteIdentifier(conn, name), value, ...)
4. dbWriteTable(conn, dbQuoteIdentifier(conn, name), value, ...)
5. .local(conn, name, value, ...)
6. dbExistsTable(conn, name)
7. dbExistsTable(conn, name)
8. dbExistsTable(conn, dbUnquoteIdentifier(conn, name)[[1]], ...)
9. dbExistsTable(conn, dbUnquoteIdentifier(conn, name)[[1]], ...)
10. name@name[["table"]] %in% connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in% 
  .    names(name@name)) name@name[["catalog"]] else "%", schema_name = if ("schema" %in% 
  .    names(name@name)) name@name[["schema"]] else "%", table_name = if ("table" %in% 
  .    names(name@name)) name@name[["table"]] else "%")
11. connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in% 
  .    names(name@name)) name@name[["catalog"]] else "%", schema_name = if ("schema" %in% 
  .    names(name@name)) name@name[["schema"]] else "%", table_name = if ("table" %in% 
  .    names(name@name)) name@name[["table"]] else "%")

Error: Can't unquote "MISDA"."cci"."testtable"
Traceback:

1. dbWriteTable(dbhandle, value = rawdata, name = DBI::Id(catalog = "MISDA", 
 .    schema = "cci", table = "testtable"), append = TRUE)
2. dbWriteTable(dbhandle, value = rawdata, name = DBI::Id(catalog = "MISDA", 
 .    schema = "cci", table = "testtable"), append = TRUE)
3. dbWriteTable(conn, dbQuoteIdentifier(conn, name), value, ...)
4. dbWriteTable(conn, dbQuoteIdentifier(conn, name), value, ...)
5. .local(conn, name, value, ...)
6. dbExistsTable(conn, name)
7. dbExistsTable(conn, name)
8. dbExistsTable(conn, dbUnquoteIdentifier(conn, name)[[1]], ...)
9. dbUnquoteIdentifier(conn, name)
10. dbUnquoteIdentifier(conn, name)
11. stop("Can't unquote ", x[bad[[1]]], call. = FALSE)

Error: 'dbId' is not an exported object from 'namespace:odbc'
Traceback:

1. odbc::dbId
2. getExportedValue(pkg, name)
3. stop(gettextf("'%s' is not an exported object from 'namespace:%s'", 
 .    name, getNamespaceName(ns)), call. = FALSE, domain = NA)

I installed the SQLTable branch from odbc and ran the same code. When using DBI::Id, the append option is not used, and when using odbc:dbId it is working !

#> devtools::install_github("rstats-db/odbc@SQLTable")

library(DBI)
library(odbc)

packageVersion('odbc')
packageVersion('DBI')

dbhandle <- dbConnect(odbc::odbc(), dsn = 'MISDAPRD', uid='MIS_DA_SSIS', pwd=unserialize(uncipherMISDA))
dbGetQuery(dbhandle,'select count(*) N from cci.testtable')

rawdata <- dbGetQuery(dbhandle,'select top(3) * from cci.testtable')
rawdata

dbWriteTable(dbhandle,value=rawdata,name=DBI::Id(catalog='MISDA',schema='cci',table='testtable'),append=TRUE)

dbWriteTable(dbhandle,value=rawdata,name=odbc::dbId(dbhandle,'testtable','cci','MIS_DA'),append=TRUE)

dbGetQuery(dbhandle,'select count(*) N from cci.testtable')

dbDisconnect(dbhandle)

[1] '1.1.1.9000'
[1] '1.0.0'

N
10

datefrom    dateto  perf
2016-09-01  2017-09-30  -0.5533046
2016-09-01  2017-09-30  2.4489095
2016-09-01  2017-09-30  -0.5399339

Error: <SQL> 'CREATE TABLE "MISDA"."cci"."testtable" (
  "datefrom" DATE,
  "dateto" DATE,
  "perf" FLOAT
)
'
  nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][ODBC Driver 13 for SQL Server]                                            
Traceback:

1. dbWriteTable(dbhandle, value = rawdata, name = DBI::Id(catalog = "MISDA", 
 .    schema = "cci", table = "testtable"), append = TRUE)
2. dbWriteTable(dbhandle, value = rawdata, name = DBI::Id(catalog = "MISDA", 
 .    schema = "cci", table = "testtable"), append = TRUE)
3. dbWriteTable(conn, dbQuoteIdentifier(conn, name), value, ...)
4. dbWriteTable(conn, dbQuoteIdentifier(conn, name), value, ...)
5. .local(conn, name, value, ...)
6. dbExecute(conn, sql)
7. dbExecute(conn, sql)
8. dbSendStatement(conn, statement, ...)
9. dbSendStatement(conn, statement, ...)
10. OdbcResult(connection = conn, statement = statement)
11. new_result(connection@ptr, statement)

*** No output for the working call of dbWriteTable with odbc::dbID *** 

N
13

[nima1i]

Hi, Is there any chance that we see this issue resolved in odbc?? Are there any alternatives?? I tried to use the package @fab64443 mentioned with this code:

devtools::install_github("rstats-db/odbc@SQLTable",dependencies=F) library(odbc) con <- dbConnect(odbc(), Driver="SQL Server", Server="ServerName", Database="Result", UID="---------------------------", PWD="--------------------------" ) nycf=nycflights13::flights[1:2000,] dbWriteTable(con,value=nycf,name=odbc::dbId(con,"flights","DIT","ServerName"),append=TRUE)

but I got this error: Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘dbWriteTable’ for signature ‘"Microsoft SQL Server", "dbId", "tbl_df"’

[jimhester]

I reproduced the issue writing to schemas and appending, it ultimately came down to driver interpretations of the % wildcard vs NULL. Switching dbExistsTable() to use NULL in all cases seems to address the issue.

Hopefully it will not cause other issues in other databases.

[eduardocorrearaujo]

Im my case, use: dbWriteTable(con, c(schema, table_name), data) worked.