XmlaClient. ExecuteStatement

Topics: Developer Forum
Mar 12, 2010 at 4:45 PM

Hi,

Having looked through a lot of the code in this project and tried to determine both here and using Google how to run an Execute command to run XMLA code. I've decided there is no real way to learn much about the XmlaClient object and in particular its ExecuteStatement method.

I really want to use a stored procedure taking a few variables to execute an XMLA Create command, but it seems impossible to get the statement to run. 

Am I missing a place in the project where this is done? I've seen XMLA Discover in action, but obvious attempts at cloning that code fail. I cannot find any documentation anywhere.

Sam.

 

Coordinator
Mar 14, 2010 at 8:29 PM

If you wanted to run create statements then cannot use the ExecuteStatement(), you would have to use the Execute() method. In Xmla terms the <Statement> tag is used to send queries to the server. You are right, there is not a lot of documentation on XmlaClient, but this is because technically it is part of Management Studio and is not meant to be re-distributed. Which is unfortunate as it's a nice "thin" way to run Xmla Discover commands. I figured out the difference between Execute and ExecuteStatement using Reflector.

You may be better using the AMO library (Microsoft.AnalysisServices.dll) which has an Execute method on the server object which you can use to run Xmla commands. There are some good examples on doing this here http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/dd085c71-7cf4-4223-960f-1d848d9c1acf 

Mar 15, 2010 at 5:31 PM

Thanks for all that. I think I'll have to persist trying to use the Execute() method then. I tried it before and it runs, but it just hangs. On a query which should finish in seconds it doesn't respond in 10 minutes, and even an attempt to cancel the query fails. So I'm obviously calling it wrongly.

I really need a call I can make to the server which cannot error. I am using a SSIS package to create a partition which might already exist. If it does, then it increases the error count in my package and causes it to fail. I can't increase the allowed errors as I need it to fail on most errors. So I simply want to send a few parameters to a SSAS stored procedure which creates the XMLA, and is set up to ignore the failure if the package exists.

I can't allow permissions to make the call any other way, only directly to AS.

I managed to get a console app creating the partitions just fine in testing, but I can't use it in production.

So, if I'm going to try to use Execute(), can you possible post a snippet of code you managed to get working? If you'd prefer not to, I'll keep messing with it.

Sam.

Coordinator
Mar 15, 2010 at 7:59 PM
Edited Mar 15, 2010 at 8:04 PM

I have a guess. One possibility is that executing that sproc takes out a lock on the database, thus the XMLA Process statement it's executing can't commit. It has basically deadlocked itself.

I think that's a possibility if you're not opening the connection inside the sproc correctly. For instance, the following could cause a deadlock

           XmlaClient client = new XmlaClient();
           client.Connect(Context.CurrentServerID);

But you might try this:

           XmlaClient client = new XmlaClient();
           client.Connect("*");

To be honest, I don't have time to test out my guess. I have never tried connecting to * using XmlaClient, but connecting to * via AMO prevents the problems described above. Can you give that a try and report back? Can you also post the exact sproc code you're using, and the exact way you're calling the sproc?

Coordinator
Mar 15, 2010 at 8:59 PM

A number of thoughts come to mind here.

  • You may be better off doing this all in AMO code. That way you would have access to the partitions collection so you could tell if the partition already existed or not.
  • If you want to use Xmla, you may be better off issuing an <Alter AllowCreate="True"> statement so that it would not throw an error if the object already existed.
  • The permissions could be an issue, you would have to deploy the assembly to run under a specified account with admin priviledges for this to work
  • You could be getting blocked or the potential also exists to be deadlocking with yourself (ie. the call to the sproc could be blocking the connection inside the sproc which is attempting to create the partition). You could test for this by running a SELECT * FROM $System.DISCOVER_LOCKS while your package is running. If you have other processing operations happening this will also block the creation of partitions. I think this is the most likely cause for your "hang" as I think that the execute method will just wait until it's lock request is granted.
  • I would have thought that the code in the Partition class would have been close to what you are after.

I'll have a look, but I don't think I have snippets lying around that do this sort of thing.

Mar 16, 2010 at 6:01 PM

Thank you both for the help. I think you're right furmangg in so much as it looks complicated to track the problem down whilst coding blind. I followed dgosbell's route though so never tried the "*" calling method, and seeing as I found a solution I can't justify the time messing with that.

I hadn't any concept of AMO code when I started this, but I'm getting better at it quite quickly! 

I guess that might show I'm quite new at this, but then, a forum like this will get a lot of new people over time, and I guess that's the point. So in case anyone wants to know what I did, I'll paste the crux of it below.

This is all in a SSIS Script Task, and the first trick is to add a reference to AMO. (Analysis Management Objects). Having done that, you get access to your AS databases fairly easily, and the possibilities look very wide from there. All I want to know is whether a particular partition exists, so I have the code below which does that. Actually it would execute on the exact reverse, if the partition doesn't exist, but you get the idea.

Thanks to the guys who took the time to help me out.

Sam.

 

 

 

public void Main() {
    DbConnectionStringBuilder connBuilder = new DbConnectionStringBuilder();
    connBuilder.ConnectionString = Dts.Connections["MySSASConnection"].ConnectionString;
    string serverName = connBuilder["Data Source"].ToString();
    string databaseName = connBuilder["Initial Catalog"].ToString();
    Server server = new Server();
    server.Connect(String.Format("Data Source={0}", serverName));
    Database database = server.Databases.GetByName(databaseName);
    Cube cube = database.Cubes.GetByName("MyCube");
    MeasureGroup myMeasureGroup = cube.MeasureGroups.GetByName("MyMeasureGroup");
    if(myMeasureGroup.Partitions.IsValidName("Partition23")) {
    }
    
    server.Disconnect();
}

 

Using System;
using System.Data.Common;
using Microsoft.AnalysisServices;
.....

public void Main() {

    DbConnectionStringBuilder connBuilder = new DbConnectionStringBuilder();
    connBuilder.ConnectionString = Dts.Connections["MySSASConnection"].ConnectionString;
    string serverName = connBuilder["Data Source"].ToString();
    string databaseName = connBuilder["Initial Catalog"].ToString();

    Server server = new Server();
    server.Connect(String.Format("Data Source={0}", serverName));
    Database database = server.Databases.GetByName(databaseName);
    Cube cube = database.Cubes.GetByName("MyCube");
    MeasureGroup myMeasureGroup = cube.MeasureGroups.GetByName("MyMeasureGroup");

    if(myMeasureGroup.Partitions.IsValidName("Partition23")) {
	//No such partition
    }
    
    server.Disconnect();

}