sql-server - SQL Server ODBC 驱动程序未引发错误

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.

背景

如果我有一个示例表:

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

我尝试发出插入表中的 SQL，但未指定 NOT NULL WalletID 列的值:

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

SQL Server 给出错误:

(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.

这是有道理的。

使用 ADO/OLEDB 和 SQL Server OLE DB 提供程序 (SQLOLEDB) 连接时:

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

当我执行INSERT时，ADO/OLEDB/COM 基础结构返回失败，这会作为异常返回到高级语言:

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

这一切都是有道理的。

但尝试使用 ODBC 驱动程序

随着 native 客户端 OLE DB 提供程序(和 the MS recommendation that you do not use the native client ODBC drivers )的贬值，我想我会尝试使用 SQL Server ODBC 驱动程序:

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

<小时/>

更新 - 未弃用:六年后，Microsoft announced the un-deprecation of OLE DB support for SQL Server ，以及为 SQL Server 创建第三个 OLE DB 驱动程序: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.

<小时/>

我发出我的批处理:

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

我惊讶地发现同样的 SQL 语句:

• 在 SQL Server 本身中触发 INSERT FAILS 错误:

  

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

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

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

无声失败

显然，无声的失败是没有好处的。

• 显然我不能使用 the SQL Server ODBC driver ,
• 并继续使用the Microsoft OLE DB Provider for SQL Server provider .

但是到底发生了什么？

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

示例伪代码

我实际上正在使用 Delphi 和 ADO。但我会将其转码为伪 C# 样式代码，以便于概念理解。

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

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

Answer 1

SQL Server 在每个语句后都会向客户端返回 DONE_IN_PROC 消息，除非您指定 SET NOCOUNT ON 。这些消息(行计数)将影响未进行编码以处理通过调用 NextRecordset 方法返回的多个记录集的 ADO 应用程序。

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