2

If I want to connect R to a database, do I still need to install driver for R user?

I had done successful connection with server but unable to read/write/fetch the tables.

library(odbc)
con <- dbConnect(odbc::odbc(), 
         .connection_string = 'driver={SQL Server};server=DW01;database=AF_DW;trusted_connection=true')

Now I can see AF_DW in RStudio connections.

enter image description here

dbListFields(con, "Visits")

I can see all the variables in the table "Visits"

data <- dbReadTable(con, "Visits")

Got an Error: nanodbc/nanodbc.cpp:1655: 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Visits'. [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. 'SELECT * FROM "Visits")

data3 <- dbGetQuery(con, "SELECT * FROM Visits")

Got same error

data4 <- dbSendQuery(con, "SELECT * FROM Visits")

Got same error

con_in_R <- dbFetch(dbSendQuery(con,"SELECT * FROM Visits"))

Error in (function (cond) : error in evaluating the argument 'res' in selecting a method for function 'dbFetch': nanodbc/nanodbc.cpp:1655: 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Visits'. [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. 'SELECT * FROM Visits'

Appreciate your help. Thank you.

DBhatta
  • 51
  • 7

1 Answers1

1

According to you image, Visits is not stored in the default dbo schema as all your queries assume but under the eCW schema.

Like most RDBMS's, SQL Server follows the three part name convention for objects (tables, stored procedures, functions): [database].[schema].[object] where database is not necessary for a database specific connection and schema not necessary for dbo default.

Therefore, you need to reference schema and table name in your attempted queries.

s <- Id(schema = "eCW", table = "Visits")

# READ FROM NON-DEFAULT SCHEMA TABLE
data3 <- dbReadTable(con, s) 
data3 <- dbGetQuery(con, "SELECT * FROM [eCW].[Visits]")

# WRITE TO NON-DEFAULT SCHEMA TABLE
dbWriteTable(conn, s, mydataframe)
dbWriteTable(con, SQL("eCW.Visits"), mydataframe)
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • I think you have read correctly @Parfait. AF_DW is a database (catalog) and inside the AF_DW: DW, eCW, WG are the schema. Visits is a table Inside the eCW. – DBhatta Nov 30 '20 at 22:27
  • Hmmm...looks like this is a known issue in R: [R DBI ODBC error: nanodbc/nanodbc.cpp:3...](https://stackoverflow.com/questions/45001152/r-dbi-odbc-error-nanodbc-nanodbc-cpp3110-07009-microsoftodbc-driver-13-fo). You may have very [large data types](https://learn.microsoft.com/en-us/sql/connect/jdbc/using-advanced-data-types?view=sql-server-ver15) (e.g., `varbinary(max)`, `text`, `image`) in table. Try selecting columns and not all with `*`: `SELECT col1, col2, col3 FROM [eCW].[Visits]`. `dbReadTable` reads all columns in order of `CREATE TABLE` statement. – Parfait Nov 30 '20 at 22:50
  • Then, explicitly `SELECT` all needed columns and choose the large data types as very *last* columns to avoid earlier error (which is not due to rows but columns). To help further, post the [schema of your table](https://stackoverflow.com/q/13405572/1422451). Need to see if table holds the large data types. – Parfait Dec 01 '20 at 04:27
  • Please update your post with such long information and delete above comments, even past comments. Again, try explicitly naming the columns you need and like above R error post shows, place the `varbinary` or `text` at the *end* of `SELECT`. – Parfait Dec 01 '20 at 18:57