用於連結伺服器的 OLE DB 提供程序“MSDASQL”“namen一種米和name’ 無法開始分佈式事務

我有一個從 SQL Server Ent 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 上的錯誤為： SQL 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

本地 DTC 屬性

• 我認為正在發生的是，臨時表中的 3 個開放查詢正在分發，並且不知何故這不起作用。我承認我不太明白這一點——這超出了我的想像。我也知道啟用“為 RPC 啟用分佈式事務的提升”會使失敗更頻繁地發生。
• 這再次工作了 5/6 次，失敗了大約 1/6 次。所以我不確定發生了什麼。
• 這是 PostgreSQL 上的只讀事務 - 我不確定為什麼要呼叫分佈式事務。

編輯我在這裡 看到這個問題，並認為我不應該遇到這個問題。

編輯 2 我可能在這裡發現了一個重複的問題。

使用 ISOLATION LEVEL READ UNCOMMITTED 將 3 個臨時表插入包裝在一個事務中似乎已經停止升級到分佈式事務並解決了我的間歇性問題。

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 功能，在嘗試使用臨時表或嘗試連接到遠端數據庫 3 次時，在連結伺服器屬性上禁用它們是不夠的。

引用自：https://dba.stackexchange.com/questions/139740