Can anyone explain why you cannot register the assembly
Microsoft.AnalysisServices.AdomdClient in a SQL Server 2005 database with
permission set to “Safe” without generating SQL Server error 6211?

Why do you have to SET TRUSTWORTHY ON in your database in order to register
the Microsoft.AnalysisServices.AdomdClient assembly with an “Unsafe”
permission set? (You would think this assembly would be safe and trustworthy,
wouldn't you?)

I would like to execute an XMLA command within a stored procedure that
processes a SQL Server 2005 Analysis Services database. It looks like the
way to do this is by 1) registering the
Microsoft.AnalysisServices.AdomdClient assembly in my database by setting
TRUSTWORTHY ON in my database then referencing the assembly with an “Unsafe”
permission set; 2) creating a .NET assembly that executes the
AdomdClient.ExecuteNonQuery method; 3) referencing the new assembly in my
database (which can be referenced using a “Safe” permission set by the way);
then 4) calling the assembly in my stored procedure via the syntax
CALL myassembly.myclass.myexecxmlacommand(xmlacommandas text).

Is there a better way to execute an XMLA command within a stored procedure?
I don't want to use an SSIS package because I want to execute XMLA commands
on different databases at different times for different purposes. In other
words I want this to be very generic.

Thanks in advance for your help.
--
Wendell G.

In article <6AC777D5-3F51-4657-B00E-14F64A25296C (AT) microsoft (DOT) com>,
WendellG (AT) discussions (DOT) microsoft.com says...
This is because AdomdClient can be used to connect to other machines,
you will find that *any* library that can establish connections to other
machines (eg ado.net) will not require an elevated trust level.

I think any code that can initiate processing is going to require full
trust. If you are after other options, some other ways of initiating the
processing of a cubes would be:

1) use the AMO library, it has a Server.Execute method, which will send
an XMLA command. Or you can just call the process method on the relevant
object.

2) Set up proactive caching with client notifications (only on
Enterprise Edition)

3) Use the Microsoft.AnalysisServices.xmla library. I have an example of
using this library on my blog at
http://geekswithblogs.net/darrengosb.../xmlaClient.as
px

Personally, I don't know if I would set this sort of thing up as a
stored procedure, I think I would tend to favour setting up a separate
app. (or you could use the ascmd sample that was added to the samples
download with SP1)

Finally you will soon be able to write scripts in PowerShell to call any
of the above libraries too (actually you can do this now, but PowerShell
has not been officially released, it is only at RC1).

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell