tim_cs
asked on
Policy Based Management -- Using NOT IN
This is for SQL 2008. I've created a policy to check the recovery model of my database. I had also created a filter that limited the policy to online user databases only.
I'm now trying to exclude any databases that are being log shipped to, one in particular is in StandBy. I created a new expression in the condition where @Status != "StandBy". This won't work as the actual @Status is "Normal, Standby, Shutdown". Now I'm trying to use NOT IN but having no luck.
I keep getting "Operator 'NOT IN' must have a value as its left operand and its right operand must be an Array."
Any help in how to exclude a Database that is in StandBy mode or being log shipped to?
I'm now trying to exclude any databases that are being log shipped to, one in particular is in StandBy. I created a new expression in the condition where @Status != "StandBy". This won't work as the actual @Status is "Normal, Standby, Shutdown". Now I'm trying to use NOT IN but having no luck.
I keep getting "Operator 'NOT IN' must have a value as its left operand and its right operand must be an Array."
Any help in how to exclude a Database that is in StandBy mode or being log shipped to?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Tim,
Sorry, it should be this instead
charindex('StandBy',@statu s)=0
Can you please show your full script here?
Sorry, it should be this instead
charindex('StandBy',@statu
Can you please show your full script here?
Can you please show your full script here?
The author is not using T-SQL they are setting up a Policy-Based Management condition.
The author is not using T-SQL they are setting up a Policy-Based Management condition.
ASKER
acperkins,
No luck setting more than one @status. Each of those expressions fails as the actual @Status value is "Normal, Standby, Shutdown".
HuaMinChen,
It won't except charindex as a valid function for the expression.
I've read through the white papers for this and searched a lot without any luck. This is not a well documented feature of SQL.
No luck setting more than one @status. Each of those expressions fails as the actual @Status value is "Normal, Standby, Shutdown".
HuaMinChen,
It won't except charindex as a valid function for the expression.
I've read through the white papers for this and searched a lot without any luck. This is not a well documented feature of SQL.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Other solutions didn't work and found a work around but appreciated the suggestions.
ASKER
Getting this error with yours: "Requested value 'CHARINDEX' was not found."
ScottPletcher,
NOT LIKE isn't an available option for creating a policy condition. Only =, !=, >, >=, <, <=, IN, & NOT IN.