<块引用>

这个问题是 Microsoft ODBC 中一系列错误的一部分司机:

• 在使用 SQL Server OLE DB 提供程序时导致客户端错误

在使用 ODBC 驱动程序时会静默失败.该语句执行时不会引发任何错误.

当我的 SQL 语句运行没有错误时，我困惑了大约一个小时，但是这些行不会出现在数据库中.

静默失败

显然，无声的失败是不好的.

• 显然我不能使用 SQL Server ODBC 驱动程序,
• 并继续使用用于 SQL Server 的 Microsoft OLE DB 提供程序提供程序.

但是发生了什么?

我如何告诉 ADO-OLEDB-ODBC 驱动程序报告错误.它是 ODBC 连接字符串设置吗?是 MSDASQL 连接字符串设置吗?

示例伪代码

我实际上将 Delphi 与 ADO 一起使用.但我会将其转码为伪 C# 样式代码，以便于概念理解.

String commandText ="INSERT INTO Wallet (WalletID, Name) VALUES (1, 'Fan')"+CRLF+插入钱包(名称)值('Ardent Defender')"；ADOConnection 连接 = 新的 ADOConnection();conn.ConnectionString = szConnectionString;conn.Open();HRESULT hr = conn.Execute(commandText, ref recordsAffected, [eoExecuteNoRecords]);

实际上，HRESULT 的检查是由语言基础结构和编译器魔法处理的 - 如果它FAILED 则抛出本地语言异常.

解决方案

除非你指定 设置无计数.这些消息(行计数)将影响未编码以处理调用 NextRecordset 方法返回的多个记录集的 ADO 应用程序.

简单的解决方案是将 SET NOCOUNT ON 指定为批处理或存储过程中的第一条语句.

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.

解决方案

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.