Using dbReadTable in_schema

Question

I'm trying to use dbReadTable , however my tables sit under a schema inside the database.

For example using the code below I can connect:

  db_ANZSCO <- tbl(con, in_schema("BGVIEW" ,"ANZSCO"))

But when I try to use dbReadTable I get the following error;

  dbReadTable(con, "ANZSCO"))  

   Error: <SQL> 'SELECT * FROM "ANZSCO"'
     nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][ODBC SQL Server      Driver][SQL Server]The SELECT permission was denied on the object 'ANZSCO', database 'BurningGlass', schema 'dbo'.

I understand the table is sitting under a schema but I have no idea how to access using dbReadTable, I did try to look into documentation but I have been so far unsuccessful.

Tks

wibeasley · Accepted Answer · 2018-06-25 07:53:08Z

2

Try the new-ish DBI::Id() function, that accepts the schema name and table name as separate arguments.

con <- DBI::dbConnect(drv = odbc::odbc(), dsn = "qqqq") # Replace `qqqq`.
a <- DBI::Id(
  schema  = "BGVIEW",
  table   = "ANZSCO"
)
ds <- DBI::dbReadTable(con, a)
DBI::dbDisconnect(con)

If that doesn't work, please paste the table definition in your question, and the code that creates the connection. You've tagged your question with [rodbc], but it looks likes you're using the newer & DBI-compliant odbc package.

6 Comments

Ian_De_Oliveira Over a year ago

Tks for your response, I did not understand the creation of the object a then using a s inside ds.. What is the use of a?

wibeasley Over a year ago

sorry, try now. I transferred the wrong line of code into the browser.

Ian_De_Oliveira Over a year ago

Tks a lot, I have a quick question if you don't mind. What does the id does?

wibeasley Over a year ago

I can't explain it as well as the authors of the three links under 'See also' above. The best short summary is probably from that 'Schema Support' section: "The new Id() function constructs identifiers. All arguments must be named, yet DBI doesn’t specify the argument names, because DBMS have an inconsistent notion of namespaces."

Luís de Sousa Over a year ago

Fails miserably with an error message like: Could not create execute: SELECT * from """BGVIEW"".""ANZSCO""".

|

Martin Gal · Accepted Answer · 2023-09-03 14:55:02Z

0

Try this:

df <- dbReadTable(con, c("BGVIEW" ,"ANZSCO")))

edited Sep 3, 2023 at 14:55

Martin Gal

17k5 gold badges24 silver badges42 bronze badges

answered Sep 1, 2023 at 14:04

Geert

112 bronze badges

Collectives™ on Stack Overflow

Using dbReadTable in_schema

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related