I am trying to pull data from a SQL database that I have access to. I can connect to the database, see the tables and get the fields associated with a given table, but cannot read a table into an R variable.
I'm working in R Studio, in case this makes a difference.
I have tried using online code snippets (new to R) and these work, except for the dbReadTable() examples. I have used both "Payments" and name="Payments" as the second argument, and both with and without "" quotes.
library(DBI)
con<-(dbConnect(odbc::odbc(), .connection_string="Driver={SQL Server},
Server=example_1234
Database=exampleDB
TrustedConnection=TRUE")
testing123 <- dbListFields(con,"Payments")
testing456 <- dbReadTable(con,"Payments")
I expect a connection to the database which is now named con. This works. I expect testing123 to contain all the fields in "Payments". This also works. I expect testing456 to be a data.frame copy of Payments. This produces: Error: 'SELECT * FROM "Payments" nanodbc/nanobdc.cpp:1587 42s02 [Microsoft][ODBC SQL SERVER DRIVER][SQL SERVER]Invalid pbject name 'Payments'.
It's slightly different without "Payments" as the argument - simply saying "Object "Payments" not found".
Any help much appreciated.
I suspect that it's because your table is in a different catalog or schema.
Rationale: DBI::dbListFields is doing select * from ... limit 0 (which is not correct syntax for sql server), but odbc::dbListFields is really calling a C++ function connection_sql_columns that is SQL Server specific. It might be permitting you to be a touch sloppy in that it will find the table even if you do not specify the catalog and/or schema. This is why your dbListFields is working. However, DBI::dbReadTable is really doing select * from ... under the hood (and odbc:: is not overriding it), so it is not allowing you to omit the schema (and/or catalog).
First, find the specific table information for your case:
DBI::dbGetQuery(con, "select top 1 table_catalog, table_schema, table_name, column_name from information_schema.columns where table_name='events'")
# table_catalog table_schema table_name column_name
# 1 my_catalog dbo Payments Id
(I'm projecting what you'll find.)
From here, try one of the following until it works:
x <- DBI::dbReadTable(con, DBI::SQL("[Payments]")) # equivalent to the original
x <- DBI::dbReadTable(con, DBI::SQL("[dbo].[Payments]"))
x <- DBI::dbReadTable(con, DBI::SQL("[my_catalog].[dbo].[Payments]"))
My guess is that DBI::dbGetQuery(con, "select top 1 * from Payments") will not work, so for "regular queries" you'll need to use the same hierarchy of catalog.schema.table, such as one of
DBI::dbGetQuery(con, "select top 1 * from dbo.Payments")
DBI::dbGetQuery(con, "select top 1 * from [dbo].[Payments]")
DBI::dbGetQuery(con, "select top 1 * from [my_catalog].[dbo].[Payments]")
(The use of the [ and ] quoted-identifier brackets are often a personal preference, strictly required in only some corner cases.)