在与 SQL Server 的 ODBC 连接中打开多个结果集 [英] Turning on multiple result sets in an ODBC connection to SQL Server

问题描述

我有一个应用程序，最初需要连接到 Sybase(通过 ODBC)，但我还需要添加连接到 SQL Server 的功能.由于 ODBC 应该能够同时处理这两种情况，所以我认为我处于一个很好的位置.

I have an application that originally needed to connect to Sybase (via ODBC), but I've needed to add the ability to connect to SQL Server as well. As ODBC should be able to handle both, I thought I was in a good position.

不幸的是，默认情况下，SQL Server 不会让我嵌套 ODBC 命令和 ODBCDataReaders - 它抱怨连接正忙(连接正忙于另一个命令的结果).

Unfort, SQL Server will not let me, by default, nest ODBC commands and ODBCDataReaders - it complains the connection is busy (Connection is busy with results for another command).

我知道在通过本机驱动程序连接到 SQL Server 时，我必须指定在类似情况下允许多个活动结果集 (MARS)，所以我认为这不会成为问题.

I know that I had to specify that multiple active result sets (MARS) were allowed in similar circumstances when connecting to SQL Server via a native driver, so I thought it wouldn't be an issue.

DSN 向导没有入口y 在创建 SystemDSN 时.

The DSN wizard has no entr y when creating a SystemDSN.

有些人提供了注册表黑客来解决这个问题，但这不起作用(将值为 Yes 的 MARS_Connection 添加到 HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\system-dsn-name).

Some people have provided registry hacks to get around this, but this did not work (add a MARS_Connection with a value of Yes to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\system-dsn-name).

另一个建议是创建一个文件 dsn，并在其中添加MARS_Connection=YES".没用.

Another suggestion was to create a file-dsn, and add "MARS_Connection=YES" to that. Didn't work.

最后，一个无 DSN 的连接字符串.我已经尝试过这个(使用 MultipleActiveResultSets - 与 Sql Server 连接将使用的变量相同)，

Finally, a DSN-less connection string. I've tried this one (using MultipleActiveResultSets - same variable as a Sql Server connection would use),

"Driver={SQL Native Client};Server=xxx.xxx.xxx.xxx;Database=someDB;Uid=u;Pwd=p;MultipleActiveResultSets=True;"

还有这个:

"Driver={SQL Native Client};Server=192.168.75.33\\ARIA;Database=Aria;Uid=sa;Pwd=service;MARS_Connection=YES;"

我检查了各种连接字符串站点 - 它们都提出了我已经尝试过的建议.

I have checked the various connection-string sites - they all suggest what I've already tried.

我应该声明我已经尝试了 SQL Server 驱动程序和 SQL Server 本机驱动程序...

I should state that I've tried both the SQL Server driver, and the SQL Server native driver...

推荐答案

根据 使用多个活动结果集 (MARS):

SQL Server Native Client ODBC驱动程序通过添加支持 MARS到 SQLSetConnectAttr 和SQLGetConnectAttr 函数.SQL_COPT_SS_MARS_ENABLED 已被添加接受SQL_MARS_ENABLED_YES 或SQL_MARS_ENABLED_NO，与SQL_MARS_ENABLED_NO 是默认值.另外，一个新的连接字符串关键字 Mars_Connection，原样添加.它接受是"或否"价值观；否"是默认值.

The SQL Server Native Client ODBC driver supports MARS through additions to the SQLSetConnectAttr and SQLGetConnectAttr functions. SQL_COPT_SS_MARS_ENABLED has been added to accept either SQL_MARS_ENABLED_YES or SQL_MARS_ENABLED_NO, with SQL_MARS_ENABLED_NO being the default. In addition, a new connection string keyword, Mars_Connection, as been added. It accepts "yes" or "no" values; "no" is the default.

确保您的客户端加载正确的驱动程序，使用 Mars_Connection=yes，并通过检查 SQLGetConnectAttr.

Make sure your client loads the right drivers, use Mars_Connection=yes, and validate in the app by checking SQL_COPT_SS_MARS_ENABLED on SQLGetConnectAttr.

这篇关于在与 SQL Server 的 ODBC 连接中打开多个结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！