Wednesday, September 19, 2007
Anyone who knows me knows how important context is to me. If you don't know the context of a question, you'll never find the right answer. As Dr. Ivan Brady is so fond of saying, "context is practically everything when it comes to determining meaning." And in business intelligence projects, always focusing on the business context (as opposed to the technical context that most geeks love so much) is vital for the project's success.
I mention all this because different individual viewpoints are necessary to expand a business' cultural viewpoint - its context, if you will. If you only hire skinny men who love hip-hop, your company will think and act like an underweight male hip-hop fan as well. More viewpoints are a good thing.
But the female viewpoint is woefully underrepresented in the IT world today, much to our collective detriment.
Why do I mention this? The Configuresoft training session I'm attending this week has over 50% female attendees. (And they're the ones asking the tough questions - I'm glad the trainer knows what he's talking about!) In all of my years of delivering IT training, I don't think I have ever seen a majority of female students.
No, I don't have any conclusion to reach, but I wanted to make the observation anyway. I hope that this is part of an overall trend and not just an anomaly, because an expanded context is good for everyone...
 Hi Jeff!
 Configuresoft is my employer, and I need to get up to speed on the inner workings of our flagship ECM product to me more effective on the amazing, new CIA product that is my primary responsibility.
In any event, if you're going to be in Stockholm on the 11th of October, make sure you plan on attending the user group meeting - we'll have a lot of fun and can take as much time as you want with Q&A regardless of how much time is allotted for the session itself.
 No, I can't read anything on the web site.
