SQL function not working when trying to write table to non-default schema

Question

I am trying to write a table to a non-default schema in SQL Server 2017. I am using RStudio's documentation as to what the best practice is for doing this: https://db.rstudio.com/best-practices/schema/#write-non-temporary-tables.

However, when I follow their guidelines I get the following error: Error: Can't unquote EXISTING_SCHEMA.newTbleIris

EDIT

After some digging around, I have made some progress. But the issue isn't completely resolved. I have found the Id() function allows for me to read but not write. Example below. The error I get when trying to write is:

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

New reproducible example

library(odbc)
library(dbplyr)
library(dplyr)
library(DBI)

con <- dbConnect(odbc(),
                 Driver = "SQL SERVER",
                 Server = "SERVER_NAME",
                 Database = "DB_NAME",
                 UID = "USER_NAME",
                 PWD = "PASSWORD")

# Works assuming you already have iris table in EXISTING SCHEMA.
# I did this not in R because I cannot currently write to a non-default schema in R.
dbReadTable(con, Id(schema = "EXISTING_SCHEMA", name = "iris"))

# Still cannot write to non-default schema Throws an error.
dbWriteTable(con, Id(schema = "nycDoe", name = "iris"), iris)

Reproducible Example

library(odbc)
library(dbplyr)
library(dplyr)
library(DBI)

con <- dbConnect(odbc(),
                 Driver = "SQL SERVER",
                 Server = "SERVER_NAME",
                 Database = "DB_NAME",
                 UID = "USER_NAME",
                 PWD = "PASSWORD")

# this works, writing to default schema
dbWriteTable(con, SQL("newTbleIris"), iris)

# this does NOT work and gives error mentioned above
dbWriteTable(con, SQL("EXISTING_SCHEMA.newTbleIris"), iris)

Session Info

- Session info --------------------------------------------------------------------------------------------------------------------
 setting  value                       
 version  R version 3.5.2 (2018-12-20)
 os       Windows Server >= 2012 x64  
 system   x86_64, mingw32             
 ui       RStudio                     
 language (EN)                        
 collate  English_United States.1252  
 ctype    English_United States.1252  
 tz       America/New_York            
 date     2019-03-07                  

