Executing MDX queries from inside SQL Server Stored Procedures (SQL Server 2005)

Hello all,

Does anyone have any idea how to access a cube from stored procedures in SQL Server 2005?

My idea was to use SQLCLR and write a function in .NET that accessed the cube through ADOMD, but there are problems with that.

See the following code sample:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Microsoft.AnalysisServices.AdomdClient;

public partial class UserDefinedFunctions
{

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString MDXRdr()
{
AdomdConnection conn = new AdomdConnection();
conn.ConnectionString = @."Provider=SQLNCLI.1;Data Source=JOAHSE0\SQL2005;Integrated Security=SSPI;Initial Catalog=dbRenTAK";

conn.Open();
// Just output cube name
string str = conn.Cubes[0].Name;
conn.Close();

return new SqlString(str);
}

};

First, I wasn't able to add a reference to AdomdClient from Visual Studio. I then did add a reference manually in the project file and it compiles. But when I try to deploy, I get an error message that the "assembly adomdclient was not found in the SQL catalog".

Thanks in advance for any suggestions or hints!

Best Regards,

Johan ?hln
Consultant, IFS

Moving to the "Data Mining" Forum, which is better suited for this question.