Monday, September 17, 2007
In any event, El Reg had an interesting article on Silverlight today, including portions of an interview with Scott Guthrie. Even though it's not technically deep at all, I enjoyed its skeptical take on how Silverlight stands up to competition from Adobe and Google. Check it out.
Friday, September 14, 2007
The first approach involves loading the data from the source system into a table in the staging database without modifying the shape of the data, and then using a SQL Server table-valued function to split the delimited field during the process of loading the data from the staging database into the data warehouse. I did some searching for a decent "split" function online and found one here that did most of what I needed. The only significant thing that I added was support for a "key" field to be passed in and included in the return table, so that the records extracted from the delimited field could then be correlated easily with the data in the source record. Here's the function I ended up with:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KeySplit]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[KeySplit]
CREATE FUNCTION dbo.KeySplit
@Key NVARCHAR (4000)
,@String NVARCHAR (4000)
,@Delimiter NVARCHAR (10)
RETURNS @Results TABLE
KeyColumn NVARCHAR (4000)
,ItemColumn NVARCHAR (4000)
DECLARE @CurrentItem VARCHAR(8000)
WHILE CHARINDEX (@Delimiter ,@String, 0) <> 0
@CurrentItem = RTRIM (LTRIM (SUBSTRING (@String, 1, CHARINDEX (@Delimiter, @String, 0) -1))),
@String = RTRIM (LTRIM (SUBSTRING (@String, CHARINDEX (@Delimiter, @String, 0) + LEN (@Delimiter), LEN (@String))))
IF LEN(@CurrentItem) > 0
INSERT INTO @Results (KeyColumn, ItemColumn) SELECT @Key, @CurrentItem
IF LEN(@String) > 0 -- Record after final delimiter
INSERT INTO @Results (KeyColumn, ItemColumn) SELECT @Key, @String
I can then use it like this in the source query from to load the data warehouse:
SELECT UDF.[KeyColumn] AS [RecordKey]
,UDF.[ItemColumn] AS [ItemName]
,RFC.[DelimitedList] AS [RawItemList]
FROM dbo.RFC_DATA RFC
CROSS APPLY dbo.KeySplit (RFC.[ID], RFC.[DelimitedList], '!#!') UDF
This performs reasonably well (although as I mentioned above, I don't really have enough data to say if it performs well enough) and returns the data I need.
Next, I wanted to look at a way to perform the same "splitting by key" functionality in memory in the SSIS data flow. This was obviously a job for... the Script Component! I personally tend to shy away from using the Script Task and Script Component in my SSIS packages (largely because there is so rarely anything that I need done that cannot be done using the built-in tools) but this is a case that screams out for scripting. And SSIS makes this incredibly easy. Here's what I needed to do:
First, I updated the data flow I'd created to load the data into the staging database by adding a Multicast transformation, a Script Component transformation, a Row Count transformation and an OLE DB destination. The image below shows the finished data flow after everything has been configured and connected.
Next, I right-clicked on the Script Component and set up the inputs and outputs to include the columns I needed.
Next, I selected my output and set its SynchronousInputID property to None to mark this as an asynchronous transformation. It's important to do this before going into Visual Studio for Applications (VSA) to write any code, because Visual Studio will put in place the method stub code appropriate for the component when you first launch VSA, and it's mildly annoying to have to change it later on.
Next, I added the Delimiter package variable (which I added earlier to my package with the data type string and the value of the delimiter in the data I needed to split) to the ReadOnlyVariables list for the Script Component.
I then clicked on the Design Script button to launch VSA, and updated the stub code with the splitting logic I needed.
As you can see, this is much cleaner in VB.NET than it is in T-SQL, since the requisite functionality is already included in the .NET System.String class, so we don't need to re-invent this particular wheel.
Finally, I executed the package and made sure everything ran as desired.
I also did a little testing in the database to verify that the two techniques produced identical results, which they did. As you can see from the final image above, there just isn't enough data at this point to reach any meaningful performance conclusions, but I'm now armed and ready with two tested techniques for once the production data is available.
 If you didn't read that to yourself in a superhero voice, please go back and re-read this sentence until you get the tone right.
 If you're not sure about the differences between synchronous and asynchronous components in SSIS data flow, check out this TechNet article, or better yet, this excellent book by former SSIS Group Program Manager Donald Farmer.
Pretty cool, eh?
Now what we need is a mathematical proof that people who use keyboard shortcuts are not only more efficient but also inherently cooler than people who use the mouse. I wonder if Fitts did any work on that front...
I’m personally skeptical that you’re going to run into this situation in the real world. From my experience, unless files come from a single source, there are almost always meaningful differences (although they may be small) between them. And if these hypothetical Excel workbook files come from a single source, they’re probably going to have the same name, right?
In any event, let’s work under the assumption that we have Excel workbook files with the same data but with a different worksheet name. The steps below will demonstrate how to update the sample package from my earlier post to work in this scenario. I’m not going to write all of the VB.NET code that will be required (partly because I’m writing this in the airport while waiting for my flight and don’t have the necessary components installed to make it work, and partly because this sounds like an ideal “exercise for the reader”) but I’ll cover everything else. Here’s what you need to do:
First, we need a new XLS file to serve as input. Copy one of the existing XLS files (I’m using the last post as the starting point, so if you didn’t follow those steps then, you’ll need to do it now in order to follow along) and then open the copy in Excel. Rename the first worksheet from Sheet1 to DifferentName. Save the file and close it.
Open the SSIS project created in the previous post in Visual Studio, and open the SSIS_Excel_Loop.dtsx package.
Add a new variable named WorksheetName of type string with the value “Sheet1$”
Edit the Excel data source in the data flow to use the “Table name or view name variable” Data access mode and select the WorksheetName variable from the drop-down list.
Click on the Preview button to verify that the data source still works.
Add a Script task to the control flow inside the Foreach Loop container and connect it to the Data Flow task with a success precedence constraint.
Right-click on the newly-added Script task and select Edit from the pop-up menu. On the Script tab within the Scrip Task Editor window, add WorksheetName to the ReadWriteVariables list and FileName to the ReadOnlyVariables list.
Click on the Design Script button to open the Visual Studio for Applications (VSA) development environment.
Edit the code to look like this:
Dim sheetName As String
Dim fileName As String = CStr(Dts.Variables("FileName").Value)
If fileName.Contains("email_book_03.xls") Then
sheetName = "DifferentName$"
sheetName = "Sheet1$"
Dts.Variables("WorksheetName").Value = sheetName
Dts.TaskResult = Dts.Results.Success
Postscript: I also see that while I was in the air yesterday I got a few additional comments posted from "Romain" on looping over the tabs in a workbook to load all of them instead of just the first tab. I'm afraid I cannot read the French solution to which he linked, but the same technique not listed in the code above is what you're going to need to do that. You'll need to loop through each Worksheet object in the Workbook object for the current XLS file, using the Excel object model to do so. Probably the easiest way to do this is to have a For Loop Container in your control flow contained within the existing Foreach Loop Container that loops through the multiple Excel files. Within the For Loop have a script task that fetches the name of the next sheet following the last-fetched "current" sheet (or the first sheet in the book on the first pass through the loop) and also sets a Boolean flag indicating that the sheet name being fetched is the name of the last sheet in the workbook. Configure the For Loop to exit when this flag variable is set to true.
Tuesday, September 11, 2007
My friend Todd has been released from the oncology ward and is back home, at least for the time being. He's done with his first round of chemotherapy, and he's feeling good enough (and probably being annoying enough - he's a QA guy, remember?) that his doctors didn't see the need to keep him in the hospital. He'll have to go back for follow-up chemo treatments in a few weeks, but I'll bet it's good to be home.
Thanks to everyone everyone who responded to my earlier posts. And don't forget to mail those caps!
Monday, September 10, 2007
But for what it's worth, the cookies were very well received. ;-)
Sunday, September 9, 2007
But the in-flight movies are a favorite pastime nonetheless.
I love looking up; between chapters, between songs, before my meal, after my meal, when I need to give my eyes and my mind a break from whatever is taking up the bulk of the flight, and watching a moment of the in-flight movie. It’s surreal, watching these fragments, these soundless snippets of the story, without knowing the characters, without understanding the plot. When the flight and the movie are done, all that remains is a vague impression of the whole built from the tiny window through which I’ve peered into the larger story, with the huge gaps filled in by my imagination.
And it’s even better when flying first class on those plans new enough to have video screens in each seat back. Each person around me is watching a different movie – or better yet the same movie started at different times. It’s like a smorgasbord for the imagination.
Of course, it doesn’t help get that damned data flow built, and neither does blogging about it. Damn it.
 One of my least favorite things is turbulence. I hate that stuff.
As I mentioned in a previous post, I’m going to be presenting a session on SQL Server Integration Services at my local CNY .NET Developers Users Group. This group meets the first Wednesday of every month at the Syracuse New Horizons training center.
My presentation is going to be on December 5th – you should feel encouraged to attend if you’re going to be in the area. The presentation itself is going to cover introductory SSIS topics. Based on my informal poll of attendees at last week’s Silverlight presentation, few people have any experience with SSIS, so we’ll probably do a lightning tour of control flow, data flow and overarching concepts, with lots of opportunities for Q&A throughout the evening. If time permits we’ll drill a little into expressions, configurations and deployment, but with 120 minutes max (and that will be pushing the time limit pretty hard) there’s not a lot of detail we can cover.
But what about your users group? ;-) If you’re located in Central New York or within a convenient drive of Syracuse (Boston might fall into this category, Rochester definitely does, NYC definitely does not) and are looking for an experienced BI developer and presenter to talk to your users group, you should feel free to contact me. My schedule is a difficult beast to wrestle with these days, but fitting in an evening’s presentation is certainly possible. Drop me a line and we’ll see if our schedules are compatible…
I attended an excellent users group presentation last week on the topic of Silverlight. I’ve been vaguely interested in Silverlight since I first saw it previewed at the Mix06 conference in Las Vegas last March, but honestly haven’t devoted many cycles to tracking (or learning) it in the months since then. Since I’ve been looking into WPF lately as a data visualization tool I thought I should see more about what “the presentation tier technology formerly known as WPF/E” had to offer.
Andy Beaulieu delivered a great presentation, which included demonstrations and code samples of some very cool games he’s developed using the Silverlight 1.1 alpha bits and a decent overview of the platform in general, with as much detail as you’d expect to get out of a 90-minute session. I came away from the session with a few impressions/thoughts/questions in mind:
- What’s with the name? Usually Microsoft has the habit of having the coolest code names during product development, like Avalon or Indigo, and then replacing them with lame and boring product names closer to RTM, like WPF and WCF. But with Silverlight, Microsoft took the opposite tack – they called it WPF/E for the first year or so and then replaced the world’s lamest code name with something relatively cool – Silverlight. Who would have thought they had it in them?
- That last bullet says a ton – Microsoft is positioning Silverlight as a broad reach, cross-platform “Flash Killer” that will run on MacOS, Lunix and Unix in Firefox and Safari as well as on Windows in Internet Explorer. That on its own is damned interesting from a company that isn’t always known for playing will with others, but that’s more of a “Microsoft watching industry analyst” sort of interesting. The thing that gets my blood pumping about Silverlight 1.1 is that it relies on Microsoft (working alone or with 3rd parties like the Mono Project people) porting the .NET CLR to non-Windows platforms. Holy crap! This has been one of those things that has been an interesting possibility since the CLR came into being, since the CLR itself serves as something of an uber abstraction layer, but no one really expected it to happen. Since Silverlight 1.1 is still in Alpha it’s not yet clear how this will play out in the end, but let’s just say it has my attention now.
- Even Silverlight 1.1 is just for play, not for building “real” applications. Why do I say this? Instead of replying in my own words, let me provide a quote from Adam Nathan, senior developer type guy from Microsoft. This quote is the first sentence from Chapter Four in his excellent book Windows Presentation Foundation Unleashed: “No modern presentation framework would be complete without a standard set of controls that enables you to quickly assemble traditional user interfaces.” Note to the Silverlight team: Go talk to Adam. Silverlight lacks the basic controls that will be essential for building even the simplest business application, and based on the information I’ve seen even things as ubiquitous as a data grid are being presented as “partner opportunities” and not included in the core product from Microsoft.
- Despite this, I can still see building “islands” of Silverlight content to be included in “traditional” ASP.NET web applications, so perhaps my “just for play” comment above isn’t completely fair. Still, the thought of having to mix-and-match more and more technologies to build an application seems like a step backward. Hopefully the Silverlight team will produce some best practice documentation even if they don’t produce a complete control set.
So I guess I’ll be keeping my eye on Silverlight as 1.1 gets closer and closer to RTM. As with WPF, I don’t know if I’ll ever write any production Silverlight code (or even if I’d ever want to) but it’s caught my interest if nothing else…
 Although Syracuse is a pretty small town, we have an excellent INTEA-affiliated users group which focuses on .NET development topics. The CNY Developers users group is run by a local developer and MCT named Andy Beaulieu, and it was he who delivered this Silverlight session.
 Although I have been making an effort to introduce more SQL Server and BI topics with my presentation a few months back on SQL Server 2005 CLR integration and my upcoming presentation on SQL Server Integration Services.
 Is it kosher to have a footnote within a footnote? Would that make it a meta-footnote?
 I think this has to be ok, because I never meta-footnote I didn’t like…
 Not to give the impression that I’ve been spending a great deal of time with “real” EPF either, because I haven’t.
 One of them is a scrolling space shooter that uses the Microsoft Virtual Earth web service API to provide the background. You can enter lat/long coordinates or a street address when you begin, and battle invading aliens in the sky above your house, your school, your workplace, or as we saw last week, over the Syracuse New Horizons training center where the users group meets every month. I wish I could provide links to the games where Andy has them posted on his home page, but I’m typing this post at 35,000 feet en route to Atlanta, and I know that when I arrive in Seattle tonight at midnight I’m not going to have the energy to do much more than drive to my hotel and crash, so those links will have to wait for another day.
 Windows Presentation Foundation/Except for the stuff you really want.
 Although I’ll personally believe this when I see it – People said the same things about the Zune, that it would be an IPod killer, but all I hear from the Zune team these days is silence, instead of the steady stream of PR that I would be hearing if Microsoft really cared about owning the portable media player market. BTW, did you notice that I spelled it “Xune” in my last post? I haven’t heard anything about it in so long I didn’t even get the name right… For what it’s worth, I do think that Silverlight has a lot of promise, but Flash is entrenched and mature and such, and Silverlight has a lot of catching up to do.
 Kind of like it’s always been theoretically possible for developers to get dates, but it never really happens when you’re awake. ;-)
 This is one of the best technical books I’ve read in many months. Adam’s approach to presenting the capabilities of WPF is exactly what I would have asked for if he’d asked me: he introduces new concepts up front, then build on those concepts throughout the book, showing the reader what is new, what is interesting and how what they already know can be applied to the new tools and technologies, without wasting time explaining things that an experienced .NET developer would already know. Great work, Adam! 
 He also makes use of sidebars for out-of-band discussions and drill-downs almost as liberally as I make use of footnotes, which fits with my style of reading as well as it fits with my style of writing. Especially since he pulls it off better than I do…
