R DBI ODBC error: nanodbc/nanodbc.cpp:3110: 07009: [Microsoft][ODBC Driver 13 for SQL Server]Invalid Descriptor Index

I continue to read the DBI/ODBC is faster than RODBC, so I tried as follows:

require(DBI);require(odbc)
con <- DBI::dbConnect(odbc::odbc(), dsn = 'SQLSERVER1', database = 'AcumaticaDB')

I can make a successful connection to the DSN, but the following query:

rs <- dbGetQuery(con, "SELECT * FROM inventoryitem")
dbFetch(rs)

gives me the following error:

Error in result_fetch(res@ptr, n, ...) : nanodbc/nanodbc.cpp:3110: 07009: [Microsoft][ODBC Driver 13 for SQL Server]Invalid Descriptor Index

What am I doing wrong ? Please, no RODBC solutions. Thanks!

I have also been struggling with this issue for several months. However, I have come across a solution that may help you as well.

In a nutshell, the issue occurs when certain text columns do not appear after integer/numeric columns. When the columns are not aligned properly in the query, an error of invalid index is thrown and your connection may freeze. The issue then is, how do I know what to put at the end of my query?

To determine this, one could typically examine a column using class() or typeof(). To examine such information from the database, you can use a query such as:

dbColumnInfo(dbSendQuery(con, "SELECT * from schema.table")) # You may not require the schema part...

This will return a table with a type field for every column in the data-set of interest. You can then use this table as an index to sort the select() statement. My particular difficulty is that the type field in the table was all numbers! However, I noticed that every column with a negative number, when placed at the end of the select statement, fixed my query and I could pull the whole table just fine. For example, my full solution:

# Create my index of column types (ref to the current order)
index <- dbColumnInfo(dbSendQuery(con, "SELECT * from schema.table"))
index$type <- as.integer(index$type) # B/c they are + and - numbers!

# Create the ref to the table
mySQLTbl <- tbl(con, in_schema("schema", "tablename"))

# Use the select statement to put all the + numbered columns first!
mySQLTbl %>%
  select(c(which(index$type>=0),
                 which(index$type<0)))

As for reason for why this occurs, I am not sure and I do not have the data access privileges to dig much deeper in my use-case

I appreciate that this question was asked a long time ago, but I've managed to find a workaround. The answers above got me most of the way there. The problem I had was with columns of type nvarchar that had a CHARACTER_MAXIMUM_LENGTH in the schema table of -1, which I understand means they are the maximum length possible.

My solution was to lookup the relevant table in the INFORMATION_SCHEMA.COLUMNS table and then rearrange my fields appropriately:

require(DBI);require(odbc)
library(tidyverse)
con <- DBI::dbConnect(odbc::odbc(), dsn = 'SQLSERVER1', database = 'AcumaticaDB')

column.types <- dbGetQuery(con, "SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='inventoryitem'")

ct <- column.types %>%
  mutate(cml = case_when(
    is.na(CHARACTER_MAXIMUM_LENGTH) ~ 10,
    CHARACTER_MAXIMUM_LENGTH == -1 ~ 100000,
    TRUE ~ as.double(CHARACTER_MAXIMUM_LENGTH)
    )
  ) %>%
  arrange(cml) %>%
  pull(COLUMN_NAME)

fields <- paste(ct, collapse=", ")
query <- paste("SELECT", fields, "FROM inventoryitems")

tbl(con, sql(query)) %>% head(5)