Error when using SQLSetStmtAttr before SQLExecute on Azure Server

Nic Thomas 21 Reputation points
2021-11-12T10:31:38.197+00:00

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.(https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-query-vertical-partitioning)

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.

Code:

#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(  
               hdbc,     
               desktopHandle,     
               (SQLCHAR*)"DSN=[ODBC Servername];Uid=[Username];Pwd=[Password];Database=Databasename;",     
               _countof("DSN=[ODBC Servername];Uid=[Username];Pwd=[Password];Database=Databasename;"),    
               OutConnStr,    
               255,     
               &OutConnStrLen,    
               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.data(), 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.data (), msg.capacity (), &msgLen)) != SQL_NO_DATA)  
                {    
                   i++;  
                }    
            }  
               if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {    
                  SQLFreeHandle(SQL_HANDLE_STMT, hstmt);    
               }    
    
               SQLDisconnect(hdbc);    
            }    
    
            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.
8,586 questions
C++
C++
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,637 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 68,536 Reputation points MVP
    2021-11-17T22:28:27.76+00:00

    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: Most helpful
  1. YufeiShao-msft 6,871 Reputation points
    2021-11-15T07:32:54.077+00:00

    Hi @Nic Thomas ,

    What the version of SQL Server? Please consider the issue of database compatibility
    https://social.msdn.microsoft.com/Forums/en-US/59c0e1e0-741d-48c9-9eaf-38b5cc1f57d3/error-8624-internal-query-processor-error-the-query-processor-could-not-produce-a-query-plan?forum=sqldatabaseengine

    maybe you can try to run “update statistics”
    https://learn.microsoft.com/en-us/archive/blogs/mdegre/8624-internal-query-processor-error-the-query-processor-could-not-produce-a-query-plan

    -------------

    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.


  2. Erland Sommarskog 68,536 Reputation points MVP
    2021-11-15T22:33:47.53+00:00

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