SQLCLR default procedure parameters scripts correctly by SSMS and SMO 2008

Although being able to use a GUI tool like SQL Server Management Studio is a nice feature by itself, it's unusual that fixing something in a GUI makes a feature (that hasn't changed) more useable. The "feature" is defaults on SQLCLR procedure parameters. You can't have defaults on parameters in .NET, but you can specify […]

Programming Policy-Based Management with SMO – Part 6 – Categories, Subscriptions, Wrapup

This is the last part of a series on programming policy-based management. The series starts here. In the previous installment, I created a policy that was constrained to a single database. To accomplish this, I used a Condition that called out the database by name, and tied it to the TargetSet using TargetSet's SetLevelCondition method. […]

Programming Policy-Based Management with SMO – Part 5 – TargetSets and TargetSetLevels

This post is part of a series on programming policy-based management. The series begins here. So, we were working with a policy that required an ObjectSet. ObjectSets contain TargetSets. For example, the ObjectSet for the naming policy (IMultipartName) we were working on needs a TargetSet for Procedure, Synonym, Table, Function, Type, View, and XmlSchemaCollection. Note […]

Programming Policy-Based Management with SMO – Part 4 – Introducing ObjectSets

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 […]

Programming Policy-Based Management with SMO – Part 3 – ExpressionNode and Policy

This is part three of a series on programming policy-based management. The series starts here. So, to initialize my Condition's ExpressionNode I need more than just a string. It doesn't look like, at this time, every ExpressionNode CAN initialized with a string. But we can use the subclasses. The ones I need here are Operator, […]

Programming Policy-Based Management with SMO – Part 2 – Conditions

This is the second part in a series about programming policy-based management. The series starts here. To build our MailOffByDefault policy we need:    Condition that specifies properties and settings    Policy that uses the condition Condition first. This looks pretty straighforward. Condition con1 = new Condition(ps, "MailOffSMO"); con1.Facet = "ISurfaceAreaFacet"; con1.ExpressionNode = ExpressionNode.Parse(                         […]

Programming Policy-Based Management with SMO – Part 1 – Intro

I've been looking at the new Policy-Based Management (was: Declarative Management Framework) in SQL Server BOL. All of the BOL examples use the SSMS user-interface to define and maintain the policy store. While there will likely be 3 ways to configure PBM as there is with, say, replication (SSMS, SMO, and system stored procs), I […]

SMO and Powershell: Better Together, The Scripts

Sorry, I know that slogan has been used already. Thanks to everyone who showed up for the SMO/Powershell chalk talk at TechEd Barcelona today. Here, as promised, are the demos. Thanks especially to Jeffrey Snover, the "dad" of Powershell for showing up and showing me some neat shortcuts… as I typed. Many of the scripts were […]

SMO scripting at TechEd Europe

I'm in Barcelona in TechEd and tomorrow I'm going to be doing a chalk talk on SQL Server Management Objects (SMO). Although I'd usually done my SMO coding in C#, I decided (based on my co-author Dan Sullivan's blog post on the subject) to try out using Powershell along with SMO. So, although when most folks […]

SMO and object-relational mapping

I've been looking at the SMO (SQL Server Management Objects) model recently. Because the amount of data they retrieve is potentially large and potentially expensive to produce (say sp_spaceused on every database), they use most/all of the *data transfer optimization* techniques: both sparse and eager population, lazy instanciation, caching, deferred/chunky writeback, and locality of reference. […]