SQL Server ODBC Driver not raising errors

Question

This question is part in a series of bugs in the Microsoft ODBC driver:

• ODBC driver fails to raise errors; but instead suppresses them
• Reading columns out of order returns incorrect results
• Cannot execute a stored procedure that is a SYNONYM

Microsoft has said they will not be fixing these bugs in their ODBC driver.

Background

If i have a sample table:

CREATE TABLE Wallet (
    WalletID int NOT NULL,
    Name varchar(50) NOT NULL
)

i attempt to issue sql that inserts into the table without specifying a value to the NOT NULL WalletID column:

INSERT INTO Wallet (WalletID, Name) VALUES (1, 'Fan')
INSERT INTO Wallet (Name) VALUES ('Ardent Defender') --Constraint violation

SQL Server gives an error:

(1 row(s) affected)
Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'WalletID', table 'Scratch.dbo.Wallet'; column does not allow nulls. INSERT fails.
The statement has been terminated.

And that makes sense.

When connecting using ADO/OLEDB, and the SQL Server OLE DB Provider (SQLOLEDB):

Provider=SQLOLEDB;Data Source=hyperion;User ID=Contoso;Password=Trub4dor;

And i execute the the INSERT, the ADO/OLEDB/COM infrastructure returns a failure, which comes back to the high level language as an exception:

Cannot insert the value NULL into column 'WalletID', table 'Wallet'; column does not allow nulls. INSERT fails

And that all makes sense.

But try it with the ODBC driver

With the derprication of that native client OLE DB providers (and the MS recommendation that you do not use the native client ODBC drivers), i thought i would try my hand at using the SQL Server ODBC driver:

Provider=MSDASQL;Driver={SQL Server};Server={hyperion};UID={Contoso};PWD={Trub4dor};

---

Update - Undeprecated: Six years later, Microsoft has announced the un-deprecation of OLE DB support for SQL Server, and the creations of a third OLE DB driver for SQL Server: msoledbsql. (archive)

Previously, Microsoft announced deprecation of the Microsoft OLE DB Provider for SQL Server, part of the SQL Server Native Client (SNAC). At the time, this decision was made to try to provide more simplicity for the developer story around Windows native software development as we moved into the cloud era with Azure SQL Database, and to try to leverage the similarities of JDBC and ODBC for developers. However, during subsequent reviews it was determined that deprecation was a mistake because substantial scenarios within SQL Server still depend on OLE DB and changing those would break some existing customer scenarios.

With this in mind, we have decided to undeprecate OLE DB and release a new version by the first quarter of calendar year 2018 March 2018.

---

I issue my batch:

INSERT INTO Wallet (WalletID, Name) VALUES (1, 'Fan')
INSERT INTO Wallet (Name) VALUES ('Ardent Defender')

I was surprised to learn that same SQL statement:

• that triggers an INSERT FAILS error in SQL Server itself:

• that results in a client side error when using the SQL Server OLE DB Provider

will silently fail when using the ODBC driver. The statement executes without any error being raised.

I was confused for about an hour when my SQL statements ran without error, but the rows would not appear in the database.

Silent Fail

Obviously a silent failure is no good.

• And obviously i can just not use the SQL Server ODBC driver,
• and continue to use the Microsoft OLE DB Provider for SQL Server provider.

But what is going on?

How do i tell the ADO-OLEDB-ODBC driver to report errors. Is it an ODBC connection string setting? Is it an MSDASQL connection string setting?

Sample Psuedocode

I'm actually using Delphi with ADO. But i'll transcode it into pseudo C# style code for easier conceptual understanding.

String commandText = 
      "INSERT INTO Wallet (WalletID, Name) VALUES (1, 'Fan')"+CRLF+
      "INSERT INTO Wallet (Name) VALUES ('Ardent Defender')";

ADOConnection conn = new ADOConnection();
conn.ConnectionString = szConnectionString;
conn.Open();

HRESULT hr = conn.Execute(commandText, ref recordsAffected, [eoExecuteNoRecords]);

In reality the checking of the HRESULT is handled by the language infrastructure and compiler magic - throwing a native language exception if it FAILED.

Gravitt

Is the problem that you have not set up the command to receive the error message? – Scrivner

Can you post the app code snippet? I cannot repro. – Unreel

Please show some minimal code. I have test-cases that explicitly test failures like this and everything works as expected (using sql server 2014 and the native client driver, with c++ on windows). – Flocculant

@Flocculant Figured out the important piece: it has to be multiple statements in the batch, with a subsequent statement failing. – Gravitt

@DanGuzman I found the missing trick to making the contrived test fail: multiple statements where the first statement doesn't fail. – Gravitt

@Ian Boyd: Interesting, I can reproduce this in c++. I have updated my answer with a workaround to at least get an error as response. Maybe this could be of use for you. – Flocculant

Answer 1

SQL Server returns DONE_IN_PROC messages to the client after each statement unless you specify SET NOCOUNT ON. These messages (row counts) will affect ADO applications that are not coded to handle the multiple record sets returned by calling the NextRecordset method.

The easy solution is to specify SET NOCOUNT ON as the first statement in the batch or stored procedure.

Unreel

Indeed, this seems to be the correct way of executing multiple statements. A quick tests shows that if SET NOCOUNT ON is set, execution of the statement will fail with the correct error message being reported. – Flocculant

If that's the case; why does it work with the OLEDB driver, which also sends back the "count" recordset after each statement. – Gravitt

Answer 2

I did not read your question carefully, therefore my comment referred to the wrong ODBC driver.

Anyway, I tested it using three different ODBC drivers against SQL Server 2014:

• SQL Server Native Client 11.0
• ODBC Driver 11 for SQL Server
• SQL Server

Updated: With all three drivers, if I execute only one insert, I get the expected error in the form:

ERROR; native: 515; state: 23000; msg: [Microsoft][ODBC SQL Server Driver][SQLServer]Cannot insert the value NULL into column 'WalletID', table 'Test.dbo.Walle t'; column does not allow nulls. INSERT fails. ERROR; native: 3621; state: 01000; msg: [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

If I execute multiple inserts in one statement, and the first insert does not fail, no error is raised, as mentioned in the comment by Ian Boyd.

Workaround: Turn of auto-commit, but enable manual commit. Execution of the multiple inserts will still not fail, but committing the transaction will raise an error. Unfortunately, I was not able to retrieve any error information, but at least you get an SQL_ERROR as result when trying to commit.

Here is the updated sample code:

#include <windows.h>
#include <tchar.h>
#include <iostream>
#include <sql.h>
#include <sqlext.h>
#include <sqlucode.h>
#include <functional>

void printErr(SQLHANDLE handle, SQLSMALLINT handleType)
{
    SQLSMALLINT recNr = 1;
    SQLRETURN ret = SQL_SUCCESS;
    while (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
    {
        SQLWCHAR errMsg[SQL_MAX_MESSAGE_LENGTH + 1];
        SQLWCHAR sqlState[5 + 1];
        errMsg[0] = 0;
        SQLINTEGER nativeError;
        SQLSMALLINT cb = 0;
        ret = SQLGetDiagRec(handleType, handle, recNr, sqlState, &nativeError, errMsg, SQL_MAX_MESSAGE_LENGTH + 1, &cb);
        if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
        {
            std::wcerr << L"ERROR; native: " << nativeError << L"; state: " << sqlState << L"; msg: " << errMsg << std::endl;
        }
        ++recNr;
    }
}


int _tmain(int argc, _TCHAR* argv[])
{
    SQLHSTMT stmt = SQL_NULL_HSTMT;

    SQLRETURN   nResult = 0;
    SQLHANDLE   handleEnv = 0;

    nResult = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, (SQLHANDLE*)&handleEnv);
    nResult = SQLSetEnvAttr(handleEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3_80, SQL_IS_INTEGER);

    SQLHANDLE   handleDBC = 0;
    nResult = SQLAllocHandle(SQL_HANDLE_DBC, handleEnv, (SQLHANDLE*)&handleDBC);

//  SQLWCHAR     strConnect[256] = L"Driver={SQL Server Native Client 11.0};Server=.\\TEST;Database=Test;Trusted_Connection=yes;";
//  SQLWCHAR     strConnect[256] = L"Driver={ODBC Driver 11 for SQL Server};Server=.\\TEST;Database=Test;Trusted_Connection=yes;";
    SQLWCHAR     strConnect[256] = L"Driver={SQL Server};Server=.\\TEST;Database=Test;Trusted_Connection=yes;";
    SQLWCHAR     strConnectOut[1024] = { 0 };
    SQLSMALLINT nNumOut = 0;
    nResult = SQLDriverConnect(handleDBC, NULL, (SQLWCHAR*)strConnect, SQL_NTS, (SQLWCHAR*)strConnectOut, sizeof(strConnectOut),
        &nNumOut, SQL_DRIVER_NOPROMPT);
    if (!SQL_SUCCEEDED(nResult))
    {
        printErr(handleDBC, SQL_HANDLE_DBC);
    }

    // Enable manual commit
    nResult = SQLSetConnectAttr(handleDBC, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0);
    if (!SQL_SUCCEEDED(nResult))
    {
        printErr(handleDBC, SQL_HANDLE_DBC);
    }

    SQLHSTMT    handleStatement = 0;
    nResult = SQLAllocHandle(SQL_HANDLE_STMT, handleDBC, (SQLHANDLE*)&handleStatement);
    if (!SQL_SUCCEEDED(nResult))
    {
        printErr(handleDBC, SQL_HANDLE_DBC);
    }

    // try to drop, ignore if it exists
    SQLExecDirect(handleStatement, L"DROP TABLE Wallet", SQL_NTS);

    nResult = SQLExecDirect(handleStatement, L"CREATE TABLE Wallet (WalletID int NOT NULL,  Name varchar(50) NOT NULL)", SQL_NTS);
    if (!SQL_SUCCEEDED(nResult))
    {
        printErr(handleStatement, SQL_HANDLE_STMT);
    }

    //nResult = SQLExecDirect(handleStatement, L"INSERT INTO Wallet (WalletID, Name) VALUES (2, 'Fan') INSERT INTO Wallet (WalletID, Name) VALUES (1, 'Fan')", SQL_NTS);
    nResult = SQLExecDirect(handleStatement, L"INSERT INTO Wallet (WalletID, Name) VALUES (2, 'Fan') INSERT INTO Wallet (Name) VALUES ('Fan')", SQL_NTS);
    //nResult = SQLExecDirect(handleStatement, L"INSERT INTO Wallet (Name) VALUES ('Fan')", SQL_NTS);
    //nResult = SQLExecDirect(handleStatement, L"INSERT INTO Wallet (WalletID, Name) VALUES (1, 'Fan')", SQL_NTS);
    if (!SQL_SUCCEEDED(nResult))
    {
        printErr(handleStatement, SQL_HANDLE_STMT);
    }

    // Try to end transaction. Will fail, but no error information can be fetched(?)
    nResult = SQLEndTran(SQL_HANDLE_DBC, handleDBC, SQL_COMMIT);
    if (!SQL_SUCCEEDED(nResult))
    {
        printErr(handleDBC, SQL_HANDLE_DBC);
    }

    return 0;
}

Flocculant

Answer 3

The WalletID int column is not set to auto-increment.

Wnw

I know. I'm demonstrating a failure, and using the constraint violation as a means to getting a minimally reproducible example. – Gravitt