- Packages ------------------------------------------------------------------------------------------------------------------------
 package     * version date       lib source        
 assertthat    0.2.0   2017-04-11 [1] CRAN (R 3.5.2)
 backports     1.1.3   2018-12-14 [1] CRAN (R 3.5.2)
 bit           1.1-14  2018-05-29 [1] CRAN (R 3.5.2)
 bit64         0.9-7   2017-05-08 [1] CRAN (R 3.5.2)
 blob          1.1.1   2018-03-25 [1] CRAN (R 3.5.2)
 callr         3.1.1   2018-12-21 [1] CRAN (R 3.5.2)
 cli           1.0.1   2018-09-25 [1] CRAN (R 3.5.2)
 crayon        1.3.4   2017-09-16 [1] CRAN (R 3.5.2)
 DBI         * 1.0.0   2018-05-02 [1] CRAN (R 3.5.2)
 dbplyr      * 1.3.0   2019-01-09 [1] CRAN (R 3.5.2)
 desc          1.2.0   2018-05-01 [1] CRAN (R 3.5.2)
 devtools      2.0.1   2018-10-26 [1] CRAN (R 3.5.2)
 digest        0.6.18  2018-10-10 [1] CRAN (R 3.5.2)
 dplyr       * 0.8.0.1 2019-02-15 [1] CRAN (R 3.5.2)
 fs            1.2.6   2018-08-23 [1] CRAN (R 3.5.2)
 glue          1.3.0   2018-07-17 [1] CRAN (R 3.5.2)
 hms           0.4.2   2018-03-10 [1] CRAN (R 3.5.2)
 htmltools     0.3.6   2017-04-28 [1] CRAN (R 3.5.2)
 httpuv        1.4.5.1 2018-12-18 [1] CRAN (R 3.5.2)
 jsonlite      1.6     2018-12-07 [1] CRAN (R 3.5.2)
 later         0.8.0   2019-02-11 [1] CRAN (R 3.5.2)
 magrittr      1.5     2014-11-22 [1] CRAN (R 3.5.2)
 memoise       1.1.0   2017-04-21 [1] CRAN (R 3.5.2)
 mime          0.6     2018-10-05 [1] CRAN (R 3.5.2)
 odbc        * 1.1.6   2018-06-09 [1] CRAN (R 3.5.2)
 pillar        1.3.1   2018-12-15 [1] CRAN (R 3.5.2)
 pkgbuild      1.0.2   2018-10-16 [1] CRAN (R 3.5.2)
 pkgconfig     2.0.2   2018-08-16 [1] CRAN (R 3.5.2)
 pkgload       1.0.2   2018-10-29 [1] CRAN (R 3.5.2)
 prettyunits   1.0.2   2015-07-13 [1] CRAN (R 3.5.2)
 processx      3.2.1   2018-12-05 [1] CRAN (R 3.5.2)
 promises      1.0.1   2018-04-13 [1] CRAN (R 3.5.2)
 ps            1.3.0   2018-12-21 [1] CRAN (R 3.5.2)
 purrr       * 0.3.0   2019-01-27 [1] CRAN (R 3.5.2)
 R6            2.4.0   2019-02-14 [1] CRAN (R 3.5.2)
 Rcpp          1.0.0   2018-11-07 [1] CRAN (R 3.5.2)
 remotes       2.0.2   2018-10-30 [1] CRAN (R 3.5.2)
 rlang         0.3.1   2019-01-08 [1] CRAN (R 3.5.2)
 rprojroot     1.3-2   2018-01-03 [1] CRAN (R 3.5.2)
 rstudioapi    0.9.0   2019-01-09 [1] CRAN (R 3.5.2)
 sessioninfo   1.1.1   2018-11-05 [1] CRAN (R 3.5.2)
 shiny       * 1.2.0   2018-11-02 [1] CRAN (R 3.5.2)
 tibble        2.0.1   2019-01-12 [1] CRAN (R 3.5.2)
 tidyselect    0.2.5   2018-10-11 [1] CRAN (R 3.5.2)
 usethis       1.4.0   2018-08-14 [1] CRAN (R 3.5.2)
 withr         2.1.2   2018-03-15 [1] CRAN (R 3.5.2)
 xtable        1.8-3   2018-08-29 [1] CRAN (R 3.5.2)

So I have seen that, and it sort of works. For example, `dbReadTable(con, Id(schema = "EXISTING_SCHEMA", name = "iris"))` works (assuming you already have a table in that schema). However, `dbWriteTable(con, Id(schema = "EXISTING_SCHEMA", name = "iris"), iris)` does not work and throws an error `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` — drizzle123, Mar 12 '19 at 16:20
Can you try with the newer `dbCreateTable()` and `dbAppendTable()` generics? — krlmlr, Mar 12 '19 at 22:36
Used dbCreateTable() and dbAppendTable() to try and write a table to a non-default schema. But it failed. `##### Tables are created, but they are all empty dbCreateTable(con, SQL("EXISTING_SCHEMA.iris3"), iris) dbAppendTable(con, SQL("EXISTING_SCHEMA.iris3"), iris) iris3 <- dbReadTable(con, SQL("EXISTING_SCHEMA.iris3")) queryIris3 <- dbGetQuery(con, "SELECT * FROM EXISTING_SCHEMA.iris3")` — drizzle123, Mar 18 '19 at 21:22
I similarly tried with Id() rather than SQL(), but the result is the same. The table is empty. dbAppendTable() does not appear to actually be appending. `dbCreateTable(con, Id(schema = "EXISTING_SCHEMA", table = "iris4"), iris) dbAppendTable(con, Id(schema = "EXISTING_SCHEMA", table = "iris4"), iris) iris4 <- dbReadTable(con, Id(schema = "EXISTING_SCHEMA", table = "iris4"), iris) queryIris4 <- dbGetQuery(con, "SELECT * FROM EXISTING_SCHEMA.iris4")` — drizzle123, Mar 18 '19 at 21:23
Strangely, I get a similar problem even when I try work with tables in the default schema. dbAppendTable() still is not appending. `dbCreateTable(con, "iris2", iris) dbAppendTable(con, "iris2", iris) iris2 <- dbReadTable(con, "iris2") queryIris2 <- dbGetQuery(con, "SELECT * FROM iris2")` — drizzle123, Mar 18 '19 at 21:25