I received quite a few offline comments about my "Cookies in Building 40" post, so I decided to share the cookies with anyone who is interested, whether or not they'll be able to come and grab some. Of course, if your browser doesn't support cookies you can use this blog post to bake them yourself. Follow the steps outlined below and soon you too can be fat, happy and heavily caffeinated, just like me.
Please note: If the volumes and amounts of ingredients listed in the text don't seem to match what is shown in the photos, please do not be alarmed. This is deliberate. I magnified the photos by approximately 6x so that it's easier to see what's going on.
First, let's start with an overview of what we're making: Chocolate Espresso Sandwich Cookies. The finished product will be small and soft chocolate espresso cookies with a layer of chocolate espresso ganache in between. Think of Oreos, only good.
We're going to start off making the ganache, but first, we're going to set out all of our ingredients. Mise en place is very important. Kind of like source code control.
Since we're starting with the ganache, we should to start with the chocolate that will go into the ganache. For this recipe we need five ounces of chocolate. You can use chocolate chips, but you probably should not - they're "enhanced" with food-grade wax and other additives that are great for helping them hold their shape, but not the best for flavor.
A better option is to start with a good quality (perhaps not Valrhona, where the complex nuances of the chocolate may be overwhelmed by the espresso flavor in the ganache) dark chocolate and chop it up into small or medium chunks.
In any event, take your five ounces of chocolate, chopped or chipped, and put it in your food processor.
Next, take five tablespoons of heavy cream and mix it with two teaspoons of instant espresso powder in a medium saucepan.
(In the pictures I was making two different batches of ganache, one with espresso powder and one without, the latter so I could make a "half-caf" variety of cookies for he kids at the school picnic.)
Bring the cream to a boil over medium heat - this takes longer but you don't need to worry as much about burning the cream.
Let the processor continue to run until the chocolate is melted and the ganache is smooth. Remove the ganache from the processor bowl into a small glass or metal bowl to cool.
At this point we have a bowl full of hot (boiling cream, remember) ganache cooling on the counter. Now we need to make and bake the actual cookies themselves, and by the time the cookies are baked and cooled the ganache will probably be just the right consistency for spreading.
At this point, it's time to start working on the cookies. Start by combining the dry ingredients in a medium bowl. You'll need:
1/4 cup unsweetened non-alkalized cocoa powder
1/2 tsp. baking soda
1/8 tsp. salt
This will dissolve the espresso powder and make it easier to incorporate into the cookie batter later on.
1/2 cup granulated sugar
1/2 cup dark brown sugar
Ok, let's go! Using a handheld electric mixer mix together the butter and sugars until they are combined, light and fluffy.
Mix in the vanilla mixture until it's completely incorporated and then scrape down the sides of the bowl once more.
Finally, add the dry ingredients in three batches, mixing each batch thoroughly and scraping down the sides of the bowl after each batch.
It's time to get baking. Pre-heat your oven to 350 degrees, and position two oven racks in the top third and bottom third of the oven. If you have baking/pizza stones (and you should, even if you don't bake your own pizza) you should put one on each rack.
- Nothing sticks to them
- They're easy to clean
- They're from France
- They enable you to cut your baking time in half!
Now wait, you say - how is this? How can even the culinary magic of the French cut my baking time in half? Here's how: by implementing a pre-cached and multithreaded kitchen environment.
Think about the mechanics of baking cookies? Normally it goes something like this:
- Place mounds of cookie dough on the cookie sheets
- Place the cookie sheets in the oven
- Wait, doing nothing constructive, while the cookies bake
- Remove the cookie sheets from the oven
- Wait, doing nothing constructive, while the cookies cool enough to be removed from the cookie sheets
- Remove the cookies from the cookie sheets
- Place mounds of cookie dough on the cookie sheets, and repeat
That's a lot of wasted time, and let's be completely honest: you have better things to do, don't you?
With Silpats (and you'll need four of them) this vicious cycle can be replaced with one that looks more like this:
- Place mounds of cookie dough on the Silpats on the cookie sheets
- Place the cookie sheets in the oven
- While the cookies bake, place mounds of cookie dough on the other Silpats
- Remove the cookie sheets from the oven
- Slide the Silpats with the baked cookies onto racks to cool
- Slide the now-empty cookie sheets under the Silpats with the cookie dough mounds and place them in the oven
- Remove the baked cookies from the Silpats
- Place mounds of cookie dough on the now-empty Silpats, and repeat
See the beauty? You need a little more equipment, but the incredible increase in productivity more than justifies the expense.
Wow, that was quite the tangent, wasn't it?
In any event, make small mounds of batter on the first two Silpats. Make them smaller than you think you should, because they spread out a lot while baking. A level teaspoon worth of batter is a good place to start.
Remove the cookies from the Silpats and put them on paper towels to cool completely. These cookies are too delicate to cool on racks (they sort of fall through and fall apart) but cooling on paper towels works great.
Oh! Before I forget, you can make your life easier by positioning the Silpats so that they slightly overhang the counter.
And from this point on, just repeat this process until all of the cookies are baked.
When all of the cookies are baked, match them into pairs to become individual sandwiches. Don't worry if they don't match exactly. Close enough is good enough.
Next work with each pair of cookies and spread a tablespoon or so (depending on your personal preferences) of ganache on one cookie, and then complete the sandwich by placing the other cookie on the ganache.
And when you're done, you'll have something that looks a little like this:
Friday, September 7, 2007
Anyone with an internet connection knows that Apple has just revamped its iPhone and iPod product lines.
Wait a minute! Matthew, I thought you were a Microsoft guy? I thought your blood was blue and you drank the kool-aid at every meal?
Well yeah, I love Microsoft. Their database and developer tools are second to none, and I've built my professional life (and, sort of sadly, most of my hobbies) around them with lots of enthusiasm and no regret.
But I love my iPod too. I've owned a half dozen or so different MP3/media players over the years, and and nothing even comes close. It's easy to use, works great and basically does everything that I want it to. It is so nice that I can even get over my general distaste of Apple (engendered primarily by their Mac operating system and secondarily by the holier-than-thou attitude that their cult of personality fosters in their "faithful") and enjoy using it whenever I'm away from home.
So anyway, there's a product line refresh, right? They're integrating the nice wide screen and touch-sensitive UI from the iPhone into the new iPods, right? Super duper cool, says Matthew! I've traveling a lot for work lately, and have started watching movies on my iPod on those long flights where I just can't read, write or code for one more minute. And having a portable 80 GB (like my current iPod) movie player with a nice wide screen should would be nice. I might even blow another few hundred bucks to get one, and hand my current iPod to my wife...
But no! They only have the nice movie-friendly screen on the pathetic flash-based iPods that hold a maximum of 16 GB. WTF? I dislike a lot of things about Apple, but I don't generally accuse them of screwing up when it comes to marketing and product placement. This is usually where they embarrass the competition. But not today.
And now we come to the motivation for the title of this post: what is Microsoft going to do about this? Here's my advice for the Xune team:
Strike! Attack! Pounce! For the love of all that is holy, go for their throats and give the world a better media player, with huge capacity and a big wide screen!
And by Odin's balls, don't make it brown this time.
So Apple has blown it this once, but I don't see Microsoft paying attention to the blood that's been spilled, and that's a damned shame. I bet I'm not the only one with four or five hundred dollars burning a hole in his pocket wishing he could give it to someone other than Steve Jobs. Come on guys - impress us.
 And any time I stray into the worlds of Oracle, Java and MacOS, I'm reminded just how much Microsoft kicks ass.
 Honestly, far too much, but that's another story...
 The best thing that's come out of this press frenzy around the iPod/iPhone debacle is my discovery of "The Secret Diary of Steve Jobs" - a blog written by the actual real Steve Jobs, just pretending he's someone at Forbes so he can actually speak the truth without getting turned on by his minions. Really.
Now don't you wish you'd wanted to get together Sunday night, Brian? ;-)
 I feel comfortable in using this adjective due largely to the number of times I've been remembered as "the cookie guy" and not "the BI guy."
