1

I am trying import tables in SQL Server 2017 into R. So, I wrote down these codes. Connection seems okay. But when I import tables I get an error and a warning message like below. I can solve this problem by write sqlQuery or dbGetQuery in R but I would like to understand what is problem with dbReadTable. Finally I kindly ask you help me to list tables within particular schema in R.

install.packages("RODBC")
library(DBI)
library(RODBC)
install.packages("odbc")
library(odbc)

# Making proper conection.
con <- DBI::dbConnect(odbc::odbc(),
                      Driver   = "SQL Server",
                      Server   = "WINDOWS-M6QDHVB\\SQLEXPRESS",
                      Database = "AdventureWorks2017",
                      Trusted_Connection = "True")
#Import data wihtin the table into R.            
dbReadTable(con, SQL("HumanResources.Department"))
dbReadTable(con, SQL("HumanResources.Employee"))

Warning message:
In dbClearResult(rs) : Result already cleared

Error in result_fetch(res@ptr, n) : 
  nanodbc/nanodbc.cpp:3186: 07009: [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
Khazar
  • 83
  • 2
  • 9
  • the script in your question executes fine. The warning message does not reflect the script, has anything been left out? for retrieving tables of a schema, you could read INFORMATION_SCHEMA.TABLES and then filter on particular schema (not optimal, just demo): `tables <- dbReadTable(con, SQL("INFORMATION_SCHEMA.TABLES")) ` `tables[tables$TABLE_SCHEMA == "HumanResources",] ` – lptr Apr 08 '20 at 11:34
  • ...also, there is a bug in the odbc driver, https://github.com/r-dbi/odbc/issues/10, for tables that have varchar(max)/text columns. – lptr Apr 15 '20 at 10:27

0 Answers0