dbReadTable/dbGetQuery returns Invalid Descriptor Index error on SQL server 2016 with NAV tables #112
Comments
|
I am not sure what is going on, did you try returning only a subset of the columns in that table? Does the order of the columns matter? |
|
Order of column does matter, I put timestamp field to the end of select statement and it successfully loads table: Bin <- dbGetQuery(con, "SELECT [Code], [Receive], [Ship], [Put Away], [Pick], [timestamp] FROM [DM8$Bin Type]")I suspect timestamp field to be the problem here, as this field is present in every table and I cant load any table using select * statement. Timestamp here has nothing to do with actual date, it is more of rowversion and it looks like this: "0x00000000000007D3" more information here |
|
Unfortunately there doesn't seem to be a way for Applications to fix this, it is really a Driver bug / feature. The only workaround I am aware of is to put these columns at the end of the query. |
|
I can see this as nothing other than deeply saddening for the state of database access in R. Reliance on a "standard" (ODBC) that hand-cuffs SQL so that the query-er must possess insider knowledge of the table's column constructs so that ordering the fields must be done according to a strict set of rules ... ODBC does not appear to give any indication that there will be a problem. If you look at @jimhester, is there hope for order-agnostic queries against ODBC data sources? I can make a query against a table in (say) postgres that works, but in sqlserver it fails due solely to the order of columns. This (to my understanding of SQL) defeats the "simple" of SQL as well as the assumption of "standard". I can (and do) translate some things between DBMS's, such as |
|
Certain drivers fix this, e.g. the RStudio professional drivers and those from Simba handle this situation transparently to the odbc package. I don't know of a way to do this automatically in the odbc package, but perhaps it could be done either here or in nanodbc. |
|
@jimhester, I've re-written this comment about seventeen times, and each time I delete it because it feels like I'm just complaining (and you are not the problem). RStudio's fix is both encouraging (that a fix/workaround exists) and frustrating (linux only, and my reliable-data-access is effectively behind a ransom-wall). Though this really should be fixed in ODBC itself, since MS has done nothing over the last decade the bug has been clearly documented, it needs to be done in upper levels. Nanodbc disagrees that it is theirs to fix. I'm interested in finding a way to fix this somewhere, inviting nanodbc to use similar techniques to address the problem there. Unfortunately, I'm not familiar enough with nanodbc low-level functions or the ODBC protocol to find enough info to do this. Specifically, I can find no way to know that this will be a problem. For instance: library(DBI)
con <- dbConnect(odbc::odbc(), ...)
dbExecute(con, "create table tbl1 ( id BIGINT PRIMARY KEY NOT NULL, s1 varchar(32), s2 varchar(max) )")
dbExecute(con, "insert into tbl1 values (1,'a','a'),(2,'b','b')")
ret <- dbGetQuery(con, "select s1,s2 from tbl1") # works
dbGetQuery(con, "select s2,s1 from tbl1") # fails
# Error in result_fetch(res@ptr, n, ...) :
# nanodbc/nanodbc.cpp:2836: 07009: [Microsoft][ODBC Driver 13 for SQL Server]Invalid Descriptor Index
res <- dbSendStatement(con, "select s2,s1 from tbl1")
ci <- dbColumnInfo(res)
ci
# name type
# 1 s2 12
# 2 s1 12
dbClearResult(res)I don't know, given that Once that's been identified, what can we do with that information? Is there a way to rearrange the columns post- I'm willing to do a bit of foot-work with this, but (1) it might need to be a new issue? (2) Can you provide some pointers on how to "play" around the column-metadata, possible reordering, or something else? |
|
I'm doing some testing, and I thought I'd try modifying while (!complete_) {
...
for (size_t col = 0; col < types.size(); ++col) {
if (r.column_size(col) == 0) continue;
...
}
for (size_t col = 0; col < types.size(); ++col) {
if (r.column_size(col) != 0) continue;
...
}
}but it's still failing. With debugging enabled, I've confirmed that on each row, it calls the "not-large" columns first and then all "large" columns in-order, but on the first row it still errs: I suspect that we need to be including |
|
Interesting ... For the record, I get this error with drivers "ODBC Driver 13 for SQL Server" and "SQL Server Native Client 11.0", but not with "SQL Server". So the driver that I'm inferring is older (because of the older |
|
Where do I get the older |
|
@retorquere, you might consider using the FreeTDS driver. I've successfully tested it on Windows and linux. The pros/cons so far: Pros:
Cons:
Update (Sep 2020): since I wrote this, I've tried FreeTDS on-and-off. Recently, using emacs-27.1, R-4.0.2, in Emacs/ESS on win10, it (somehow) eventually freezes emacs after several minutes, and it is affecting the whole system. If I had to guess, it's a memory leak that bogs everything else to a crawl. When I use Microsoft's ODBC driver, I get no instability. |
|
@r2evans thanks. The pool package is not much of an issue for us, and since we're using it for fairly simple RMarkdown documents the performance hit is likely tolerable, but most of my colleagues are on Windows, and we're connecting (a.o.) to Azure databases; I can't imagine Azure would allow non-secured connections. |
|
(but to test: how would I set up a FreeTDS connection? I'm on macOS myself) |
|
I don't know how to manage odbc drivers on macos, sorry. I was only able to find FreeTDS' appveyor jobs, which is how I found the windows binaries for installation, I wonder if there's an analog for macos. Once you have that, though, you use it just like you would another driver, using the Frankly, if |
|
@retorquere, I would be very interested if somebody figures out how to get the |
|
I haven't seen anything that would indicate we can do this. |
@r2evans I thought this problem does NOT occur when using RStudio's commercial drivers?See Jim Hester's comment Specifically, this statement:
|
|
First, the comment that suggests FreeTDS is going to have problems with "long data" because it doesn't implement his As to using RStudio pro drivers ... I had been working for way too long before those drivers were available to me (before I purchased RSC), that switching was effectively not the immediate priority. And the fact that those drivers are not for Windows means that I'm out of luck: RStudio pro drivers do not support Windows. Joy. |
|
To give you some context behind my questions, I am writing about the problem and solutions in this WIP article: Debugging the Invalid Descriptor Index Error. In regards to this statement...
The Requirements for RStudio Professional Drivers page does say they support Windows. If this isn't a viable solution for Windows users, I'd like to update my post. I'm curious, have you attempted to use the SQL driver and found it didn't work on Windows? |
|
I thought it used to say "windows not supported", but effectively the same for me:
I have RSC, not RStudio Desktop Pro. I tried downloading the (1.2GB!) tarfile anyway, but no DLLs. |
I found similar issue with Invalid Descriptor Index error, and the issue was with column type varchar(max), but I cant find this type of column in my case (see attached picture), using select * statement I cant load any Navision table.
Example:
Result:
However, selecting certain columns, query returns data.frame. RODBC is working without an issue, I really want switch to ODBC for additional features.
Session Info
The text was updated successfully, but these errors were encountered: