Maintain transaction on Linked server inside a stored procedure which uses OpenRowSet command to read data from Excel file

Question

Hi,

I have a Helper DB which is on 32bit sql server and i have added a linked server of 64bit,

to perform Excel Import operation as Jet.oledb driver is not supported on 64-bit sql server machine.

Everything is working fine, but i have to maintain transactions for insert ,update,delete that happens on linked server database,

I have configured DTC service on client machine and server machine,

Added Block on top of sp for transaction on stored procedure start like,

BEGIN TRY SET XACT_ABORT ON BEGIN TRANSACTION
BEGIN TRY

SET XACT_ABORT ON

BEGIN TRANSACTION

--Code

--Code

END TRY

BEGIN CATCH

IF @@TRANCOUNT<>0 ROLLBACK TRANSACTION

--// raise error to log in website.

DECLARE @ErrorMessage NVARCHAR(4000);

DECLARE @ErrorSeverity INT;

DECLARE @ErrorState INT;

SELECT

@ErrorMessage = ERROR_MESSAGE(),

@ErrorSeverity = ERROR_SEVERITY(),

@ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, -- Message text.

   @ErrorSeverity, -- Severity.  

   @ErrorState -- State.  

   );  

But it throws an err saying,

The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not support the required transaction interface.

It is throwing error on Openrowset function that i have used to read data from Excel sheet

i.e,

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;HDR=Yes; IMEX=1;Database=C:\test.xls','SELECT EmployeeID FROM [Sheet$]
where EmployeeID is not null')

I m really stuck and getting Crazy as am not able to find any solution,

Thanks in Advance for Help,

Answer 1

What if you open the Excel file in a cursor, but before the Begin Trans. ?