How to retrieve unary operator for member?

Hi to everyone!

How to retrieve the value of the "UNARY_OPERATOR" property for a given dimension member?
MDSCHEMA_PROPERTIES schema contains information about this property but there is no such item in Properties collection of ADOMD.Member object.

I use the final version of SSAS2005 and MSOLAP.3 provider. I connect to server via ADOMD (not ADOMD.NET).
I have just discovered the way, but I think it's rather strange. You can execute a query with DIMENSION PROPERTIES statement like this:

SELECT { [Account].[Accounts].AllMembers } DIMENSION PROPERTIES UNARY_OPERATOR
ON ROWS, { [Category].[Category].Levels(0).AllMembers }
ON COLUMNS FROM [Finance] WHERE ([Measures].[Amount]
)

and needed property value is included in query results.

I'm still looking for another solution...

How to retrieve the actions using MDSCHEMA_ACTIONS?

Hello,

When I run the following code, I get an empty schema.

Dim drAction As DataRow

Dim dsActions As DataSet

dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _

New Object() {szDatabase, Nothing, szCubeName, Nothing, Nothing, szCubeName, 1})

The result:

dsActions.Tables.Count = 1 but dsActions.Tables(0).Rows.Count = 0

Even though my cube contains two actions as it is shown in the following:

<Actions>

<Action xsi:type="DrillThroughAction">

<ID>Drillthrough Action</ID>

<Name>Drillthrough Action</Name>

<TargetType>Cells</TargetType>

<Target>MeasureGroupMeasures("Values Measure")</Target>

<Type>DrillThrough</Type>

<Default>true</Default>

<MaximumRows>3</MaximumRows>

</Action>

<Action xsi:type="DrillThroughAction">

<ID>Drillthrough Action 1</ID>

<Name>Drillthrough Action 1</Name>

<TargetType>Cells</TargetType>

<Target>MeasureGroupMeasures("Values Measure")</Target>

<Type>DrillThrough</Type>

<Default>true</Default>

<MaximumRows>2</MaximumRows>

</Action>

</Actions>

Thanks,

yones

hello Yones,

i think the problem is with the restrictions you specify. I think coordinate and coordinate type in this case should be: Coordinate type should be Cell (6) and Coordinate should be a tuple defining your cell. (right now it looks like they are for cube object). So for example the query could look like (for Adventure Works)

dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _

New Object() {

"Adventure Works DW", // CATALOG_NAME

Nothing, // SCHEMA_NAME

"Adventure Works", // CUBE_NAME

Nothing, // ACTION_NAME

Nothing, // ACTION_TYPE

"([Customer].[Customer Geography].[Country].&[Canada],[Measures].[Internet Extended Amount])", // COORDINATE

6}) // COORDINATE_TYPE : MDACTION_COORDINATE_CELL (6)

hope this helps,

|||

Hello Mary,

First thank you very much for your help.

I tried Cell actions instead of Cube actions as it is shown in the following code:

dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _

New Object() {"AmoAdventureWorks", Nothing, "Adventure Works", Nothing, Nothing, "[Date].[Calendar Month Name].[All Periods],[Customer].[City].[All Customers],[Measures].[Reseller Sales Amount]", 6})

But the result was the same:

dsActions.Tables.Count = 1

dsActions.Tables(0).Rows.Count = 0

And my cube contains the following action:

<Actions>

<Action xsi:type="DrillThroughAction">

<ID>Drillthrough Action 1</ID>

<Name>Drillthrough Action</Name>

<TargetType>Cells</TargetType>

<Target>MeasureGroupMeasures("Reseller Sales")</Target>

<Type>DrillThrough</Type>

<Default>true</Default>

<MaximumRows>4</MaximumRows>

</Action>

</Actions>

Please let me know if you notice any wrong in my code.

Again thank you

Yones|||

hello Yones,

i think you might be missing () inside a coordinate. I.e. i think it should be "([Date].[Calendar Month Name].[All Periods],[Customer].[City].[All Customers],[Measures].[Reseller Sales Amount])".

hope this helps,

How to retrieve the actions using MDSCHEMA_ACTIONS?

Hello,

When I run the following code, I get an empty schema.

Dim drAction As DataRow

Dim dsActions As DataSet

dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _

