Unable to create dataset for report from stored procedure 1

[alexjones]

At the risk of having anyone who sees this run screaming from the forum - the stored procedure extracts data from a FoxPro linked server. Initial attempts at retrieving data yielded the dreaded message:

Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]Driver not capable]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].

After adding set options:
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
inside and outside the stored procedure and
SET XACT_ABORT ON
inside the stored procedure, I was able to execute the stored procedure correctly from Query Analyzer.

When I tried to use the stored procedure to populate the dataset for a report I was creating in Reporting Services (2005, SP2), once again I got

The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction. (.Net SqlClient Data Provider)

Can anyone tell me what I'm missing here? Or point me in the direction of something or someone who can?

Any hints would be greatly appreciated.

 

[ousoonerjoe]

Might try the Microsoft: VFP - Databases, SQL&VFP, and Reports Forum if you don't get any response from here.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.