0

I am working on an integration between a SQL Server database and an Informix database. The databases are running on separate servers. I have created a Linked Server to Informix using the Informix OLE DB provider. I am able to retrieve data from Informix no problem. However, when I try to run an insert into Informix, I receive the following error:

The operation could not be performed because OLE DB provider "Ifxoledbc" for linked server "INFORMIX2" was unable to begin a distributed transaction.

I have verified that I can insert into the Informix database from the SQL Server machine, using the ConnectTest application that comes with the Informix Client SDK, so I don't think this is an permissions/firewall issue.

Here is how I am running the insert in my stored procedure:

SET NOCOUNT OFF;

DECLARE
@error_msg NVARCHAR(4000),
@error_severity INT,
@error_state INT,
@today datetime,
@set_no int

SET XACT_ABORT ON;

SET @today = CONVERT(VARCHAR(10), GETDATE(), 1)

BEGIN DISTRIBUTED TRANSACTION

BEGIN TRY 

--do stuff, including insert into Informix

COMMIT TRANSACTION

END TRY

BEGIN CATCH

SELECT
@error_msg = ERROR_MESSAGE(),
@error_severity = ERROR_SEVERITY(),
@error_state = ERROR_STATE()

-- Error has occured and transaction is uncommittable
IF (XACT_STATE()) = -1
BEGIN
    SET @error_msg = @error_msg + ' The transaction has been rolled back.'
    ROLLBACK TRANSACTION
END

-- Transaction is still committable
IF (XACT_STATE()) = 1
BEGIN
    SET @error_msg = @error_msg + ' The transaction was recoverable and was committed.'
    COMMIT TRANSACTION 
END

-- Report the error
RAISERROR (@error_msg, @error_severity, @error_state)

END CATCH

I have the Distributed Transaction Coordinator configured, based on this post. I have also tried setting the communication level to No Authentication Required, but received the same error.

enter image description here

Is there something that needs to be configured on the Informix server? Like a Distributed Transaction Coordinator equivalent in Solaris?

Community
  • 1
  • 1
wooters
  • 829
  • 6
  • 24

1 Answers1

0

While it's not a solution to the transaction issue, I've found the best possible workaround. In SSMS, go to Linked Servers > Providers. Right-click on Ifxoledbc (the Informix OLE DB provider) and select Properties. Check Enable on the properties below.

enter image description here

The "Allow inprocess" will allow you to insert into Informix. However, you cannot use a transaction. You have to use a Try-Catch and then use some old-school methods to ensure that the insert was successful (i.e., count on the target table).

wooters
  • 829
  • 6
  • 24