SQL Server、远程存储过程和 DTC 事务
我们的组织在大型机 Adabas 数据库中拥有大量重要数据。 我们可以通过 ODBC 访问此数据，并使用 ODBC/自然“存储过程”从 C# 成功查询/更新它。
我们现在想要做的是从 SQL Server 2005 存储过程中查询大型机表，将结果转储到表变量中，对其进行处理，然后将结果与本机 SQL 数据连接作为结果集。
当我们只是选择它时，从 SQL 执行 Natural 过程工作正常； 然而，当我们将结果插入表变量时，SQL 似乎正在启动一个分布式事务，而这又似乎对我们的连接造成了严重破坏。
鉴于我们不执行更新，是否可以关闭此 DTC 升级行为？
有关正确设置 DTC 以与 DataDirect（以前称为 Neon Systems）Shadow ODBC 驱动程序对话的任何提示吗？
Our organization has a lot of its essential data in a mainframe Adabas database. We have ODBC access to this data and from C# have queried/updated it successfully using ODBC/Natural "stored procedures".
What we'd like to be able to do now is to query a mainframe table from within SQL Server 2005 stored procs, dump the results into a table variable, massage it, and join the result with native SQL data as a result set.
The execution of the Natural proc from SQL works fine when we're just selecting it; however, when we insert the result into a table variable SQL seems to be starting a distributed transaction that in turn seems to be wreaking havoc with our connections.
Given that we're not performing updates, is it possible to turn off this DTC-escalation behavior?
Any tips on getting DTC set up properly to talk to DataDirect's (formerly Neon Systems) Shadow ODBC driver?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
以下代码为链接服务器设置“启用分布式事务升级”：
这将允许您将链接服务器存储过程调用的结果插入到表变量中。
The following code sets the "Enable Promotion of Distributed Transactions" for linked servers:
This will allow you to insert the results of a linked server stored procedure call into a table variable.
我不确定 DTC，但 DTSX（集成服务）对于移动数据可能很有用。 但是，如果您可以简单地查询数据，您可能需要考虑添加链接服务器以进行直接访问。 然后，您可以编写一个简单的查询来根据链接服务器表中的选择来填充您的表。
I'm not sure about DTC, but DTSX (Integration Services) may be useful for moving the data. However, if you can simply query the data, you may want to look at adding a linked server for direct access. You could then just write a simple query to populate your table based on a select from the linked server's table.
这是真的。 正如您可能猜到的，我们要调用的自然过程会执行查找和计算，如果可能的话，我们希望将其保持在该级别。
That's true. As you might guess, the Natural procedures we want to call do lookups and calculations that we'd like to keep at that level if possible.
查看
将 REMOTE_PROC_TRANSACTIONS 设置为关闭< /a> 应该禁用它。或者
sp_serveroption配置链接服务器一般来说，不是每批。因为您是在 MS SQL 端编写，所以您启动了一个事务。
默认情况下，无论是否需要都会升级。
即使表变量不参与事务。
我之前也遇到过类似的问题，根据 MS SQL 是否在存储过程和其他内容中写入，MS SQL 端的行为有所不同。 我发现最可靠的方法是对我的 Sybase 链接服务器使用动态 SQL 调用...
Check out
SET REMOTE_PROC_TRANSACTIONS OFFwhich should disable it.Or
sp_serveroptionto configure the linked server generally, not per batch.Because you are writing on the MS SQL side, you start a transaction.
By default, it escalates whether it needs to or not.
Even though the table variable does not particapate in the transaction.
I've had similar issues before where the MS SQL side behaves differently based on if MS SQL writes, in a stored proc and other stuff. The most reliable way I found was to use dynamic SQL calls to my Sybase linked server...