New Object() {szDatabase, Nothing, szCubeName, Nothing, Nothing, szCubeName, 1})

The result:

dsActions.Tables.Count = 1 but dsActions.Tables(0).Rows.Count = 0

Even though my cube contains two actions as it is shown in the following:

<Actions>

<Action xsi:type="DrillThroughAction">

<ID>Drillthrough Action</ID>

<Name>Drillthrough Action</Name>

<TargetType>Cells</TargetType>

<Target>MeasureGroupMeasures("Values Measure")</Target>

<Type>DrillThrough</Type>

<Default>true</Default>

<MaximumRows>3</MaximumRows>

</Action>

<Action xsi:type="DrillThroughAction">

<ID>Drillthrough Action 1</ID>

<Name>Drillthrough Action 1</Name>

<TargetType>Cells</TargetType>

<Target>MeasureGroupMeasures("Values Measure")</Target>

<Type>DrillThrough</Type>

<Default>true</Default>

<MaximumRows>2</MaximumRows>

</Action>

</Actions>

Thanks,

yones

hello Yones,

i think the problem is with the restrictions you specify. I think coordinate and coordinate type in this case should be: Coordinate type should be Cell (6) and Coordinate should be a tuple defining your cell. (right now it looks like they are for cube object). So for example the query could look like (for Adventure Works)

dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _

New Object() {

"Adventure Works DW", // CATALOG_NAME

Nothing, // SCHEMA_NAME

"Adventure Works", // CUBE_NAME

Nothing, // ACTION_NAME

Nothing, // ACTION_TYPE

"([Customer].[Customer Geography].[Country].&[Canada],[Measures].[Internet Extended Amount])", // COORDINATE

6}) // COORDINATE_TYPE : MDACTION_COORDINATE_CELL (6)

hope this helps,

|||

Hello Mary,

First thank you very much for your help.

I tried Cell actions instead of Cube actions as it is shown in the following code:

dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _

New Object() {"AmoAdventureWorks", Nothing, "Adventure Works", Nothing, Nothing, "[Date].[Calendar Month Name].[All Periods],[Customer].[City].[All Customers],[Measures].[Reseller Sales Amount]", 6})

But the result was the same:

dsActions.Tables.Count = 1

dsActions.Tables(0).Rows.Count = 0

And my cube contains the following action:

<Actions>

<Action xsi:type="DrillThroughAction">

<ID>Drillthrough Action 1</ID>

<Name>Drillthrough Action</Name>

<TargetType>Cells</TargetType>

<Target>MeasureGroupMeasures("Reseller Sales")</Target>

<Type>DrillThrough</Type>

<Default>true</Default>

<MaximumRows>4</MaximumRows>

</Action>

</Actions>

Please let me know if you notice any wrong in my code.

Again thank you

Yones|||

hello Yones,

i think you might be missing () inside a coordinate. I.e. i think it should be "([Date].[Calendar Month Name].[All Periods],[Customer].[City].[All Customers],[Measures].[Reseller Sales Amount])".

hope this helps,

How to retrieve schema bound to a Query?