Template Explorer is not visible by default (at least I don't think it is - I don't have a default installation of SSMS kicking around today) but you can hide it or show it by using the SSMS View menu. As you can see below, it displays a tree view of server and database objects, and within each folder in the tree is a list of templates.
When you double-click on a template (for this example I chose CREATE TABLE) you will get a new query window in SSMS that contains an example of the selected operation with
At this point you can simply edit the text of the template in the query window, but you can also use a poorly-documented feature of SSMS to automate the edits. As shown below, if you click on the SSMS Query menu, there will be a "Specify Values for Template Parameters..." menu option displayed.
When you select this menu option, you'll be presented with a dialog box that looks like this:
It's relatively simple to edit the template parameters, to end up with something that looks a little like this:
When you click OK, the text in the query window will be updated with the values you supplied, looking more like this:
The nice thing about this is that if a given parameter (in this example, think of the table name and the PK field name) appears multiple times in the template, you need to only enter its value once in the dialog box for it to be replaced consistently in all places within the template, which is pretty darned handy.
Of course, there is still room for improvement. For example:
- The template parameter dialog box is modal, so if you forget something (like the name of the database) you cannot navigate around SSMS to find it out.
- There are not nearly as many templates as you'd like to see, although to be completely fair you can also create your own custom templates.
- The templates that do exist are inconsistent in their design and quality. For example, if you use the CREATE TABLE template you get an "IF EXISTS" block so you can run the script as many times as you want. But if you use the CREATE SCHEMA template you don't get anything like this - you can either remember to only run the script once or else you need to manually add the logic you need.
Still, despite their flaws, SSMS templates are a good tool if you have occasional syntax failures like mine this morning. Enjoy!
 Or else in Word, Outlook, PowerPoint and Visio...
 In any event, I've always stumbled across it and not seen it documented anywhere, which is why when I remembered it this morning ("hey, isn't there a menu option somewhere?") I decided to blog about it. I point this out because nine times out of ten I'd rather edit code in a text editor and not in a dialog box, so there needs to be some real value here or else you're just adding work, not lessening it.
 Not that I would ever do this, of course...