在SQL查询中运行XMLA语句？

I'm trying to create a new role on my analysis DB.

我正在尝试在我的分析数据库上创建一个新角色。

i have tested multiple combination :
when running a simple MDX query with Openrowset => it works.
when running the it works.
when trying to create anew SSAS role from SQl relational DB using openrowset=> ERROR.

我测试了多个组合:当使用Openrowset =>运行简单的MDX查询时,它可以工作。当它运行时它工作。尝试使用openrowset => ERROR从SQl关系数据库创建新的SSAS角色时。

here is the code I'm trying

这是我正在尝试的代码

SELECT * FROM OpenRowset('MSOLAP', 'DATA SOURCE=servername; Initial Catalog=AnalysisDBName;',
'
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
            <ParentObject>
                <DatabaseID>AnalysisDBName</DatabaseID>
            </ParentObject>
            <ObjectDefinition>
                <Role xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">
                    <ID>Role 22</ID>
                    <Name>Rolename</Name>
                </Role>
            </ObjectDefinition> 
    </Create>

')

and the error that i receive is:

我收到的错误是:

OLE DB provider "MSOLAP" for linked server "(null)" returned message "A required child element is missing under Envelope/soap:Body at line , column  (namespace 'http://schemas.xmlsoap.org/soap/envelope/'). One of Fault, AuthenticateResponse, DiscoverResponse, ExecuteResponse was expected.".
Msg 7321, Level 16, State 2, Line 2
An error occurred while preparing the query "
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <ParentObject>
        <DatabaseID>AnalysisDBName</DatabaseID>
    </ParentObject>
    <ObjectDefinition>
        <Role xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">
            <ID>Role 22</ID>
            <Name>Rolename</Name>
        </Role>
    </ObjectDefinition>
</Create>
" for execution against OLE DB provider "MSOLAP" for linked server "(null)".

Please help :)
Regards, elie

请帮助:)问候,elie

2 个解决方案

#1

You can add your ssas olap as linked server and then use this code:

您可以将ssas olap添加为链接服务器,然后使用以下代码:

EXEC ('XMLAsomething ....rest of your code') AT LinkedOlap

在LinkedOlap中执行EXEC('XMLAsomething ......其余代码')

#2

OPENROWSET expects a valid query such as "SELECT * FROM table" (in the correct SQL dialect of the data source specified)

OPENROWSET需要一个有效的查询,例如“SELECT * FROM table”(在指定的数据源的正确SQL方言中)

The xml snippet is not a valid query in most SQL dialects, especially not T-SQL.

xml片段在大多数SQL方言中不是有效查询,尤其不是T-SQL。

Unfortunately, there is no CREATE USER equivalent in MDX like T-SQL, so it can't be done via regular SQL statement or OPENROWSET.

不幸的是,在MDX中没有像T-SQL那样的CREATE USER等价物,因此不能通过常规SQL语句或OPENROWSET来完成。

In the SSAS Granting User Access, it mentions using AMO to manage users which can't done using OPENROWSET.

在SSAS授予用户访问权限中,它提到使用AMO来管理使用OPENROWSET无法完成的用户。

Sorry, it's not an answer: I can only say how not to do it...

对不起,这不是答案:我只能说不怎么做......

#1