4

I have problem with MSSQL in R language, similar like in R DBI ODBC error: nanodbc/nanodbc.cpp:3110: 07009: [Microsoft][ODBC Driver 13 for SQL Server]Invalid Descriptor Index , but a little another, or I don't understand something.

I have clearly connection with DB and my SELECT works when I send something like this:

third <- DBI::dbGetQuery(con, "SELECT TOP 1 
                         arr_delay_new,
                         fl_date,
                         carrier,
                         origin_city_name,
                         dest_city_name
                   FROM Flight_delays 
                   ORDER BY arr_delay_new DESC")

Problem is in the columns order. I have to show response in other order - like this:

third <- DBI::dbGetQuery(con, "SELECT TOP 1 
                         carrier,
                         arr_delay_new,
                         fl_date,
                         origin_city_name,
                         dest_city_name
                   FROM Flight_delays 
                   ORDER BY arr_delay_new DESC")

and when I send this request - is error: "result_fetch(res@ptr, n)': nanodbc/nanodbc.cpp:2966: 07009: [Microsoft][ODBC Driver 13 for SQL Server]Invalid Descriptor Index"

How I can set up this or which workaround could help me change order?

I'm fresh in R-language, so sorry if it's to easy

damlit
  • 103
  • 1
  • 6

2 Answers2

6

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).

Edit 2: make sure you're using a recent-enough version of Microsoft's ODBC driver (OS-level), either "ODBC Driver 17 for SQL Server" or "ODBC Driver 18 for SQL Server". I don't think (but have not verified that) this is sensitive to the subversions within 17 or 18.

# 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:

r2evans
  • 141,215
  • 6
  • 77
  • 149
1

The solution of updating the ODBC package is dependent on the Microsoft ODBC Driver you're using. When I upgraded to ODBC-1.3.3 the problem returned. Upgrading the Microsoft ODBC Driver to Microsoft ODBC Driver 17 for SQL Server (https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15) solved the problem. So, if the above solution does not work for you, try upgrading your driver.

Andy Cooper
  • 11
  • 3