R Dbi Odbc Error: Nanodbc/Nanodbc.Cpp:3110: 07009: [Microsoft][Odbc Driver 13 for SQL Server]Invalid Descriptor Index

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

EDIT: if you are on odbc-1.3.0 or older, then skip this portion and go to the original answer, below. (Or update and reap the benefits.)

Starting with odbc-1.3.1, the underlying code works around the fundamental ODBC "feature" (bug). With the update, this particular error no longer indicates a problem with column-order (if it occurs at all).

# con <- DBI::dbConnect(...)
DBI::dbExecute(con, "create table test (id int, longstr nvarchar(max), shortstr nvarchar(64))")
DBI::dbWriteTable(con, "test", data.frame(id=1, longstr="hello", shortstr="world"), create=FALSE, append=TRUE)
DBI::dbGetQuery(con, "select * from test")
#   id longstr shortstr
# 1  1   hello    world

Huge accolades to @detule (author of PR !415), and to @Jim (@jimhester on HG) and @krlmlr (among several others) for updating and maintaining odbc.

---

(for odbc-1.3.0 and older)

Up front, the order of columns matters.

This is a long-standing error when using Microsoft's own "ODBC Driver": in the ODBC standard, Microsoft says (arbitrarily, I think, since no other drivers or DBMSes feel this is necessary) that "long data" must all be at the end of the query. "Long data" is vague, even MS's page says "such as 255 character", not sure if that's the firm number.

Unfortunately, as long as you're using MS's ODBC drivers for its own SQL Server, then it doesn't matter if this is R or python or Access, it's still broken. (Actually, they don't think it's broken.)

So the fix is to determine which columns are "long" and ensure they are the last column(s) selected.

For example:

# con <- DBI::dbConnect(...)
DBI::dbExecute(con, "create table test (id int, longstr nvarchar(max), shortstr nvarchar(64))")
DBI::dbGetQuery(con, "select column_name, data_type, character_maximum_length from information_schema.columns where table_name='test'")
#   column_name data_type character_maximum_length
# 1          id       int                       NA
# 2     longstr  nvarchar                       -1
# 3    shortstr  nvarchar                       64

In this case, longstr's length is -1 indicating "max"; even 255 would be too big.

DBI::dbWriteTable(con, "test", data.frame(id=1, longstr="hello", shortstr="world"), create=FALSE, append=TRUE)
DBI::dbGetQuery(con, "select * from test")
# Error in result_fetch(res@ptr, n) : 
#   nanodbc/nanodbc.cpp:2966: 07009: [Microsoft][ODBC Driver 17 for SQL Server]Invalid Descriptor Index 

### must reconnect
# con <- DBI::dbConnect(...)
DBI::dbGetQuery(con, "select id, shortstr, longstr from test")
#   id shortstr longstr
# 1  1    world   hello

References:

• https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/getting-long-data
• https://github.com/r-dbi/odbc/issues/112
• https://github.com/r-dbi/odbc/issues/86
• https://github.com/nanodbc/nanodbc/issues/228 (discussion about workaround/resolution!)
• perhaps many more.

Invalid Descriptor Index when reading table from MS SQL 2012 odbc connection

So that just to close this:

is is related to the isseu pointed out by @Hearkz
https://github.com/r-dbi/odbc/issues/10
Which also relates to ntext and text variables, so that I need to adjust these variables to nvarchar(veryhighnumber) instead of nvarchar(MAX).

Save or Read data from SQL Server in to R using ODBC package

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)

R ODBC nanodbc error when not using DSN

When I switched from a Linux to a Windows box for R (note nothing else changed) I was getting

"Error: nanodbc/nanodbc.cpp:950: IM002: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" 

The ODBC connection issue was fixed by changing

Driver = "ODBC Driver 17 for SQL Server"

to

Driver = "SQL Server"

Hope this saves you some time.

---

Related Topics