Monday, November 10, 2008

Remember to enable MARS when using Snapshot Isolation from SSAS

We started getting this error when processing our cube:

OLE DB error: OLE DB or ODBC error: Cannot create new connection because in manual or distributed transaction mode
It went away when:
  • We changed to using ReadCommitted isolation, rather than snapshot
  • We processed the cube using Maximum Parallel Tasks = 1

Reading knowlege base article PRB: SQLOLEDB Allows Only One Connection in Scope of Transaction lead me to think that SSAS was trying to open multiple connections within a transaction, which isn't allowed.

Which got me thinking about MARS. Not quite sure why it wasn't on to start with, but I enabled it, and then everything was fine again.

Turns out this is actually a RTFM, if you pay attention when reading How to enable the snapshot transaction isolation level in SQL Server 2005 Analysis Services . Of course it's only in Snapshot mode that SSAS attempts to ensure database consistency for the duration of the entire Process operation, which is why it's not a problem using ReadCommitted isolation.

No comments:

Popular Posts