0

I am able to establish a connection to a Microsoft SQL Server and am also able to read tables.

pool <- pool::dbPool(drv=odbc::odbc(),
                     dsn="MYDSN",
                     uid = "MYUID",
                     pwd = "XXXXX")
con <- poolCheckout(pool)

WVDListFull <- tbl(con, in_schema('Midas',"WVDListFull")) %>% head() %>% collect()

However I am unable to append new records to the table. Assuming that I have new records in a dataframe called x, I ttried the following code:

dbWriteTable(pool,'[Midas].[WVDListFull]', x, append=TRUE)

This gave me an error:

nanodbc/nanodbc.cpp:1587: 42000: [FreeTDS][SQL Server]CREATE TABLE permission denied in database 'ScorpioEDW'.

I do have read and write permissions on the said database. I also tried this:

dbWriteTable(con,DBI::SQL("Midas.WVDListFull"), x, append=TRUE)

Which resulted in another error:

Error: Can't unquote Midas.WVDListFull

Here Midas is the schema containing the table WVDListFull. Can someone tell me what's going on here?

r2evans
  • 141,215
  • 6
  • 77
  • 149
Dhiraj
  • 1,650
  • 1
  • 18
  • 44
  • You're using [`odbc`](https://cran.r-project.org/web/packages/odbc/index.html) not [`RODBC`](https://cran.r-project.org/web/packages/RODBC/index.html), so I'm removing the [tag:RODBC] tag. – r2evans Dec 26 '18 at 15:58
  • @r2evans yes that's correct, my bad. Would it make a difference if I was to use `RODBC`? – Dhiraj Dec 26 '18 at 16:02
  • Have you tried `dbWriteTable(pool,'Midas.WVDListFull', x, append=TRUE)` (without the brackets)? Don't know if it'll work, but a thought. Also, does the DSN definition include something like `SQL_ACCESS_MODE=1` or `applicationintent=readonly` or some other read-only indicator? – r2evans Dec 26 '18 at 16:08
  • @r2evans just tried it and get the same error message. I will need to find out about the DSN definition from the IT team. But they confirmed I have full read and write access. SQL queries like `insert into [Midas].[WVDListFull]...` with my account seems to work on the SQL server. – Dhiraj Dec 26 '18 at 16:15
  • I've had issues with SQL Server and permissions in the past, where the DBA "knew" I had read/write access but it had been done incorrectly on a few tables. Regardless, all the write-perms on the server mean nothing if your DSN connection is defined as read-only. – r2evans Dec 26 '18 at 16:18
  • 1
    Roger that. Will have it checked. – Dhiraj Dec 26 '18 at 16:19
  • Are you able to create (and then append to) a new table? If your DSN is not read-only, it might help (you and your DBA) to know if it's *just* that table or if it's more systemic. – r2evans Dec 26 '18 at 16:21
  • @r2evans So I tried `dbWriteTable(pool, 'Midas.iris', iris, append=TRUE)` and still get the same permissions error. – Dhiraj Dec 26 '18 at 16:31
  • Can you please try `dbAppendTable()`? – krlmlr Mar 12 '19 at 22:57

0 Answers0