sql-server - 无法执行请求的操作，因为链接服务器 'MSOLAP' 的 OLE DB 提供程序 'SSAS' 不支持所需的接口(interface)

Question

当我尝试使用链接服务器更新多维数据集时出现此错误:

Msg 7390, Level 16, State 2, Line 20 The requested operation could not be performed because OLE DB provider "MSOLAP" for linked server "SSAS" does not support the required transaction interface.

我的查询:

DECLARE @command VARCHAR(max) = 'UPDATE CUBE [TESTING] SET (
       [Measures].[Group Curr Amt Original],[Local Currency].[Local Currency].&[USD],[Time].[Fis Yr Month SKey].&[201806], [Transaction Currency].CurrencyName].&[US Dollar],  )=1234 USE_EQUAL_ALLOCATION;'

EXEC (@command) at SSAS

DECLARE @command2 VARCHAR(max) = 'commit tran'

EXEC (@command2) at SSAS

如果我不提交事务，更新就会生效。有什么办法可以解决这个问题吗？

Answer 1

我使用@GregGalloway 的回答评论解决了这个问题。这是为链接服务器启用此功能的简单方法:

EXEC master.dbo.sp_serveroption @server = N'SSAS'
    ,@optname = N'rpc'
    ,@optvalue = N'true'

EXEC master.dbo.sp_serveroption @server = N'SSAS'
    ,@optname = N'rpc out'
    ,@optvalue = N'true'

EXEC master.dbo.sp_serveroption @server = N'SSAS'
    ,@optname = N'remote proc transaction promotion'
    ,@optvalue = N'false'