SQL Server: batch with primary key violations not fully executed using ODBC SQLExecDirect

user_4919610 0
Viewed 103

In SQL Server by default, a primary key violation does not abort the batch. This behaviour can easily be reproduced using SQL Server Management Studio (SSMS), for instance:

create table dbo.A (id int primary key)

insert dbo.A values (0) --works!
insert dbo.A values (0) --primary key violation
insert dbo.A values (0) --primary key violation
insert dbo.A values (0) --primary key violation
insert dbo.A values (1) --works!

select * from dbo.A

Alas, when I run similar code from my C++ program using the ODBC function SQLExecDirect(), I get non-deterministic behaviour, i.e. the batch is aborted after "a couple of" primary key violations and the remainder is not executed.

Consider the following loop which adds an insert statement to the batch and executes it:

for (int i = 0; i < 100; ++i)
{
   ssSql << L"insert dbo.A values(" << i << L")" << std::endl; // append 
   wcscpy_s(wszInput, ssSql.str().c_str());
   SQLExecDirect(hStmt, wszInput, SQL_NTS);
}

Every iteration will add one more primary key violation to the batch, but the expectation is that the final insert statement will eventually be executed.

When using "ODBC Driver XX for SQL Server", where XX is 11, 13 or 17, I only get values 0-24 inserted, no more. If I copy the batch from the 99th iteration and run it in SSMS, I get the remaining values (25-99) inserted.

When using the old, Windows bundled "SQL Server" driver, sometimes I get all values inserted, sometimes less. Hence, I don't see any deterministic behaviour in this.

Is this a bug/limitation of ODBC, the driver or SQL Server?

odbc sql-server
1 Answers

Is this a bug/limitation of ODBC, the driver or SQL Server?

The Driver. All drivers simplify the Client/Server interaction to make programming easier, but every simplification creates corner cases.

That batch will return a variety of messages to the client, including rowcount notifications and error messages, and complicating things, SQL Server will buffer the messages on the server for performance reasons.

So what's probably happening is that the driver aborts the batch when it sees the first error message, which it's perfectly free to do. But when it sees the first error message is not the same as when the first error message is sent, because of the server-side message buffering.

Best practice is to always use XACT_ABORT ON, and if you want to send a bunch of rows to SQL Server and insert whichever don't exist, you can craft INSERT ... WHERE NOT EXISTS queries, use TSQL's MERGE or set IGNORE_DUP_KEY on the index.

user_72977000
Related