Error when using SQLSetStmtAttr before SQLExecute on Azure Server

Question

Error when using SQLSetStmtAttr before SQLExecute on Azure Server

Nic Thomas 21

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);    
   }    
}

Pierre-Luc Giguere 1,066 Reputation points

2021-11-12T14:16:15.627+00:00

Hi Nic,

Welcome to the community.

Disclaimer: I am not a SQL guru.

Did you try explicitly setting SQL_SOPT_SS_DEFER_PREPARE ?

https://learn.microsoft.com/en-us/sql/relational-databases/native-client-odbc-api/sqlsetstmtattr?view=sql-server-ver15#sql_sopt_ss_defer_prepare

SQL_DP_ON Default. After calling SQLPrepare Function, the statement preparation is deferred until SQLExecute is called or metaproperty operation (SQLDescribeCol or SQLDescribeParam) is executed.

I would still explicitly set it to ON just because of the behavior you describe.
Viorel 89,061 Reputation points

2021-11-12T18:40:48.167+00:00

Does it work if you specify 1 instead of 2?

At which another location does SQLSetStmtAttr work and why is it needed at current location?
Nic Thomas 21 Reputation points

2021-11-15T10:22:27.227+00:00

Hi Pierre,

I tried setting SQL_SOPT_SS_DEFER_PREPARE to SQL_DP_ON but it did not solve my problem.
Nic Thomas 21 Reputation points

2021-11-15T10:27:43.07+00:00

Yes it works if i set it to 1, but 1 is the default value and I wanted to improve the perfomance so this does not help.
SQLSetStmtAttr works if I set it after SQLPrepare but if i wanted to change the location in my legacy code it would require wayyy to much work.

Pierre-Luc Giguere 1,066 Reputation points

2021-11-12T14:16:15.627+00:00

Hi Nic,

Welcome to the community.

Disclaimer: I am not a SQL guru.

Did you try explicitly setting SQL_SOPT_SS_DEFER_PREPARE ?

https://learn.microsoft.com/en-us/sql/relational-databases/native-client-odbc-api/sqlsetstmtattr?view=sql-server-ver15#sql_sopt_ss_defer_prepare

SQL_DP_ON Default. After calling SQLPrepare Function, the statement preparation is deferred until SQLExecute is called or metaproperty operation (SQLDescribeCol or SQLDescribeParam) is executed.

I would still explicitly set it to ON just because of the behavior you describe.
Viorel 89,061 Reputation points

2021-11-12T18:40:48.167+00:00

Does it work if you specify 1 instead of 2?

At which another location does SQLSetStmtAttr work and why is it needed at current location?
Nic Thomas 21 Reputation points

2021-11-15T10:22:27.227+00:00

Hi Pierre,

I tried setting SQL_SOPT_SS_DEFER_PREPARE to SQL_DP_ON but it did not solve my problem.
Nic Thomas 21 Reputation points

2021-11-15T10:27:43.07+00:00

Yes it works if i set it to 1, but 1 is the default value and I wanted to improve the perfomance so this does not help.
SQLSetStmtAttr works if I set it after SQLPrepare but if i wanted to change the location in my legacy code it would require wayyy to much work.

Answer 1

Erland Sommarskog 72,421 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.

Nic Thomas 21 Reputation points

2021-11-19T07:41:21.73+00:00

Thank you for your research, as stated above, we have a legacy application and this doesn't seem to be the only attribute which results in this error. We can't simply skip it, because we would need to restructure way too much of the underlying code base. Since this feature is only in a preview state, do you think it will work in the future ?
Erland Sommarskog 72,421 Reputation points MVP

2021-11-19T16:27:05.927+00:00

I brought this up with a contact at Microsoft who confirmed that cursor over external tables are not supported.

I did not ask about whether this would be changed in the future, but I would not hold my breath.

You say that this is a legacy application, but apparently you are making some changes, since you are introducing external tables. In any case, I think you will need to figure out a plan B.
Nic Thomas 21 Reputation points

2021-11-22T08:10:33.36+00:00

Thank you very much for everything you have done so far. Could you maybe suggest to your contact at Microsoft that the MSDN documentation for the external tables gets an addition so that it is clear what works and what doesn't ?
Erland Sommarskog 72,421 Reputation points MVP

2021-11-22T22:29:19.047+00:00

Actually, you can do this yourself. On the top right of the page (like on all Docs pages), you can see this:

The second icon from the left permits you to submit feedback on the document on Github and the third is even braver: here you can edit the document and create a pull request.

I am not sure if the restrictions should be listed on this page, but I agree that at least there should be a note and reference to limitations.
Nic Thomas 21 Reputation points

2021-11-23T14:36:44.833+00:00

Okay thank you @Erland Sommarskog , I submitted a feedback on Github. If you want to read it you can click here.

Answer 2

Erland Sommarskog 72,421 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.)

Nic Thomas 21 Reputation points

2021-11-16T09:14:47.833+00:00

Hi @Erland Sommarskog ,
I know the SQL Statement which causes this error, I am searching for a way to fix it. So unfortunally this does not help me with my cause.
Would it help you to know the event Log ? It could be a bug because the feature is in a preview state, do you know anything about this ?
Erland Sommarskog 72,421 Reputation points MVP

2021-11-16T22:07:42.537+00:00

Well, if you know that SQL, why don't you share it with us?

Note that the SQL I have in mind is not the SQL statement you have in the code. When you provide that statement attribute, I guess ODBC wraps things around it. That's what I'm interested in.
Nic Thomas 21 Reputation points

2021-11-17T12:04:39.207+00:00

150137-events.xml

In the file you can find the two Events which are related to my programm. Is that what you need ?
By the way, it does not matter which sql statement I try to execute, it fails every time. (Different Rows, Different Tables ...)

Update:
These are failing statement (depending on some settings):

declare @p1 int
set @p1=0
declare @p3 int
set @p3=16388
declare @p4 int
set @p4=8193
declare @p5 int
set @p5=0
exec sp_cursoropen @p1 output,N'select city from bank',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

declare @p1 int
set @p1=NULL
declare @p2 int
set @p2=0
declare @p5 int
set @p5=16388
declare @p6 int
set @p6=8193
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,NULL,N'select city from bank',@p5 output,@p6 output,@p7 output
select @p1, @p2, @p5, @p6, @p7

And this is the succeding statement:

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,NULL,N'select city from bank'
select @p1

Answer 3

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.

Nic Thomas 21 Reputation points

2021-11-15T10:21:03.92+00:00

Hi @YufeiShao-msft ,

i'm using the newest Version of Azure SQL (I could not find a Version Number).
I tried running "update statistics" and it executed succesfully but did not solve my problem.

Error when using SQLSetStmtAttr before SQLExecute on Azure Server

2 additional answers

Activity