Hi ,
Can you please tell me how to retrieve schema associated with a query? Here
is the sample query for which i want to know DATATYPE, LENGTH of every colum
n
which i'm selecting in the query.
SELECT Tbl2.Name, Tbl3.Age, Tbl1.ID, Ttb2.DOB
FROM Table1 Tbl1,
Table2 Tbl2
WHERE Tbl1.Key = Tbl2.Table1Key
Thanks in advance for spending your valuable time to look in to this
problem. I would be very much thankfull to you if you help me to write SQL
script( or C# code) to get the schema bound with the query.
Regards,
Gopinath MGopi Try this
select * from information_schema.columns where table_Name = Table1 and
COLUMN_NAME IN([Name], Age, [ID], DOB)
Regards
R.D
"Gopinath M" wrote:

> Hi ,
> Can you please tell me how to retrieve schema associated with a query? Her
e
> is the sample query for which i want to know DATATYPE, LENGTH of every col
umn
> which i'm selecting in the query.
> SELECT Tbl2.Name, Tbl3.Age, Tbl1.ID, Ttb2.DOB
> FROM Table1 Tbl1,
> Table2 Tbl2
> WHERE Tbl1.Key = Tbl2.Table1Key
>
> Thanks in advance for spending your valuable time to look in to this
> problem. I would be very much thankfull to you if you help me to write SQL
> script( or C# code) to get the schema bound with the query.
> Regards,
> Gopinath M
>
>|||Hi R.D,
Thank you very much for spending your valuable time. The script you
have given will work if i know the table and the column for which i want to
retrive schema well in advance. But my requiremet is "a user inputs a query
and i want to find out schema associated with that query".
Users are allowed to key in a valid sql select statement. For this
select statement i want to retrieve schema. Also, i dont have access to
create(any DDL) any type of temporary table in the production environment fo
r
retrieving schema. I'm struck up with the problem for a couple days. Please
help me in solving this.
Regards,
Gopinath M
"R.D" wrote:
> Gopi Try this
> select * from information_schema.columns where table_Name = Table1 and
> COLUMN_NAME IN([Name], Age, [ID], DOB)
> Regards
> R.D
> "Gopinath M" wrote:
>|||gopi
you got to query VIEW : information_schema.columns
where all properties of columns are listed.
Regards
R.D
"Gopinath M" wrote:
> Hi R.D,
> Thank you very much for spending your valuable time. The script you
> have given will work if i know the table and the column for which i want t
o
> retrive schema well in advance. But my requiremet is "a user inputs a que
ry
> and i want to find out schema associated with that query".
> Users are allowed to key in a valid sql select statement. For this
> select statement i want to retrieve schema. Also, i dont have access to
> create(any DDL) any type of temporary table in the production environment
for
> retrieving schema. I'm struck up with the problem for a couple days. Pleas
e
> help me in solving this.
> Regards,
> Gopinath M
>
> "R.D" wrote:
>|||R.D,
Is there any logic or script which will identify the table name of every
column we listed in the select statment?
If i'm able to idenfity the table name associated with a column say tbl1.ID
then i can query the system views to get the information for datatype of
column ID in the table Table1( as aliased tbl1).
Also, when i get an expression in the select statement like
SELECT (2 * tbl1.Id) FROM XYXTABLE tbl
then i need a logic to find the type of data the query is giong to return.
i've a solution like this... if i create a view on the database with the
user select statement i can use Sysobject and syscolumns table to retrive
column datatypes and length. But as our applications are going to run on
highly secured production environment, i dont have rights to create view or
temporary tables.
"R.D" wrote:
> gopi
> you got to query VIEW : information_schema.columns
> where all properties of columns are listed.
> Regards
> R.D
> "Gopinath M" wrote:
>|||Gopi
can you join Information.schema.columns with your query using tablename.col
= sysview.col.
That is possible. You may try
Regards
R.D
"Gopinath M" wrote:
> R.D,
> Is there any logic or script which will identify the table name of every
> column we listed in the select statment?
> If i'm able to idenfity the table name associated with a column say tbl1.I
D
> then i can query the system views to get the information for datatype of
> column ID in the table Table1( as aliased tbl1).
> Also, when i get an expression in the select statement like
> SELECT (2 * tbl1.Id) FROM XYXTABLE tbl
> then i need a logic to find the type of data the query is giong to return.
> i've a solution like this... if i create a view on the database with the
> user select statement i can use Sysobject and syscolumns table to retrive
> column datatypes and length. But as our applications are going to run on
> highly secured production environment, i dont have rights to create view o
r
> temporary tables.
>
> "R.D" wrote:
>

How to restrict evil create scripts?

Hi,

For a service I'm working on I need to ask the user for their database
create script. It's used to re-create the users database schema in a
temporary database on a in-house server in an automated fashion.

For security reasons, I need to be sure that the create script can only
create tables, columns etc and not things like snooping in other
databases and/or formatting the server.

Can you give me pointers about what the minimum grants are to let good
script execute successfully and evil scripts fail?

Regards,

WardWard,

I would say as long as your permissions are set right you don't have to
worry. If your user only have rights in their own databases they
shouldn't be able to accessother user databases. Xp_Cmdshell which
would be able to delete files or run other OS commands is (by default)
only available to members of the sysadmin role.
So I would say create a empty datbase and grant the user only db_owner
or ddl_admin rights within the database.

Markus|||Ward Bekker (ward@.NospaaMequanimity.nl) writes:

Quote:

Originally Posted by

For a service I'm working on I need to ask the user for their database
create script. It's used to re-create the users database schema in a
temporary database on a in-house server in an automated fashion.
>
For security reasons, I need to be sure that the create script can only
create tables, columns etc and not things like snooping in other
databases and/or formatting the server.
>
Can you give me pointers about what the minimum grants are to let good
script execute successfully and evil scripts fail?

First of all, which version of SQL Server including service pack do you
have?

As M.Bohse said, run the scripts as a user who only have access in that
database, although in that database he need some privs. Very important:
make sure that cross-database chaining is turned off, and that the
database is not set as trustworthy on SQL 2005.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,

Thanks for the tips!

We are running SQL Server 2005.

Ward

Erland Sommarskog wrote:

Quote:

Originally Posted by

Ward Bekker (ward@.NospaaMequanimity.nl) writes:

Quote:

Originally Posted by

>For a service I'm working on I need to ask the user for their database
>create script. It's used to re-create the users database schema in a
>temporary database on a in-house server in an automated fashion.
>>
>For security reasons, I need to be sure that the create script can only
>create tables, columns etc and not things like snooping in other
>databases and/or formatting the server.
>>
>Can you give me pointers about what the minimum grants are to let good
>script execute successfully and evil scripts fail?

>
First of all, which version of SQL Server including service pack do you
have?
>
As M.Bohse said, run the scripts as a user who only have access in that
database, although in that database he need some privs. Very important:
make sure that cross-database chaining is turned off, and that the
database is not set as trustworthy on SQL 2005.
>
>

|||Ward Bekker (ward@.NospaaMequanimity.nl) writes:

Quote:

Originally Posted by

Hi Erland,
>
Thanks for the tips!
>
We are running SQL Server 2005.

In that case you should grant CONTROL on the dbo schema for the database
user. Maybe they are creating other scheams, but then they need to include
GRANTs for that.

You should also use

select name, is_trustworthy_on, is_db_chaining_on
from sys.databases

to make sure that the databases are not marked as trustworthy, or available
for DB-chaning.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How to restrict evil create scripts?

Hi,
For a service I'm working on I need to ask the user for their database
create script. It's used to re-create the users database schema in a
temporary database on a in-house server in an automated fashion.
For security reasons, I need to be sure that the create script can only
create tables, columns etc and not things like snooping in other
databases and/or formatting the server.
Can you give me pointers about what the minimum grants are to let good
script execute successfully and evil scripts fail?
Regards,
Ward
Ward Bekker
"Asp.Net Discussions for the Professional Developer"
http://www.dotnettaxi.com
"Free .Net 2.0 C# to/from VB.Net Code Converter"
http://www.dotnettaxi.com/Tools/Converter.aspxI would consider using Virtual Server isolation.
Set up a virtual server, save copies of the vhd/vmc files (set the file
properties to read only), and then each time you have such a script, use a
virtualized environment and 'evil' scripts won't have anything to sniff
around it.
When finished, just delete the VM.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Ward Bekker" <ward@.NospaaMequanimity.nl> wrote in message
news:eb7es3$c9a$1@.news.cistron.nl...
> Hi,
> For a service I'm working on I need to ask the user for their database
> create script. It's used to re-create the users database schema in a
> temporary database on a in-house server in an automated fashion.
> For security reasons, I need to be sure that the create script can only
> create tables, columns etc and not things like snooping in other databases
> and/or formatting the server.
> Can you give me pointers about what the minimum grants are to let good
> script execute successfully and evil scripts fail?
> Regards,
> Ward
>
> --
> Ward Bekker
> "Asp.Net Discussions for the Professional Developer"
> http://www.dotnettaxi.com
> "Free .Net 2.0 C# to/from VB.Net Code Converter"
> http://www.dotnettaxi.com/Tools/Converter.aspx|||Hi Arnie,
Interesting approach. Is great as a "second line" of defense. SQL Server
can have advanced security configuration, so I was wondering what could
be done in that level. Do you have any idea's about how that?
Thank you,
Ward
Arnie Rowland wrote:
> I would consider using Virtual Server isolation.
> Set up a virtual server, save copies of the vhd/vmc files (set the file
> properties to read only), and then each time you have such a script, use a
> virtualized environment and 'evil' scripts won't have anything to sniff
> around it.
> When finished, just delete the VM.
>