• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 778
  • 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
 
HuaMin ChenSystem 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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Anthony PerkinsCommented:
I suggest you add the @Status condition twice once for each case.
0
 
HuaMin ChenSystem 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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