Custom Stored procedure

Hi, I've tried to develop custom function for AS 2005, written in C#, and registered as database assembly

Code snippet is here

public string MySampleMethod()

{

AdomdCommand command = new AdomdCommand();

command.CommandText = string.Format("with member A AS 'username' select A on 0 FROM MyCube");

return (string)command.ExecuteScalar();

}

Error that I've got was "XML for Analysis parser: The input query is not in the language specified in the Dialect property for this request."

When I execute the same query from SQL Management Studio, everything works OK.

What I did wrong and how it can be resolved?

Thanks in advance

Borko

Hi Borko,

You can't use AS stored procs to execute queries on the same connection as you're using to call the proc, unfortunately. You can open a new connection within the proc but that may or may not be particularly useful to you - here's an example of how to do this:

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.AnalysisServices.AdomdServer;

using Microsoft.AnalysisServices.AdomdClient;

namespace MDXTests

{

public class RunMDXQueries

{

public static Microsoft.AnalysisServices.AdomdClient.AdomdDataReader RunAQuery()

{

Microsoft.AnalysisServices.AdomdClient.AdomdCommand c = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand("select measures.members on 0 from [adventure works]");

AdomdConnection conn = new AdomdConnection(@."Data Source=localhost\sh; Provider=msolap.3; initial catalog=adventure works dw");

conn.Open();

c.Connection = conn;

return c.ExecuteReader();

}

}

}

You need to set security permissions to 'unrestricted' to get this to work; you can then call this from SQLMS using the following:

CALL SPSRUNNINGQUERIES.RUNAQUERY()

HTH,

Chris

|||

Chris,

information that is not possible to execute queries on the same connection as connection used to call the proc is very usefull.

Thank you very much.

Borko