The requested operation could not be performed because OLE DB provider "MSOLAP" for linked server "(null)" does not support the required transaction interface. RRS feed

  • Question

  • Hi All,

    I am not quite sure if this problem is attributed to SQL Server, SQL Server Analysis Services or some other unknown culprit, but it seems this is the likely place to start.

    I have started receiving the following error messages during peak times intermittently:

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

    During non-peak times, this error message never appears.  So it's clearly a load issue and not a functional one.  Even during peak times, the transactions that do get through are very quick and responsive, so it's not as if SSAS is struggling under the load to execute queries.

    Basically, the application is a WCF service that receives a request, calls another WCF service (potentially on a separate server), which then calls SQL Server to run a stored procedure, which then calls SSAS to receive certain OLAP data via a linked server.

    The last part is where it is falling over.  SQL Server returns that exception which bubbles back through the various tiers.

    The strange thing is, is that no transaction exists.  WCF is explicitly forbidding and supressing transactions from flowing through and SQL Server never creates one.  In fact, if I do have a distributed transaction, I get an immediate error because SSAS is unable to enlist as part of that transaction.  This means the error message is quite misleading because no transaction actually exists.

    I have tried heavily load testing the querying by having many threads hit the service with random requests (far more than in actual peak load), to which no error messages could be reproduced.

    The only thing that I haven't been able to simulate yet is the actual data ingestion that is occurring (we use proactive caching in SSAS to load data throughout the day in quite significant quantities).  The actual ingestion process into the OLTP database uses distributed transactions heavily (MSMQ transaction to ingest data).  I can't understand how this may affect it, though, if no transaction exists when querying.

    During these times the server itself is under minimal load.  Low CPU, disk activity and RAM usage.

    Does anyone have any ideas why this error message may come up intermittently?  Is there anything I can do to trace/debug it, or any settings that I might be able to increase?

    Note: Searching over googling,

    I  enabled "Non transacted updates" option for the MSOLAP provider on SQL Server to check whether the error comes up. To enable the option, connect Database Engine in SQL Server Management Studio and go to Server Object node, expend the Server Object -> Linked Servers-> Providers -> MSOLAP, double click MSOALP to open its options window and you will see "Non transacted updates" option and check it.

    But it doesn't works. I am still facing same error message in SQL profiler.



    Monday, July 16, 2012 10:17 AM

All replies