{"id":706,"date":"2008-03-17T22:04:00","date_gmt":"2008-03-17T22:04:00","guid":{"rendered":"\/blogs\/bobb\/post\/Programming-Policy-Based-Management-with-SMO-Part-4-Introducing-ObjectSets.aspx"},"modified":"2013-01-04T01:49:55","modified_gmt":"2013-01-04T09:49:55","slug":"programming-policy-based-management-with-smo-part-4-introducing-objectsets","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/programming-policy-based-management-with-smo-part-4-introducing-objectsets\/","title":{"rendered":"Programming Policy-Based Management with SMO – Part 4 – Introducing ObjectSets"},"content":{"rendered":"

\nThis is part of a series on programming policy-based management. The series starts here<\/a>.\n<\/p>\n

\nNow, we'll tackle programming a little bit more complicated policy. The table-naming standard that applies to a set of database objects. For this, we need three items:
\n1. Condition for defining the policy itself.
\n2. Policy that uses the condition and contains….
\n3. Condition that specifies a set of database objects to which the policy should be applied.\n<\/p>\n

\nThe first condition is straightforward\n<\/p>\n

\nCondition con = new Condition(ps, "TablePattern");
\ncon.Facet = "IMultipartNameFacet";
\nExpressionNode exp = new ExpressionNodeOperator(OperatorType.LIKE,
\n    new ExpressionNodeAttribute("Name"),
\n    new ExpressionNodeConstant("fintbl%")
\n    );
\ncon.ExpressionNode = exp;
\ncon.Create();\n<\/p>\n

\nNote that, as with the complex condition we specified previously (part 3), you can't specify 'LIKE' by using the Parse() method, but 'LIKE' does appear when you access the condition by using ToString(). Perhaps, in future, Parse and ToString will be reflexive on an ExpressionNode. Or maybe I'm just missing something.\n<\/p>\n

\nThe first part of the policy is straightforward too:\n<\/p>\n

\nPolicy p2 = new Policy(ps, "CheckFinanceTab");
\np2.Condition = "TablePattern";
\np2.AutomatedPolicyExecutionMode = AutomatedPolicyExecutionMode.Enforce;
\np2.Enabled = true;\n<\/p>\n

\nBut we want this policy to apply to to the TABLES (not views, for example) and only those tables in the finance database. For this, we need an ObjectSet.\n<\/p>\n

\nObjectSet os1 = new ObjectSet(ps, "CheckFinanceTab_ObjectSet");
\nos1.Facet = "IMultipartNameFacet";\n<\/p>\n

\nNote that ObjectSets reference Facets too. Note also that ObjectSets are a "top-level" object, that is, in the PowerShell provider they appear at the same level as Conditions and Policies. Note that Facets do NOT appear at that level.\n<\/p>\n

\nBefore we go any further, cavaet… there is no function in SSMS to define an ObjectSet standalone. Therefore, no way to delete a "half-baked" ObjectSet from SSMS if you don't want it. To delete an ObjectSet you don't want, you need to go into the PowerShell provider, navigate to SQLSERVER:\\SQLPolicy\\{server}\\{instance}\\ObjectSets and delete it. "del MyObjectSet". In SSMS, deleting a policy that refers to an ObjectSet deletes the ObjectSet too. Not sure if you can have multiple policies refer to the same ObjectSet yet, but if you could I assume you'd have to delete all the policies before an ObjectSet would go away.<\/p>\n","protected":false},"excerpt":{"rendered":"

This is part of a series on programming policy-based management. The series starts here. Now, we'll tackle programming a little bit more complicated policy. The table-naming standard that applies to a set of database objects. For this, we need three items: 1. Condition for defining the policy itself. 2. Policy that uses the condition and […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20,25,29],"tags":[],"class_list":["post-706","post","type-post","status-publish","format-standard","hentry","category-policy-based-management","category-smo","category-sql-server-2008"],"yoast_head":"\nProgramming Policy-Based Management with SMO - Part 4 - Introducing ObjectSets - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/programming-policy-based-management-with-smo-part-4-introducing-objectsets\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Programming Policy-Based Management with SMO - Part 4 - Introducing ObjectSets - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"This is part of a series on programming policy-based management. The series starts here. Now, we'll tackle programming a little bit more complicated policy. The table-naming standard that applies to a set of database objects. For this, we need three items: 1. Condition for defining the policy itself. 2. Policy that uses the condition and […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/programming-policy-based-management-with-smo-part-4-introducing-objectsets\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2008-03-17T22:04:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-04T09:49:55+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/programming-policy-based-management-with-smo-part-4-introducing-objectsets\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/programming-policy-based-management-with-smo-part-4-introducing-objectsets\/\",\"name\":\"Programming Policy-Based Management with SMO - Part 4 - Introducing ObjectSets - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2008-03-17T22:04:00+00:00\",\"dateModified\":\"2013-01-04T09:49:55+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/programming-policy-based-management-with-smo-part-4-introducing-objectsets\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/programming-policy-based-management-with-smo-part-4-introducing-objectsets\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/programming-policy-based-management-with-smo-part-4-introducing-objectsets\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Policy-Based Management\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/policy-based-management\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Programming Policy-Based Management with SMO – Part 4 – Introducing ObjectSets\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Programming Policy-Based Management with SMO - Part 4 - Introducing ObjectSets - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/programming-policy-based-management-with-smo-part-4-introducing-objectsets\/","og_locale":"en_US","og_type":"article","og_title":"Programming Policy-Based Management with SMO - Part 4 - Introducing ObjectSets - Bob Beauchemin","og_description":"This is part of a series on programming policy-based management. The series starts here. Now, we'll tackle programming a little bit more complicated policy. The table-naming standard that applies to a set of database objects. For this, we need three items: 1. Condition for defining the policy itself. 2. Policy that uses the condition and […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/programming-policy-based-management-with-smo-part-4-introducing-objectsets\/","og_site_name":"Bob Beauchemin","article_published_time":"2008-03-17T22:04:00+00:00","article_modified_time":"2013-01-04T09:49:55+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/programming-policy-based-management-with-smo-part-4-introducing-objectsets\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/programming-policy-based-management-with-smo-part-4-introducing-objectsets\/","name":"Programming Policy-Based Management with SMO - Part 4 - Introducing ObjectSets - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2008-03-17T22:04:00+00:00","dateModified":"2013-01-04T09:49:55+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/programming-policy-based-management-with-smo-part-4-introducing-objectsets\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/programming-policy-based-management-with-smo-part-4-introducing-objectsets\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/programming-policy-based-management-with-smo-part-4-introducing-objectsets\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Policy-Based Management","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/policy-based-management\/"},{"@type":"ListItem","position":3,"name":"Programming Policy-Based Management with SMO – Part 4 – Introducing ObjectSets"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/706","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=706"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/706\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=706"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=706"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=706"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}