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.

Executing an external process() in SQLCLR Project

I can create an external text file from within the SQLCLR project, but I cannot run an external executable. Just in case you are asking, I need to do this to push data into a legacy application using a different DB format. I have found it best to simply use my old language (Clipper) for data validation, etc. - and especially since I require multiple indices to be open. So, if you could just take my word on this.

The following code:

Process newProcess = new Process();

string path = @."C:\TEST.BAT";

newProcess.StartInfo.FileName = path;

newProcess.Start();

Executes without error, but does not actually run the external.

Now, I can have a DOS (Clipper) application poll a directory for text files, but I am trying to get away from all these "mini" data transformation applications. If an exception is caused in the DOS app, a remote user on the other side of the country has no idea it is broke and his data (coming from a SQL Mobile Device) never gets to our legacy database structure.

So, am I out of luck?

Is your assembly deployed at the UNSAFE permission level? What happens if you try to run the same code in a console app run under the same user account (SQL Server service account or impersonated account, as appropriate)?|||

Hi Nicole - thx for responding.

I am able to execute a console app - the issue is that SQL Server will not allow touching any network drive or network resource period (from a SQLCLR Project). So my cmd app can update a DB on the C drive, but not one of my network drives.

As well, I can establish an OLEDB connection (Visual Foxpro) to a local directory, but not a network directory. I'm surprised that CLR will not even allow an external app to touch a network resource. Wild, eh? Although I understand why there is such security, there must be a way for me to execute an external console app that can update a DB on a network resource.

Oh - and yes, I am running the assembly at the UNSAFE permission level.

|||Might the problem be the user context rather than anything having to do with SQLCLR? Unless you deliberately impersonate another user (e.g.: via SqlContext.WindowsIdentity.Impersonate), your SQLCLR code will run under the user context of the SQL Server service account, which is highly unlikely to have any permissions on any network resources.|||

I think you're correct - it has to do with attempting to authenticate a local user (IUSR_Computername) on a network resource. I will look into impersonation (something my wife says I'm horrible at...)

You know, Nicole, you are the first person to assist me in these forums. I had actually thought of using a female handle - seems they get a pretty quick response... :)

I'll post the results of my efforts... and thx again.

|||

After impersonation, I can do something simple like use a streamwriter to create a text file on a network share. But it still throws an exception when attempting to execute an executable on the same network share. The impersonation rights are those of administrator (only for testing!). Oh, in case you're wondering - I simply change the Process StartInfo from @."C:\Mobile.exe" to @."F:\Mobile.exe", and you will have to take my word for it that the Mobile exists in both places. It runs without exception on the C drive.

What's really wild, is that the actual Mobile.exe code (clipper) will update a dbf on the C drive, but not on the F drive. What does the CLR do? Freeze all resources when in process? geez!

I'm also using:

[PermissionSetAttribute(SecurityAction.Demand, Name = "FullTrust")]

So... I really thought this would allow my CLR code to access a network share. I can't understand why the answer to this wouldn't be just a little more simple, even for an old programmer like me.