Very long query execution using C++, Sql Server and ODBC connection

Question

I'm using the following code to connect to a sql-server database. I can run all other queries with no difficulties using the same function. However, one query executes in Sql management studio and in R using RODBC in ~11 seconds but takes over an hour (at the "SQLExecDirect" function) using c++. Has anyone else had this issue and how did you resolve it?

std::string sqlQuery="[myquery]";
SQLHANDLE sqlconnectionhandle;
SQLAllocHandle(SQL_HANDLE_DBC, sqlenvhandle, &sqlconnectionhandle);
SQLCHAR retconstring[1024];
SQLDriverConnect (sqlconnectionhandle, NULL, 
        conn_str, 
        len, retconstring, 1024, NULL,SQL_DRIVER_NOPROMPT);
SQLHANDLE sqlstatementhandle;
SQLAllocHandle(SQL_HANDLE_STMT, sqlconnectionhandle, &sqlstatementhandle);
//this is where the program "hangs" for over an hour 
if(SQL_SUCCESS!=SQLExecDirect(sqlstatementhandle, (SQLCHAR*)(sqlQuery.c_str()), SQL_NTS)){ 
    show_error(SQL_HANDLE_STMT, sqlstatementhandle, errorMsg);
    return;
}
int numRow=0;
while(SQLFetch(sqlstatementhandle)==SQL_SUCCESS){    
    for(int i=1; i<=numFields+1; ++i){
        double myVal;
        SQLGetData(sqlstatementhandle, i, SQL_C_DOUBLE, &myVal, 0, NULL);
        cb(myVal, numRow, i-1); //callback function defined elsewhere
    }
    numRow++;
}
SQLFreeHandle(SQL_HANDLE_STMT, sqlstatementhandle );
SQLDisconnect(sqlconnectionhandle);
SQLFreeHandle(SQL_HANDLE_DBC, sqlconnectionhandle);

Answer 1

That is because the ArithAbort property on your database is off. See also http://www.sommarskog.se/query-plan-mysteries.html

You can check it and correct it with this small script

declare @value sql_variant 
select @value = SESSIONPROPERTY('ARITHABORT') 
if @value <> 1 
begin 
    USE master 
    ALTER DATABASE [your_database] SET ARITHABORT ON WITH NO_WAIT
    use your_database
end