0

I have a linked server setup from SQL Server Ent 2012 to PostgreSQL 9.3 and a stored procedure pulling 3 filtered tables from it and storing each in a temporary table.

There is a final query at the end of the Stored Procedure with many left joins, from local SQL Database and it includes these 3 temporary tables.
I have disabled "Enable promotion of distributed transactions for RPC". Every 1/6 or more attempts to use this Stored Procedure fails, with error on web server as: 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 No errors on the SQL Server though.

Linked server definition:

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'

Stored Procedure/Query (very simplified sorry)

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

Local DTC Properties

enter image description here

  • What I think is happening, is that the 3 openqueries into temp tables are getting distributed, and somehow that just doesn't work. I confess I don't understand this very well - it's over my head. I also know that enabling "Enable promotion of distributed transactions for RPC" makes the fails happen more frequently.
  • This again works 5/6 times and fails about 1/6 times. So I am not sure what is happening.
  • This is a read only transaction on PostgreSQL - I am not sure why distributed transaction is getting invoked at all.

Edit I see this question here and thinking I shouldn't be having this issue.

Edit 2 I may have a found a duplicate question here.

1

Wrapping the 3 temp table inserts in one transaction with ISOLATION LEVEL READ UNCOMMITTED seems to have stopped promotion to a distributed transaction and solved my intermittent problem.

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;

This was a duplicate question and ultimately gave me the solution. Perhaps, PostgreSQL ODBC does not support distributed transactions - and disabling them on Linked Server Properties is not enough when attempting to use temp tables or attempting to connect to a remote database 3 times -- due to ACID functionality..

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.