SSAS, linked server, unwanted distributed transaction

This is a discussion on SSAS, linked server, unwanted distributed transaction within the sqlserver-datawarehouse forums in Data Warehousing category; Hi While doing some experiments with SSAS, i got stuck with transaction being promoted to DTS. Scenario is as follows: 1. There is an SQLServer2005 database and SSAS cube built over it. 2. ASP.NET application uses CellSetGrid2 web control to connect to cube. It internally uses Microsoft.AnalysisServices.AdomdClient. 3. I've defined linked server, in order to query analysis services using SQL; it works fine using OPENQUERY. EXEC master.dbo.sp_addlinkedserver @server='DW',@srvproduct='',@provider='MSOLAP', @datasrc='localhost',@catalog='DW' 4. I'd like to execute code similar to insert into results select * from OPENQUERY(DW,'....'). When being ...

Go Back   Database Forum > Data Warehousing > sqlserver-datawarehouse

Database Forums

Register FAQ Calendar Search Today's Posts Mark Forums Read
  #1  
Old 08-14-2008, 07:13 AM
Default SSAS, linked server, unwanted distributed transaction

Hi

While doing some experiments with SSAS, i got stuck with
transaction being promoted to DTS.
Scenario is as follows:
1. There is an SQLServer2005 database
and SSAS cube built over it.
2. ASP.NET application uses CellSetGrid2 web control
to connect to cube. It internally uses
Microsoft.AnalysisServices.AdomdClient.
3. I've defined linked server, in order to query
analysis services using SQL; it works fine
using OPENQUERY.

EXEC master.dbo.sp_addlinkedserver
@server='DW',@srvproduct='',@provider='MSOLAP',
@datasrc='localhost',@catalog='DW'

4. I'd like to execute code similar to
insert into results select * from OPENQUERY(DW,'....').
When being executed from SQL console it works fine.
5. Executing this code from application also works,
unless olap grid already established connection to SSAS.


It seems that in this case, transaction being promoted
to become distributed, what ends up in error such as:
- Cannot start a transaction for OLE Database provider "MSOLAP" for linked
server DW.
- OLE Database provider "MSOLAP" for linked server "DW" returned message
"Error Code = 0x8004D008, External Code = 0x00000000:."


Any suggestion on how to avoid this?
(but not using linked server, and using the same SSAS connection
object both for grid and application logic).

--
marcin

Reply With Quote
Reply


Thread Tools
Display Modes



All times are GMT -4. The time now is 05:20 AM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Integrated by bbpixel2008 :: jvbPlugin R1013.368.1

Search Engine Friendly URLs by vBSEO 3.1.0
vB Ad Management by =RedTyger=
In an effort to better serve ads to our visitors, cookies are used on Mydatabasesupport.com. For more information, check out our Privacy Policy.