Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 765
  • Last Modified:

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?
0
tim_cs
Asked:
tim_cs
  • 4
  • 2
  • 2
  • +1
5 Solutions
 
HuaMinChenBusiness AnalystCommented:
Try
charindex(@status,'StandBy')=0

to check that @status does not have 'StandBy' inside at all! Is this what you need?
0
 
Scott PletcherSenior DBACommented:
WHERE
    @Status|Status NOT LIKE '%Standby%'

I would avoid using CHARINDEX, because then SQL can't even consider using an index for the match, even if one exists on the column.
0
 
tim_csAuthor Commented:
HuaMinChen,
   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.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Anthony PerkinsCommented:
I suggest you add the @Status condition twice once for each case.
0
 
HuaMinChenBusiness AnalystCommented:
Tim,
Sorry, it should be this instead
charindex('StandBy',@status)=0

Can you please show your full script here?
0
 
Anthony PerkinsCommented:
Can you please show your full script here?
The author is not using T-SQL they are setting up a Policy-Based Management condition.
0
 
tim_csAuthor Commented:
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.
0
 
Scott PletcherSenior DBACommented:
You're using denormalized data.  A list of values in one column is not a valid relational structure.  It's likely Policy wasn't designed to handle unexpected situations like that.
0
 
tim_csAuthor Commented:
The @Status value is supplied by SQL Server, it's not something I'm passing in so I can't help that it's denormalized.

I've switched to just checking if the Database is ReadOnly which will work.  I figured out that using the Array() function with @Status will only return INT values and once you use any of the built in functions the condition can no longer be used for filtering.
0
 
tim_csAuthor Commented:
Other solutions didn't work and found a work around but appreciated the suggestions.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now