ALTER TABLE In cursor

Hi, All
I have a cursor which sets the value of @.tablename to a user table and
another cursor which set the @.contraint to a valid contraint name.
Why does the statement below produce a incorrect syntax error
alter table @.tablename drop constraint @.constraint
If I substitute the variables with actual values it works fine
Please helpYou can't use parameters in an ALTER TABLE statement. Use:
EXEC ('alter table ' + @.tablename + ' drop constraint ' + @.constraint)
--
Jacco Schalkwijk
SQL Server MVP
"Antony" <Antony@.discussions.microsoft.com> wrote in message
news:89619AFE-048B-4002-AEF4-E7D0A1EA527D@.microsoft.com...
> Hi, All
> I have a cursor which sets the value of @.tablename to a user table and
> another cursor which set the @.contraint to a valid contraint name.
> Why does the statement below produce a incorrect syntax error
> alter table @.tablename drop constraint @.constraint
> If I substitute the variables with actual values it works fine
> Please help

ALTER TABLE In cursor

Hi, All
I have a cursor which sets the value of @.tablename to a user table and
another cursor which set the @.contraint to a valid contraint name.
Why does the statement below produce a incorrect syntax error
alter table @.tablename drop constraint @.constraint
If I substitute the variables with actual values it works fine
Please help
You can't use parameters in an ALTER TABLE statement. Use:
EXEC ('alter table ' + @.tablename + ' drop constraint ' + @.constraint)
Jacco Schalkwijk
SQL Server MVP
"Antony" <Antony@.discussions.microsoft.com> wrote in message
news:89619AFE-048B-4002-AEF4-E7D0A1EA527D@.microsoft.com...
> Hi, All
> I have a cursor which sets the value of @.tablename to a user table and
> another cursor which set the @.contraint to a valid contraint name.
> Why does the statement below produce a incorrect syntax error
> alter table @.tablename drop constraint @.constraint
> If I substitute the variables with actual values it works fine
> Please help

Allowed Set for All Members

It looks like NULL is treated the same way as an empty set {} for allowed/denied sets.If I use dynamic security by calling to an SSAS stored procedure but I want to allow the users of an admin role to see all members, what should the stored procedure return?It doesn't matter how you set up security - the database administrators (and server administrators too) will always be able to see everything. Security definitions are not even evaluated for them.|||Thank you, Mosha. I understand this. In this case, I was referring to an "application" administrator. So, when I use dynamic security and call down to the application security infrastructure and the application tells me that the user is an application administrator, what do I need to return as a set from the SSAS stored procedure to allow all members?|||Depends on the return type of your sproc. If it is a string - then you can return empty string, and it should be fine. But if your sproc returns AdomdServer.Set, then I don't have very good advice. Obviously returning set of all members for the attribute - i.e. Dim.Attr.Attr.MEMBERS seems the right thing to do, but you may have problems including in sproc hidden members etc. Dimension security itself when it evaluated expression Dim.Attr.Attr.MEMBERS would do the right thing though.|||

I am affraid this doesn't work for me. Given this stored procedure

public static string GetCustomer(bool admin) {

return admin ? String.Empty : "{[Customer].[Customer].&[15]}";

}

String.Empty, "", null don't work. Nor do {[Customer].[Customer].&[15]} or StrToSet('{[Customer].[Customer].&[15]}') although plugging in {[Customer].[Customer].&[15]} in the allowed set expression works. The stored procedure executes succesfully but the server throws the following exception on browse:

The function expects a tuple set expression for the argument. A string or numeric expression was used. The 'Customer attribute in the 'Customer' dimension has a generated dimension security expression that is not valid. What am I doing wrong?

|||

Your sproc looks good. In the allowed set expression you should use the following expression: StrToSet(GetCustomer(admin))

|||

Thanks, Mosha. I was just writing an update when I got your reply. Yes, StrToSet(Extensibility.GetCustomer(false)) works. But StrToSet(Extensibility.GetCustomer(true)) doesn't. I tested with "", String.Empty, or null. So, back to my original question, what should the sp return if I wan't to void the allowed set and allow all members?

|||

Perhaps the following will work for you:

IIF(admin, "", Extensibility.GetCustomer(false))

|||

Yes, but I don't know if the user is admin. I stubbed out the stored procedure for testing purposes only. Recall that the application security layer knows who the user is given the user Windows identity. I would get back a list of the allowed members. But since an admin user has access to all members (of a very large dimension), I would like to short-curcuit the filter. So, if the security layer returns null (or whatever the convention is for admin), it would be faster to nuke the filter as opposed to getting/setting a set with all members.

Hope this makes sense.

|||BTW, IIF(admin, "", Extensibility.GetCustomer(false)) or simply "" don't work too. It looks like the only input that allowed/denied set filters support is a set with no option to set the filter to empty.