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

Question

I have created an MS SQL 2012 data base using enterprise archiect and I am in the process of uploading data to this server using R and the dbi and odbc packages. Now, I have problem reading a table from my MS SQL 2012 sever, that I need to merge with another table that has a FK constraint to this table. What frustrates me is, that I can download the empty table, but not the filled table that I appended before.

Here is a picture of the EA data base modelling:

There is a main table "unternehmen" with information about firms that leads to a secondary table "eigentumsverhaeltnisse" with further information and one information is linked to a meta table "eigentuemer" containing the labels of this information. (I do not necessarily need this, but there are similar situations elsewhere). The entire data base (rougly 100 tables) is created using the DDL generation of enterprise architect. I could add more information on this, if needed.

So I created the meta table manually and uploaded it to the server using this code:

con <- DBI::dbConnect(odbc::odbc(),
                      Driver = "SQL Server",
                      Server = "MY server", 
                      Database = "EA_DB",
                      encoding = "latin1")
df_temp<-data.table(bezeichnung=c("Land", … , "Staat"))
DBI::dbWriteTable(con, “eigentuemer”, df_temp, append=TRUE)

There is a bit more code to create the secondary table, which I then merge with the meta table after loading it from the SQL server to include the FK. This is then also uploaded using the same code as above.

df_temp <- code to create the other table 
df_temp_sql<-DBI::dbReadTable(con, “eigentuemer”)
df_temp<-merge(df_temp,df_temp_sql,by="bezeichnung")
some other code
DBI::dbWriteTable(con, “eigentumsverhaeltnisse“, df_temp, append=TRUE)

Now I cannot reload the previously appended table, which is really frustrating and I was able to use the same command when the table was empty.

df_temp_sql<-DBI::dbReadTable(con, “eigentumsverhaeltnisse“,)
Error in result_fetch(res@ptr, n) : 
  nanodbc/nanodbc.cpp:2966: 07009: [Microsoft][ODBC SQL Server Driver]Ungültiger Deskriptorindex

I am assuming „ungültiger Deskriptorindex means invalid descriptor index. The table exists

DBI::dbExistsTable(con, "eigentumsverhaeltnisse")

I have found simliar questions, but I did not see a solution for me I tried different

df_temp_sql_4<-DBI::dbReadTable(con,DBI::SQL("eigentumsverhaeltnisse"))
df_temp_sql_5<-DBI::dbReadTable(con,"dbo.eigentumsverhaeltnisse")
df_temp_sql_6<-DBI::dbReadTable(con,DBI::SQL("dbo.eigentumsverhaeltnisse"))

I also tried dgGetQuery but get the same error

Bin <- DBI::dbGetQuery(con, "SELECT [id], [konzernname], [eigentuemer_id], [eigentuemer_andere], 
                          [weitere_geschaeftsfelder], [konzernteil] FROM [EA_DB].[dbo].[eigentumsverhaeltnisse]")
Error in result_fetch(res@ptr, n) : 
      nanodbc/nanodbc.cpp:2966: 07009: [Microsoft][ODBC SQL Server Driver]Ungültiger Deskriptorindex

Similar questions are: Import Tables from SQL Server into R dbReadTable error in R: invalid object name dbReadTable error in R: invalid object name

Edit to answer comment: I do not quite understand the issue it the link. I do not have varchar(max) or varbinary(max) variable, do I? I have

PK id:bigint
   konzername:ntext
FK eigentuemer_id:bigint
   eigentuemer_andere:ntext
   weitere_geschaftsfelder:bit
   konzernteil:bit

What is strang is that some DBI::dbGetQuerycommands work when I only include a subset of the columns and some dont.

#Works
Bin <- DBI::dbGetQuery(con, "SELECT [id], [konzernname], [eigentuemer_andere] FROM [EA_DB].[dbo].[eigentumsverhaeltnisse]")
head(Bin)
#works as well
Bin <- DBI::dbGetQuery(con, "SELECT [id],  [eigentuemer_id] FROM [EA_DB].[dbo].[eigentumsverhaeltnisse]")
head(Bin)
#Does not work 
Bin <- DBI::dbGetQuery(con, "SELECT [id], [konzernname], [eigentuemer_andere], [eigentuemer_id] FROM [EA_DB].[dbo].[eigentumsverhaeltnisse]")
#works as well
Bin <- DBI::dbGetQuery(con, "SELECT [id],  [weitere_geschaeftsfelder] FROM [EA_DB].[dbo].[eigentumsverhaeltnisse]")
head(Bin)
#Does not work
Bin <- DBI::dbGetQuery(con, "SELECT [id], [konzernname], [eigentuemer_andere], [weitere_geschaeftsfelder] FROM [EA_DB].[dbo].[eigentumsverhaeltnisse]")

Could this be the issue - https://github.com/r-dbi/odbc/issues/10? It trips me up on occasions — Hearkz, Apr 15 '20 at 09:39
thx @Hearkz i edited the questions I am not sure i understand the link to my problem — Max M, Apr 15 '20 at 10:00
there is no varchar(max) column in your table but there is an ntext column (which is also deprecated, better use nvarchar(max)). Still, the problem/bug occurs when a (max),text column is not the last column in the result. — lptr, Apr 15 '20 at 10:17
...and if you want to store foreign characters in "eigentuemer_andere" with no more than 4000 chars then you could change the datatype to nvarchar(4000) or to varchar(8000) for latin chars only and avoid the bug. — lptr, Apr 15 '20 at 10:22
Ok so I will try this. I change all the ntext vars into nvarchar(400) that should be enough for the time being. What do you mean by the ntext column is deprecated? Can I try anything else? Those fields are comment fields and thats why my consultant suggested ntext — Max M, Apr 15 '20 at 10:24
IMPORTANT! ntext, text, and image data types will be removed in a future version of SQL Server. : https://learn.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql?view=sql-server-ver15 — lptr, Apr 15 '20 at 10:29
Ok so using nvarchar(400) worked. So to clarify this for me as soon as I have two ntext or nvarchar(max) variables in my SQL table, I cannot query that table anymore? Or if I have only one nvarchar(MAX) field, I need to put it at the end of the table? — Max M, Apr 16 '20 at 06:50

score 1 · Accepted Answer · answered Apr 17 '20 at 09:26

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

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

1 Answers1