[Microsoft][SQL Native Client] Invalid Descriptor Index, error 07009

I was doing some work with native ODBC through the incredibly useful and excellent ODBC-wrapper Nanodbc. In theory, it should be simple work. However, when things can go wrong, they will go horribly wrong.

The task was to extend the number of columns retrieved from a view in SQL Server. Simple enough. The view can be described as a mix between ints, longs and nvarchar(max) fields. The actual order is not important, except nvarchar(max) wasn’t the last field.

Being the good citizen (programmer) I was, I decided to order the fields retrieved from the database with the order in the view, and fetch data in the order requested.

That did not go unpunished. I was stuck getting exceptions from Nanodbc, which kept repeating Invalid Descriptor Index again and again, no matter what I did.

I was extending the view to retrieve data in the order of from:

1. A bigint
2. B nvarchar(max)

To:

1. A bigint
2. B bigint
3. C nvarchar(max)
4. D int
5. E bigint
6. F nvarchar(50)

The retrieval order in SQL query was identical to the order presented in the view, and identical retrieval order in the code.

Some ODBC drivers doesn’t like that. They like putting the nvarchar(max) fields at the end without telling anyone.

The solution was to change the retrieval order in the code, so the code requested the nvarchar(max) fields last.

With the example above, the order must be:

nanodbc::result results;

/* fetch results */ 

while (results.next())
{
    int64_t a, b, e;
    int32_t d;
    std::string c, f;
    
    results.get_ref("A", a);  // bigint
    results.get_ref("B", b);  // bigint
    results.get_ref("D", d);  // int
    results.get_ref("E", e);  // bigint
    results.get_ref("F", f);  // nvarchar(50)
    results.get_ref("C", c);  // nvarchar(max) MUST BE AT END
}

Having any other fields after C, other than other nvarchar(max) fields will fail.