Walt Peralta · Answer 1 · 2019-04-18T18:43:21.440

1

I've found an easy workaround for this. Just follow through these steps:

First, write your table with a default schema: dbWriteTable(con, "newTbleIris", iris)

And insert it into a new one with a non-default schema, but this time using the dbGetQuery function:

dbGetQuery(con, "SELECT * INTO [schema].newTbleIris FROM newTbleIris")

And that's it! Your table will now appear with a non-default schema.

Now you can remove the first table you first created with a default schema by doing dbGetQuery(con, "DROP TABLE newTbleIris")

edited Apr 18 '19 at 18:43

answered Apr 03 '19 at 09:17

Walt Peralta

11
3

This is a very useful workaround! Hopefully in the future the maintainers of the package fix this issue. – drizzle123 Apr 17 '19 at 15:28
So I've actually run into an issue using your method, and I wonder if you have the same issue. First, I write the table `dbWriteTable(con, "iris", iris)`. Then, I copy the table `dbExecute(con, "SELECT * INTO [schema].iris FROM iris"). Finally, I drop the table I initially added into the default schema `dbExecute(con, "DROP TABLE iris"). However, when I run `dbExistsTable(con, "iris") the return value I get is TRUE. – drizzle123 Jun 18 '19 at 18:27
That's means the table does exist in your sql server with the new schema – Walt Peralta Jun 24 '19 at 12:14
Right. But `dbExistsTable()` should only return true for `[schema].iris`. In my function call, I don't specify the schema, and I would have thought it would only check the default schema. This isn't the case, however, hence why it's returning true. However, this leads to problematic behavior down the line because if I try to write to the default schema using the table name `iris` it won't let me. Because it thinks the table already exists. Hopefully this is clear. – drizzle123 Jun 26 '19 at 22:38

score 1 · Answer 2 · answered Nov 13 '20 at 22:43

1

DBI 1.1.0 overcomes this issue.

dbWriteTable(con, SQL("schema.newTbleIris"), iris)

answered Nov 13 '20 at 22:43

Zaki

131
3

1

I had to upgrade DBI to 1.1.0 and also odbc to 1.3.0, and then use the SQL function, e.g. dbWriteTable(con, SQL("SCHEMA.TABLE"), value = table, row.names = FALSE, overwrite=FALSE, append=TRUE). That worked. – Chris Dec 08 '20 at 16:12

score 0 · Answer 3 · answered May 14 '19 at 18:53

0

In my case, doing dbWriteTable(con, "[schema].newTbleIris", iris) without wrapping any function around worked.

answered May 14 '19 at 18:53

Jas

1

Yeah this isn't working for me. Can you post more details about how your environment? I tried with the "[" "]" characters and without those characters. – drizzle123 Jun 11 '19 at 16:25
Hey drizzle, the exact syntax I am using is without "[]" around the schema name. Did you also try that? `dbWriteTable(con, "schema.tablename", dt)` – Jas Jun 13 '19 at 17:40
Yup. I tried with the "[]" characters and without them, and nothing works. What winds up happening is a table gets written to the default schema as "schema.tablename". – drizzle123 Jun 14 '19 at 18:06

score 0 · Answer 4 · answered Feb 03 '22 at 20:12

0

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

answered Feb 03 '22 at 20:12

Eduardo Araujo

1

1

Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, **can you [edit] your answer to include an explanation of what you're doing** and why you believe it is the best approach? Reference @Walt's answer as a good example of this. – Jeremy Caney Feb 04 '22 at 00:20

SQL function not working when trying to write table to non-default schema

EDIT

New reproducible example

Reproducible Example

Session Info

4 Answers4

Linked