SSMS 2017 RegEx

A short technical post on one thing I’ve found annoying.

Anyone who has worked with computers in the last decade has probably used regular expressions in some form or another, or “regexs” as they’re known as. First (as far as I know) popularized in Perl (though their history stretches back to the 1950s), they’ve become standard fair in most languages and tools and are very useful for complex matching and find and replaces.¬† And for anyone who has worked with computers for over two decades they often look like line noise when someone would pick up the phone in the house when you were dialed in via an actual modem.

That said, I first learned about their value in SQL Server Management Studio due to a great talk by Sean McCown. (note the post’s byline is Jen’s, but trust me, I saw Sean give the talk. ūüėČ

One of the powerful features is the ability to “tag” part of an expression so you can use it in your replace statement (see the above link for more details.)

But, here’s the thing, somewhere along the line (I think it was post SSMS 2014) Microsoft changed the rules of the game and it can be hard to find the new rules!¬† They have a post on using regexp in SSMS 2017. But as far as I can tell, it’s the old 2014 info, simply rebranded. Some of it, especially the tagging part does not appear to work for me. If anyone CAN make it work in 2017, please let me know how.

Let me give you an example. Just today I was given a script that had a lot of statements similar to:

DROP TABLE If Exists ##TempAttribute

Let me say I LOVE the new “If Exists” option for DROP Table, but, I needed to run this on SQL Server 2008R2 (I know, don’t ask!) and that syntax won’t work.

I needed to replace it with something like:

IF OBJECT_ID('##TempAttribute', 'U') IS NOT NULL
  DROP TABLE ##TempAttribute; 

Now, I’m naturally lazy and didn’t want to have to find and replace all 100 or so instances of this by hand. So, I reached for regexes… and… well it didn’t go well.

Based on the old syntax my find should look something like for the find:

DROP Table if exists {\#\#[A-z_0-9]+}

And for the replace

if object_ID('\1', 'U') is not null drop table \1;

Except, for the life of me, that wasn’t working. Every time I tried to tag the table name using the braces {} my regex would fall apart.¬† So of course I searched and got the link from Microsoft above that still suggests tagging with braces.¬† From previous experience I knew it was wrong, but that IS the official Microsoft page after all, so I kept doubting myself.

But, I was right in remembering things had changed.

The proper syntax is:

Drop table if exists (\#\#[A-z_0-9]+)

and

if object_ID('$1', 'U') is not null drop table $1;

The change to the search expression is subtle, changes braces to curved parenthesis .

The change to the replace is about the same, changing a \ to a $ but I suspect (I have not confirmed) that you’re no longer limited to just 9 tagged expressions.

And before anyone chimes in, I do realize there are some other ways of writing the search expression (such as I could have used :w+ instead in SSMS 2014 that would have worked in my particular case, since there were no temp tables with numbers, but this would not have worked in SSMS 2017), but this worked for me and wasn’t the point of this post. My goal was to focus on the change in tagging expressions.

Regular Expressions are still one of those things that don’t come very easily to me so I often struggle with the syntax and it doesn’t help when Microsoft changes the syntax rules between versions of SSMS (my understand they did so to make SSMS functionality better match Visual Studio, so I’m ok with this), but overall, I find them EXTREMELY useful and if you haven’t played with them, I highly recommend you start learning. There can be a bit of a learning curve for anything too complex, but it’s worth it.

My advice, start with learning how to “grab” the beginning and the “end” of a line and then go from there. This is the most useful thing to me at times when I want to add or remove something at the start or end of every line.

Happy expressing!

Janus 2 – 2019

“All my life’s a circle” – Harry Chapin

The New Year is now upon us. It’s now January around the world.¬† For those who don’t know where the name of the month comes from, or why my previous blog post and today’s are named as they are, it comes from the Roman God Janus.¬† Janus looked backwards and forwards. I thought it was appropriate for posts bracketing the New Year. In addition, the name of the month January is often believed to come from the name of the god, but that appears to be a false etymology.

Yesterday I looked back. Today, I’ll look forward.¬† I’m not necessarily a fan of New Year’s Resolutions (other than resolving to live one more year, which I’ve been successful at so far every time) so call these goals:

  • Continue to blog at least once a week. Last year I think I missed a week while on vacation, but otherwise I pretty much succeeded.
  • Hit 2000 page views. Last year I hit 1907.¬† I think I can exceed that this year. Of course I’ll need your help!
  • Continue speaking at SQL Saturdays. I haven’t set my schedule, but I already have 3-5 in mind. I’m not sure I’ll do 6 again, but we’ll see.
    • Expand my “SQL Server for under $200” session
    • Expand my “SQL Server Backups” (perhaps into a full precon)
    • Add one more topic to my list of sessions (see current ones here)
    • Shoot for at least one overseas engagement
  • Shoot for speaking at SQL Summit!
  • Figure out how to get an MVP!
  • Publish at least 3 more articles for Redgate’s Simple Talk
  • Continue to promote and support Women in Tech as well as other minority groups
  • Continue to learn PowerShell
  • Continue to learn about SQL Server on Linux
  • Play with containers, just a bit. This is really a minor goal given all the others I have, but I figure I should learn a little.
  • Pick up at least 1-2 more decent sized customers
  • Continue teaching cave rescue
  • Cave more!
  • Hike more!
  • Bike more!
  • Travel
  • Have fun!

That last goal is important to me. If I’m not enjoying what I’m doing, why do it? Life is too short to hate what you do with life. If you can find a way to enjoy life, do it!

Most of the goals above are SQL related, but that doesn’t mean that’s the major focus of my life. It’s just the place this blog touches upon the most these days.

I have a number of personal goals, but that’s for me and I won’t be sharing here.

In any event, I wish everyone in my biological family, #SQLFamily, Caving family, and other chosen families a wonderful and amazing New Year and hope that the new year brings you peace and happiness.

Janus 1 – 2018

As the year draws to a close, I thought I’d look back on the year a bit.

The goal of this blog has been to give me a place to reflect on the purpose of this blog.¬† I claim in My Goal Here¬†to want to reflect on how we think and what drives certain decisions. And I suppose at times that’s true. At times it’s to give actual SQL or IT related advice.¬† But at times, it’s simply an exercise in my ability to put fingers to the keyboard and words on the screen and to be a bit self-indulgent if I’m honest.

My most popular page this  year was a mixture of things: The Streisand Effect. It was a bit of an activism piece about events at my alma mater and a chance to broaden my blog to more readers. But, it did also serve to actually reach one of my primary goals; to reflect on how we think and make decisions; primarily sometimes by trying to tamp down an issue, we only serve to draw more attention to it and to inflame things further.

My second most viewed piece this year was one of several on sexism, especially in the IT industry: Math is hard, Let’s Go Shopping. I still haven’t finished the book mentioned in the post, but it’s on my list to finish. The issue of sexism in my primary industry is one that has grown in importance to me and I expect to write more about it in the coming year and to try to do more about it.

Reviewing my SQL Saturday’s in 2018, I had the honor of speaking at Colorado Springs, or at least trying to, which I wrote about here; SQL Saturday Philadelphia, SQL Saturday Atlanta, SQL Saturday Manchester UK (my first overseas SQL Saturday, where I had a blast!), SQL Saturday Albany, and finally SQL Saturday DC. I also presented at the DC SQL User Group in September.¬† All great times and I had learned a lot and had a great time meeting new people and reconnecting with old friends.

I put in to speak at SQL Pass Summit, but again didn’t make it. But I still attended and had a great time.

I also was pleased to be asked to write for Redgate’s Simple Talk where I know have two articles published on using PowerShell for SQL: My first and second. I’ll be submitting my third article in coming weeks.

But not everything I did or wrote about was SQL related or even IT related. In late June, 13 people became trapped in the Tham Luang Nang Non cave in Thailand. This became a world-wide media event that a few weeks later I found myself part of. Besides at least four blog posts of my own that touched upon it, in my role as a regional coordinator of the National Cave Rescue Commission I did close to a half-dozen media engagements, including one for The Takeaway NPR program.

Oh, one more interview I did this past year was with Carlos Chacon and Steve Stedman of SQL Data Partners: it was a podcast I did with them. You can read about my thoughts here and listen to the podcast here. And definitely go to Amazon and buy my book!

Anyway, it’s been a great, and eventful year and I appreciate everyone who has read my blog and even more so to those who have commented on it, shared it, or somehow given me feedback.

I’m looking forward to 2019. I hope you are too.

Age Impostor Syndrome

This past weekend I was at another successful SQL Saturday. It was, as always, great to see so many of my fellow speakers and friends.

I was perhaps a bit more nervous than usual for this SQL Saturday because I was giving a new technical talk and my demo wasn’t working like I wanted and I hadn’t done as many run-thrus as I like to do.¬† But it was well received and people seemed to really like it. (For those interested, it was a demo of running SQL Server for under $200, including licensing and hardware!)

During a conversation this weekend I used the expression that I might grow old, but I don’t have to grow up. But I’ve realized it’s more complicated than that.

  • In the past week I’ve completed my 51st orbit of the Sun while still breathing
  • I’m preparing to cook dinner for a bunch of college students this weekend
  • I’ve been working with two recent college graduates on a couple of projects
  • I’m consulting on a new project and using my years of experience to guide it in the right direction
  • My son is completing his first semester at college and coming home this week
  • Apparently received praise (this is second hand) for work I’ve done in a volunteer community

Physically at times I sometimes feel my age, and there certain facts that suggest I really as old as I am; but mentally, I often actually forget I’m as old as I am. I wonder, “why do folks think so highly of me, I’m just a young kid trying to figure my way out in the world.”¬† Then I realize, I’m not that young kid at his first programming job, trying to figure out how to create a make file.¬† I’m a middle-aged man who has decades of experience in my various fields of expertise.¬† People look to me, the way I look to my mentors because they expect me to have the answers! (And fortunately, they’re actually sometimes right.) Sometimes too I’ll be engaging with people my own age and they treat me as equals and I get excited that they’re treating someone half their age with such respect. Then I remember, “but wait I AM their age.”¬† Or people half my age act as if they’re looking up to me and I want to say, “but I’m no different than you” but then remember, “Oh wait, I do have that many more years of experience.”

So, there’s still a bit of me thinking I’m an impostor. I really don’t know as much as people seem to think I do.¬† Or that I’m not as old as I really am. Can one even be an age impostor?¬† Not really, I mean age is a pretty objective fact. But the truth is, I don’t feel my age, and for that I’m grateful.

I’ll continue getting older, but I simply won’t grow up any faster than I have to.

One final request from this wizened old boy, make sure to subscribe if you haven’t!¬† And speak a little louder so I can hear you.

 

 

“So, why are you sitting here?”

I had been anticipating the question and it was a fair question, after all, I was one of two men sitting at the Women in Technology Birds of a Feather table at PASS Summit.  But let me back up a bit.

Last week was the PASS Summit in Seattle, an annual event that I mentioned two weeks ago that I was headed to. There are several thousand people that attend and in order to promote networking, in the massive lunch hall, they have a number of tables set aside for particular topics, i.e. “birds of a feather”. So if there’s a particular topic or interest group you are associated with you, you can sit at such a table and know you’re among like minded friends. For example on Day One I had set at the “Virtual and Local User Group” table.¬† But today, I found myself at the Women in Technology table.

So why?

Let’s back up even further. I grew up in a small town in the northwest corner of Connecticut. I can’t say my parents were poor, but we probably lived below what many would consider a middle-class lifestyle. However, I was very fortunate to have hard-working parents and grandparents who helped, and more than a bit of privilege.¬† What do I mean by this? One example comes to mind. A couple of years after college when I was first consulting, I needed a small business loan to cover a project for a client. I literally walked into the local bank and on my word got the loan I needed. Even then I realized I had a bit of privilege going on there.

As I’ve grown older, I’ve listened to more and more testimonies from women and persons of color and continued to realize how for granted I’ve taken many aspects of my life. As a result, I’ve worked to listen to others and try to increase their access to opportunities and gain the same privilege I was simply born with by being a white male.

So why was I there?

The question was not a surprise, since the table host, Kathi Kellenberger¬†had said she wanted to go around the table and ask folks why they were there. fortunately she hadn’t started with me first! This gave me time to think about my answer.

To listen. To listen to two women of color talk about their struggles and efforts to make it into the world of being SQL DBAs. To listen to other women talk about their experiences and to learn from them.

So I gave that and a bit more as my answer and then shut up and listened. It was a great lunch and a great experience.¬† As my friend, and WIT Virtual Group co-leader (along Kathi) Rie Irish is wont to say, “if women could solve these problems we’d have done so by now. We need your help”.

So to my fellow men out there, I would say, be an ally. Attend the WIT Luncheon (which was the day before) at Pass Summit.¬† Encourage women to speak at your User Group and at SQL Saturdays, stop others from interrupting them during meetings, amplify their ideas. And sometimes, just shut up and listen. And if you’re involved with SQL Server and PASS and want more information reach out to Rie and Kathi and contact the Virtual Group the manage, Women in Technology.¬† Trust me, men are welcome as allies.

 

Family

Over the weekend on my Twitter feed¬†I saw some tweets about #SQLFamily taking the #SQLTrain up to Seattle for the PASS Conference I’m at this week. It made me reflective.¬† As some of you may know, I grew up in a train station (no trains though) and have always loved trains. And the ride from Portland to Seattle is one I’ve wanted to make because of the scenery.

But I want to write more about family. Family can mean so many things. It can be your blood family, but it can be those you choose to associate with, or that chose you.  Both have their value and place.

In my blood family, my daughter, as a tradition, has started to take me to see the latest Star Wars film when it comes out. This has led to some amazing moments, such as in 2015 when I got to see through her eyes, the excitement I felt at a similar age of “a new Star Wars movie“.

Unlike some, I loved The Last Jedi, for many reasons. Yes, it had some weak moments, but I think it was a great movie. And it makes me think about family. Something I alluded to in the post linked above. Kylo had his blood family. He had parents that loved him, an Uncle that care for him. But, he rejected all that, trying to find more.¬† Ultimately, at the end of The Last Jedi we realize, before he does, that he’s utterly alone; that he has rejected everyone in the Universe that cared for him or tried to care for him.

Rey on the other hand, learns just the opposite. Many fans were upset to learn she’s not a Skywalker or a Kenobi or anyone famous. Her parents literally are nobody. She is, in the ultimate sense of the word, an orphan, without family. Or so she thinks. At the end of TLJ, it becomes clear, she is part of a family that has chosen HER, not because of blood, but because of who she is. And she has chosen them.

I am fortunate to have many families. I have my blood family, ones that I hold near and dear. I am fortunate to have them and have such great ones.

I have my #SQLFamily, which is a diverse group of people who all share one passion: SQL Server. It’s a bit nuts at times and we’re all different, but it’s a great group of people for that chosen field. I’m not sure they’re my ride or die family, but I’ll take them!

I have certain friends I consider a family. These are my ride or die family, the ones I would drop anything for if called and asked. I’m visiting some now in Seattle while here for the PASS Summit.

While here, I’ll be visiting yet another eclectic family, my ROC Family: folks who I have shared many adventures with as members of the Rensselaer Outing Club.¬† We all share a common set of experiences and it binds us.

And finally today, election day, I think of a different family: one that I’m perhaps a distaff member of, but that is my friends and associates who are members of the LGBTQTI+ (and if I’ve left off any letters its through oversight not for lack of caring) community.¬† They’ve invited me into their homes, to their birthday parties, weddings and more. Today I think about them because for many, today is about more than tax reform, or foreign policy, it’s about in some cases, whether or not their government will support and protect them, or possibly even try to define them out of existence.¬† So I’m going to again break one of my own rules (what are rules for if not to be broken) and say, if you haven’t voted today, do so. And if you do so, think beyond simply your taxes, your religion and your other views and remember, we are all members of various families and elections can and do have consequences.

I love my families, all of them, in different ways and I hope you all are members of families that love and care. Not every family is of blood nor should it be nor does it need to be.

 

SQL Pass 2018

Next week I’m off to the SQL Pass conference in Seattle.¬† This will be my 4th¬†peregrination to Seattle in 4 years. This has become an annual trip for me. There’s one very obvious reason for going and then a 2nd also important reason.¬† SQL Pass is one of the top events for folks who work with SQL Server. It’s a 3 day conference (plus up to 2 days of pre-con events, including at least one meeting I’ll be attending as our local group leader) full of technical sessions covering a wide range of topics related to SQL Server and related technologies.

Four years ago, when I first attended, I was a newbie and wasn’t sure what to expect. My father had recently passed and I wasn’t entirely sure I still wanted to make the trip. But tickets had been bought and the price to attend been paid, so I decided to go. One of the first (perhaps the first) session I attended, was a session by Kathi Kellenberger on how to get published as an author. I had for years toyed with an idea for a book and I figured it couldn’t hurt to attend and perhaps learn something. Her session was quite helpful and I approached her afterwards for more input and she introduced me to one of the editors at Apress. I pitched my idea and a few months later, the contracts were signed.¬† All I had to do now was actually write the thing.¬† So, I ended up writing¬†IT Disaster Response: Lessons Learned in the Field. (btw, I do obviously recommend it, it covers IT disasters, plane crashes and cave rescues. It’s not your standard cut and dry boring book on disasters.)

A friend of mine who owns a book shop once said, “anyone can write a book, it’s harder actually publish a book.” I had now done both. It was a bit bittersweet because my dad had been an English major and had always wanted to write a book and be published. Now, admittedly, he wanted to write fiction, which I think is far harder, and in his day, the idea of “print on demand” like what Apress tends to do, didn’t really exist.¬† And to be honest, at the end of the day, as Kathi warned me, if I was in it for the money, I’d be better off in terms of hours spent, getting a job at McDonald’s.

But, I digress. That book ended up being my first foray into actually getting paid to write.¬† As I mentioned in an earlier blog post¬†I’ve now contributed to Red Gate’s Simple Talk program with my post on an Intro to PowerShell. And my second post has been submitted and accepted and hopefully is going up in a few weeks or so.

So, to say my first PASS event changed my live would probably be accurate.

Beyond that one session four years ago, I’ve attended many other sessions and learned a wealth of knowledge and leveraged that in my job and in finding speakers for my local SQL Server User Group which I now lead. One of my favorite speakers I had in the last year was Bob Ward who did a remote presentation for us about SQL Server on Linux. And this despite me being a Patriots fan and him being a *cough* Cowboys fan.

So again, I look forward to seeing a lot of my #sqlfamily out in Seattle next week. But I still won’t be doing karaoke, sorry Aunt Kathi!

But I also mentioned a second reason for visiting: my non-sqlfamily, what I might call my #rocfamily.¬† The Rensselaer Outing Club has a number of alumns who all live in the area and we’ve started a yearly tradition of getting together for take-out Thai food at the house I stay in. ROC in its own way changed my life, among other things, teaching me how to be a leader and an effective decision maker.

In addition to all my fellow ROCcers, there’s at least one from my days on sci.space.* on Usenet (where I can still be found btw) and a few other friends I’ve made over the years. I’m quite looking forward to seeing them all.

So see you all next week in Seattle!