Executing MDX StoredProcedures using AdomdClient.dll

Hi fellow developers,

At the moment, i have a SSAS 2005 installed and a working cube on it. Everything is working fine. However i want to have an ASP.NET Page to access some of the data in the cube and present it.

Well this would be no problem at all, if i use the AdomdClient and send queries over it, but this destroy my layer architecture. I dont want any Queries in my Application, but in the SQL Server.

When i used a sql server database i always used stored procedures to access the data because of security and consistence.

Now is there a possible way to create MDX Stored Procedures and execute them with the Adomd API like i did with sql server databases?

Or other clean solutions like creating mdx libraries on server and access it with the client?

I appreciate your help.

Sincerely

David

PS: If you find any gramatically mistakes, keep em :-))

Moving to SQL Server Analysis Services forum.

Thanks,
Sarah

|||So far there is no support for stored procedures. There is support for parameterized queries though.

So you do not want ASP.NET code contain explicit text of MDX queries, right? What is your real goal here? Do you want to hide the text of the MDX queries from the writer of ASP.NET code?

How about creating some .NET component accepting a reference to the connection object, a name of the "stored procedure" and list of parameters? The component would fetch the real text from some other store, use the passed connection and parameters and pass the query to the server. Then return the results back to the client.

This is really not that useful, but maybe it will cover your goal.|||

Hi Andrew

Thanks for your answer.

Well the goal would be that, other applications could access the same Queries, without copying the Queries in their application code. However we decided in the meanwhile that we use the queries in the code, because we dont have any time to search other clean solutions :-)

But still i am interested in a clean splitted Query Logic -> Application Logic solution. If anyone knows? Please let me know.

Sincerelly

David

|||By other applications you probably mean those also developed by you, right? The applications could load the component. It could also accept references to a store of MDX queries so that different scenarios would load different stores and keep the queries in memory (so that not to have performance problems in multithreaded scenarios).sql

execute xp_cmdshell and other SA storedproc

Hi all,

I have to execute stored procedures containing
xp_cmdshell and certain system storedprocedures in msdb and master
with a user who is not SA.
(i.e iam able to execute stored procedures when i log as sa,
but any other user cannot run them)

Pls tell how to do this, it is quite urgent.Books online reviews:
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft Windows NT 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.

Follow the corresponding links on BOOKS ONLINE about this topic.

EXECUTE permission denied on object

I've taken over a project where the datasets are running stored
procedures on a database that I don't have permissions to execute
stored procedures.
I've changed the shared data source to a database I can use but I'm
getting the above message that still refers to the original database.
I don't know of anywhere else that I need to change.
Any ideas?andy
Grant EXECUTE permissions on the object. However , if a stopred procedure
uses dynamic sql within , you'll have to grant SELECT/UPDATE/DELETE
permissions on the undelaying tables as well
<andy.williams1971@.ntlworld.com> wrote in message
news:1162977266.829057.169620@.b28g2000cwb.googlegroups.com...
> I've taken over a project where the datasets are running stored
> procedures on a database that I don't have permissions to execute
> stored procedures.
> I've changed the shared data source to a database I can use but I'm
> getting the above message that still refers to the original database.
> I don't know of anywhere else that I need to change.
> Any ideas?
>|||What do you mean grant EXECUTE permissions on the object? I am new to
Reporting services and have a very limited programming knowledge, and
unfortunately there is nobody on site who knows Reporting Services.
I work in an environment which I have full permissions to
execute/create/delete stored procedures. This environment is called
'Rules'. The initial database (to which I have no access, and am not
allowed access) is called 'Dev'. I've changed the datasource from Dev
to Rules but I'm getting the error message EXECUTE permissions denied
on object ........., database 'dev', owner 'dbo'
Uri Dimant wrote:[vbcol=seagreen]
> andy
> Grant EXECUTE permissions on the object. However , if a stopred procedure
> uses dynamic sql within , you'll have to grant SELECT/UPDATE/DELETE
> permissions on the undelaying tables as well
>
>
> <andy.williams1971@.ntlworld.com> wrote in message
> news:1162977266.829057.169620@.b28g2000cwb.googlegroups.com...|||Andy
Right click on the stored procedure and click Properties. At the left -top
click on Permission button and click Execute for specific user
<andy.williams1971@.ntlworld.com> wrote in message
news:1162978403.280403.69650@.e3g2000cwe.googlegroups.com...
> What do you mean grant EXECUTE permissions on the object? I am new to
> Reporting services and have a very limited programming knowledge, and
> unfortunately there is nobody on site who knows Reporting Services.
> I work in an environment which I have full permissions to
> execute/create/delete stored procedures. This environment is called
> 'Rules'. The initial database (to which I have no access, and am not
> allowed access) is called 'Dev'. I've changed the datasource from Dev
> to Rules but I'm getting the error message EXECUTE permissions denied
> on object ........., database 'dev', owner 'dbo'
>
> Uri Dimant wrote:
>|||I'm sorry for being useless, but I've looked on the data tab and I
cannot get the properties window for the stored procedure
Uri Dimant wrote:[vbcol=seagreen]
> Andy
> Right click on the stored procedure and click Properties. At the left -top
> click on Permission button and click Execute for specific user
>
> <andy.williams1971@.ntlworld.com> wrote in message
> news:1162978403.280403.69650@.e3g2000cwe.googlegroups.com...|||I meant you have Enterprise Manager Tool installed on the computer.
<andy.williams1971@.ntlworld.com> wrote in message
news:1162981702.059182.305600@.k70g2000cwa.googlegroups.com...
> I'm sorry for being useless, but I've looked on the data tab and I
> cannot get the properties window for the stored procedure
> Uri Dimant wrote:
>|||> I've changed the shared data source to a database I can use but I'm
> getting the above message that still refers to the original database.
Are you running the report through the VS IDE or after deployment? If you
change the data source and run the report from the IDE, the new database
should be used and reflected in any error messages. However, when you
deploy a report, existing data sources on the report server are not
overwritten unless you specify 'true' for the data source overwrite
property.
Hope this helps.
Dan Guzman
SQL Server MVP
<andy.williams1971@.ntlworld.com> wrote in message
news:1162977266.829057.169620@.b28g2000cwb.googlegroups.com...
> I've taken over a project where the datasets are running stored
> procedures on a database that I don't have permissions to execute
> stored procedures.
> I've changed the shared data source to a database I can use but I'm
> getting the above message that still refers to the original database.
> I don't know of anywhere else that I need to change.
> Any ideas?
>

