Error when using SQLSetStmtAttr before SQLExecute on Azure Server

Nic Thomas 21 Reputation points

I have an Azure Database Server with two databases. I hooked up the second table as an external table to the first table like shown here.(

The Problem:

When I now try to select from the second table, with ODBC, I get the SQLState: 4200 and the NativeError: 8624 with the message: "The query processor could not produce a query plan. For more information, contact Customer Support Services."

BUT this only happens when I perform SQLSetStmtAttr before the SQLExecute Statement.
Any Ideas how I can avoid this error while still performing SQLSetStmtAttr before the SQLExecute Statement?

I have a legacy application and would not like to move the SQLSetStmtAttr to another location.


#include <windows.h>    
#include <sqlext.h>  
#include <string>  
#include <iostream>  
int main() {    
   SQLHENV henv;    
   SQLHDBC hdbc;    
   SQLHSTMT hstmt;    
   SQLRETURN retcode;    
   SQLCHAR OutConnStr[255];    
   SQLSMALLINT OutConnStrLen;    
   HWND desktopHandle = GetDesktopWindow();  
   retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);    
   if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {    
      retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0);     
      if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {    
         retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);  
         if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {    
            SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);    
            retcode = SQLDriverConnect(  
               (SQLCHAR*)"DSN=[ODBC Servername];Uid=[Username];Pwd=[Password];Database=Databasename;",     
               _countof("DSN=[ODBC Servername];Uid=[Username];Pwd=[Password];Database=Databasename;"),    
               SQL_DRIVER_PROMPT );    
            if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {                   
               retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);  
            std::string statement = "select name FROM <ExternalTable>";  
            SQLSetStmtAttr (hstmt, SQL_ATTR_ROW_ARRAY_SIZE,reinterpret_cast <SQLPOINTER> (static_cast <SQLULEN> (2)), sizeof 2);  
            SQLPrepare (hstmt, (SQLCHAR *), sql.length());  
            SQLRETURN excecuteRetCode = SQLExecute(hstmt);     
            if (excecuteRetCode == SQL_SUCCESS || excecuteRetCode == SQL_SUCCESS_WITH_INFO)  
                int i = 0;  
            else {  
                SQLLEN numRecs = 0;  
                SQLGetDiagField(SQL_HANDLE_STMT, hstmt, 0, SQL_DIAG_NUMBER, &numRecs, 0, 0);  
                int i = 1;  
                std::string msg;  
                msg.reserve (SQL_MAX_MESSAGE_LENGTH);  
                SQLRETURN   diagRecReturn;  
                SQLSMALLINT msgLen;  
                SQLCHAR     SqlState[6];  
                SQLINTEGER  NativeError;    
                while (i <= numRecs && (diagRecReturn = SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, i, SqlState, &NativeError,    
                         (SQLCHAR *) (), msg.capacity (), &msgLen)) != SQL_NO_DATA)  
               if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {    
                  SQLFreeHandle(SQL_HANDLE_STMT, hstmt);    
            SQLFreeHandle(SQL_HANDLE_DBC, hdbc);    
      SQLFreeHandle(SQL_HANDLE_ENV, henv);    
Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,128 questions
A high-level, general-purpose programming language, created as an extension of the C programming language, that has object-oriented, generic, and functional features in addition to facilities for low-level memory manipulation.
2,756 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 72,421 Reputation points MVP

    I was able to repeat this with an external table. For a table located in the database, there is no issue.

    I'm tempted to say that it is as simple that cursors are not supported with external tables. Then again, I was able to run an SQL-side cursor over the table. But that does not prove that API cursors should work.

    But I don't really see why you would want to run a cursor over the table. I wold simply skip the statement attribute.

2 additional answers

Sort by: Newest
  1. Erland Sommarskog 72,421 Reputation points MVP

    You have "select name FROM <ExternalTable>". So this is a table elsewhere you have created a link to with CREATE EXTERNAL TABLE. It may be that what you are trying to do does not work with external tables, but that is just speculation on my part.

    I think it would help to see the actual SQL that is produced. Unfortunately, since you are on Azure SQL Database, this is somewhat cumbersome. But open the server in Object Explorer, and navigate to the database then then to Extended Events. You should find one session ADS_Standard_Azure. Right-click it and select Start. Expand the node, and select package0.ring_buffer. Right-click and select View target data. A window with a one row XML object will open. There is a blue bar above it.

    At this point run the application, so that you get this error. Now right-click the blue bar, and select Refresh. Now right-click the XML so that a new Window opens where the XML is displayed in a more readable way. Use the Find function to search for your table.

    (No, that is certainly not a very good user experience.)

    1 person found this answer helpful.

  2. YufeiShao-msft 6,971 Reputation points

    Hi @Nic Thomas ,

    What the version of SQL Server? Please consider the issue of database compatibility

    maybe you can try to run “update statistics”


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.