OLE提供程序"MSDASQL“用于链接服务器"[name]”无法开始分布式事务

Question

我有一个从server 2012到PostgreSQL 9.3的链接服务器设置，以及一个存储过程，从它中提取3个过滤的表，并将每个表存储在一个临时表中。

在存储过程的末尾有一个来自本地SQL数据库的带有许多左联接的最后查询，它包括这3个临时表。

我禁用了“启用RPC的分布式事务处理”。每1/6或更多次使用此存储过程的尝试都失败，web服务器上的错误为：Unknown Error Detected System.Data.SqlClient.SqlException (0x80131904): The operation could not be performed because OLE DB provider "MSDASQL" for linked server "POSTGRESQL" was unable to begin a distributed transaction. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection server上没有错误。

链接服务器定义：

EXEC master.dbo.sp_addlinkedserver @server = N'POSTGRESQL', @srvproduct=N'PostgreSQL', @provider=N'MSDASQL', @datasrc=N'PostgreSQL'
 EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'POSTGRESQL',@useself=N'False',@locallogin=NULL,@rmtuser=N'xxxxxx',@rmtpassword='xxxxx'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'remote proc transaction promotion', @optvalue=N'false'

存储过程/查询(非常简化的对不起)

ALTER PROCEDURE [dbo].[SP_Name]
@DateFrom DATETIME,
@DateTo DATETIME

AS

DECLARE @variableB int

SET @variableB= {some criteria} 

IF (@variableB < 1000)

BEGIN
-- Passthrough to PG for a filtered table, instead of entire table grabbed via open query
    IF OBJECT_ID('tempdb.dbo.#temp_table1', 'U') IS NOT NULL
      DROP TABLE #temp_table1; 

    create table #temp_table1 (column1 int, column2 varchar(60))

    DECLARE @TSQL varchar(max)
    SET  @TSQL = 'select c1, c2 from OpenQuery([POSTGRESQL],''
            select c1, c2 from table1 t where 
            t.date>=''''' +CONVERT(VARCHAR(15),@DateFrom,102)+ '''''' + 't.date <=''''' +CONVERT(VARCHAR(15),@DateTo,102) + '''''' +'order by c1'')'

    insert into #temp_table1
    EXEC (@TSQL)

    create clustered index temp_index1 on #temp_table1 
    (column1 asc)
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

-- 2nd table passthrough to PG for a filtered table, instead of entire table grabbed via open query
    IF OBJECT_ID('tempdb.dbo.#temp_table2', 'U') IS NOT NULL
      DROP TABLE temp_table2; 

    create table #temp_table2 (column1 int, column2 varchar(60), column3 int)

    SET  @TSQL = 'select a, b, c from OpenQuery([POSTGRESQL],''
            select a, b, c from table2 t where 
            t.date>=''''' +CONVERT(VARCHAR(15),@DateFrom,102)+ '''''' + 't.date <=''''' +CONVERT(VARCHAR(15),@DateTo,102) + '''''' +'order by a'')'

    insert into #temp_table2
    EXEC (@TSQL)

-- 3rd table passthrough to PG for a filtered table, instead of entire table grabbed via open query
    IF OBJECT_ID('tempdb.dbo.#temp_table3', 'U') IS NOT NULL
      DROP TABLE temp_table3; 

    create table #temp_table3 (column1 int, column2 varchar(60))

    SET  @TSQL = 'select a1, a2, a3 from OpenQuery([POSTGRESQL],''
            select a1, a2, a3 
            from
            table3 t
            where 
            t.date>=''''' +CONVERT(VARCHAR(15),@DateFrom,102)+ '''''' + 't.date <=''''' +CONVERT(VARCHAR(15),@DateTo,102) + ''''''
{plus other filtering criteria} +'order by a1'')'

    insert into #temp_table2
    EXEC (@TSQL)

IF (@variableB < 1000)

BEGIN

select {many columns}
from {various local tables with left joins}
left join #temp_table1 on {conditions}
left join #temp_table2 on {conditions}
left join #temp_table3 on {conditions}

END

局部直接转矩特性

• 我认为正在发生的情况是，临时表中的3个开放查询正在得到分发，但不知怎么的，这是行不通的。我承认我不太明白这的意思--这是我无法理解的。我还知道，启用“为RPC启用分布式事务促进”会使失败发生得更频繁。
• 这再次工作5/6次，失败约1/6次。所以我不知道发生了什么。
• 这是PostgreSQL上的只读事务--我不知道为什么要调用分布式事务。

编辑我看到这个问题，这里，并认为我不应该有这个问题。

编辑2我可能找到一个重复的问题这里。

Answer 1

将3个temp表插入到一个具有隔离级别的事务中，读取未提交似乎已经停止了对分布式事务的升级，并解决了我的间歇性问题。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;

    DECLARE @TSQL varchar(max)
    SET  @TSQL = 'select c1, c2 from OpenQuery([POSTGRESQL],''
            select c1, c2 from table1 t where 
            t.date>=''''' +CONVERT(VARCHAR(15),@DateFrom,102)+ '''''' + 't.date <=''''' +CONVERT(VARCHAR(15),@DateTo,102) + '''''' +'order by c1'')'

    insert into #temp_table1
    EXEC (@TSQL)

.....etc

COMMIT TRANSACTION;

这是一个重复的问题，最终给了我解决方案。也许，PostgreSQL ODBC不支持分布式事务--如果尝试使用临时表或尝试三次连接远程数据库，仅在链接服务器属性上禁用它们是不够的--这是由于ACID功能。