EXECUTE permission denied on object

I've taken over a project where the datasets are running stored
procedures on a database that I don't have permissions to execute
stored procedures.
I've changed the shared data source to a database I can use but I'm
getting the above message that still refers to the original database.
I don't know of anywhere else that I need to change.
Any ideas?
andy
Grant EXECUTE permissions on the object. However , if a stopred procedure
uses dynamic sql within , you'll have to grant SELECT/UPDATE/DELETE
permissions on the undelaying tables as well
<andy.williams1971@.ntlworld.com> wrote in message
news:1162977266.829057.169620@.b28g2000cwb.googlegr oups.com...
> I've taken over a project where the datasets are running stored
> procedures on a database that I don't have permissions to execute
> stored procedures.
> I've changed the shared data source to a database I can use but I'm
> getting the above message that still refers to the original database.
> I don't know of anywhere else that I need to change.
> Any ideas?
>
|||What do you mean grant EXECUTE permissions on the object? I am new to
Reporting services and have a very limited programming knowledge, and
unfortunately there is nobody on site who knows Reporting Services.
I work in an environment which I have full permissions to
execute/create/delete stored procedures. This environment is called
'Rules'. The initial database (to which I have no access, and am not
allowed access) is called 'Dev'. I've changed the datasource from Dev
to Rules but I'm getting the error message EXECUTE permissions denied
on object ........., database 'dev', owner 'dbo'
Uri Dimant wrote:[vbcol=seagreen]
> andy
> Grant EXECUTE permissions on the object. However , if a stopred procedure
> uses dynamic sql within , you'll have to grant SELECT/UPDATE/DELETE
> permissions on the undelaying tables as well
>
>
> <andy.williams1971@.ntlworld.com> wrote in message
> news:1162977266.829057.169620@.b28g2000cwb.googlegr oups.com...
|||Andy
Right click on the stored procedure and click Properties. At the left -top
click on Permission button and click Execute for specific user
<andy.williams1971@.ntlworld.com> wrote in message
news:1162978403.280403.69650@.e3g2000cwe.googlegrou ps.com...
> What do you mean grant EXECUTE permissions on the object? I am new to
> Reporting services and have a very limited programming knowledge, and
> unfortunately there is nobody on site who knows Reporting Services.
> I work in an environment which I have full permissions to
> execute/create/delete stored procedures. This environment is called
> 'Rules'. The initial database (to which I have no access, and am not
> allowed access) is called 'Dev'. I've changed the datasource from Dev
> to Rules but I'm getting the error message EXECUTE permissions denied
> on object ........., database 'dev', owner 'dbo'
>
> Uri Dimant wrote:
>
|||I'm sorry for being useless, but I've looked on the data tab and I
cannot get the properties window for the stored procedure
Uri Dimant wrote:[vbcol=seagreen]
> Andy
> Right click on the stored procedure and click Properties. At the left -top
> click on Permission button and click Execute for specific user
>
> <andy.williams1971@.ntlworld.com> wrote in message
> news:1162978403.280403.69650@.e3g2000cwe.googlegrou ps.com...
|||I meant you have Enterprise Manager Tool installed on the computer.
<andy.williams1971@.ntlworld.com> wrote in message
news:1162981702.059182.305600@.k70g2000cwa.googlegr oups.com...
> I'm sorry for being useless, but I've looked on the data tab and I
> cannot get the properties window for the stored procedure
> Uri Dimant wrote:
>
|||> I've changed the shared data source to a database I can use but I'm
> getting the above message that still refers to the original database.
Are you running the report through the VS IDE or after deployment? If you
change the data source and run the report from the IDE, the new database
should be used and reflected in any error messages. However, when you
deploy a report, existing data sources on the report server are not
overwritten unless you specify 'true' for the data source overwrite
property.
Hope this helps.
Dan Guzman
SQL Server MVP
<andy.williams1971@.ntlworld.com> wrote in message
news:1162977266.829057.169620@.b28g2000cwb.googlegr oups.com...
> I've taken over a project where the datasets are running stored
> procedures on a database that I don't have permissions to execute
> stored procedures.
> I've changed the shared data source to a database I can use but I'm
> getting the above message that still refers to the original database.
> I don't know of anywhere else that I need to change.
> Any ideas?
>