Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #155–Using Dynamic SQL for SQLCMD

    It’s that time of month, and I’m the host this month. I wrote the invitation last week and now its’ time to answer. I’m actually using an example from the past that I was reminded of. That was the basis for the invitation as well.

    SQL Slammer

    I don’t know how many of you remember SQL Slammer, but it hit my company, JD Edwards, over a weekend. I was away on holiday, coming home Sunday night and got called into the office that night.

    One of the challenges with JD Edwards was that we used MSDE extensively. It was a part of some of our products, and developers had it on various machines, lots of multi-instances, and in all sorts of development servers. The worm crippled our network.

    We also had non-standard installs, so when we got a patch from Microsoft, it didn’t work because we weren’t in the c:\Program Files\… that they expected.

    I had to some some fancy dynamic stuff to get the patch to work. First, we used some queries in SMS (Systems Management Server) to find all the places where we had MSDE and SQL Server services. This wasn’t too hard, and I had a list of hosts and instances from here. Now the hard part.

    I needed to query all these instances and find out where things were installed, as well as get some patch information back. The SQL itself wasn’t too hard, but connecting to and querying all these machines wasn’t simple. This was the pre-PowerShell  era. and VBScript wasn’t as easy, or bulletproof, to write.

    Excel to the Rescue

    I’d used Excel to help with this type of thing in the past. I would  put in some data in a column in this case, the hosts and instances. Then I’d add the same value in other columns, like SQLCMD. Then I would concat these together to make a string I could run. I also included T-SQL code in here, as I’d be querying various tables inside the engine.

    I also had an output part of the command, so that when I copied the contents of my final column, I had hundreds of SQLCMD command calls that would query all our instances and return data in a way that we could use to run the patch.

    Double dynamic code!

    I put these in a batch file, ran them, and then we had results that could be used in a similar process with the MS patch to patch all machines.

    A long 2-3 day of getting systems patched and slowly turning our network back on.

  • T-SQL Tuesday #155 –The Dynamic Code Invitation

    tsqltuesdayApologies for the late invitation. A minor snafu has me hosting again.

    This is the monthly blog party where someone hosts and you all write a response. I’d like to think this is one where lots of you have a story or a situation that worked out. Write a post on 11 Oct and post a comment here.

    The Invitation

    I saw a post recently where someone noted they used Excel to help build dynamic SQL for their job. I thought that was a) creative, and b) similar to something I’ve done. In fact, that will be my post for this month.

    However, while many of the experts decry dynamic SQL as a poor way of solving problems, it is not going away. In fact, it works really well for many situations and problems, albeit not necessarily a high volumes of data. There also are security concerns.

    My invitation this month is to write about producing SQL dynamically in some way. Let us know about any of these things:

    • a problem you solved
    • a creative use of technology to build SQL
    • security concerns
    • a place where dynamic SQL failed you
    • a way to convert dynamic SQL to something cleaner
    • anything else that relates to code producing code

    The Rules

    Only a few rules.

    • publish on 11 Oct 2022 sometime.
    • use the logo above and link back to this post
    • leave a comment or trackback/pingback here
    • Use the #tsql2sday hashtag to tag your post on Twitter, LinkedIn, etc.
    • Encourage others to blog

    That’s it. Have fun and I look forward to reading your responses.

    If you want to host, ping me on twitter (@way0utwest) or LinkedIn or email or anything really.

  • T-SQL Tuesday #154–Thinking about SQL Server 2022

    This month is an interesting T-SQL Tuesday party, as Glenn Berry hosts and asks us to think about the upcoming new release of SQL Server. Sometime later this year, I expect SQL Server 2022 to be released and Glenn is asking us to talk about our experiences.

    If you haven’t looked at this new version, which is in RC0, you might take a few minutes to play with it and see if the language (or other) changes might be useful in your organization.

    If you want to host a T-SQL Tuesday yourself, ping me on Twitter.

    My Work with SQL Server 2022

    I first saw some demos of SQL Server 2022 in 2021, at various conferences. Microsoft showed off some new capabilities, some of which were very interesting. I was lucky as an MVP to get some access to private, pre-CTP builds and experiment a bit with new features.

    This spring Microsoft publicly released CTP 2.0, then 2.1, and now RC0. I’ve upgraded to 2.1 on my desktop, and was waiting the docker container to update before moving to RC0. just after Glenn’s invitation, I saw the container was up to date, so I pulled a new one.

    I’ve run some of my old demos on the platform, just to check that they work. That’s really work to see if there are any regression bugs. I have rarely found this to be the case, but it has been interesting to do this with 2017, with the linux version, and more. I’ve only been lightly interested in this release, as a few of the changes aren’t applicable for the work I do with Redgate. Others might be interesting to the community, and I need to spend more time on them.

    Really, I got mostly interested in the T-SQL language changes. I had been using Window functions and the OVER() clause quite a bit and find writing them cumbersome, but I was excited to see the SELECT..WINDOW clause. I’d also liked the STRING_SPLIT enhancements with an ordinal.

    There was an article sent to SQL Server Central that got me to look at more of the features, and I think that while most of these aren’t changes I’ve been needing, they do improve and round out the language. I look forward to experimenting with them a bit.

    I’ve reproduced a few performance demos, looking at the query optimization features, and those seem good, but for me and many other developers, these will just be things that should work. Not something to get too excited about. Unless you’re on call, then you might really want to upgrade and hope these fix some of your problems without creating other ones. Maybe the thing I’m most excited about is the granular UNMASK permission, which has been overdue for a couple of versions.

    I don’t quite know what to think of this new version. While there are more changes than I expected, it feels like a lot of small changes and not much of a fundamental shift in the product. I suppose it’s a major release, but kind of like SQL Server 2014, this feels more evolutionary than revolutionary.

  • The Conference of the Year–T-SQL Tuesday #153

    tsqltuesdayIt’s the second Tuesday of the month and it’s time for T-SQL Tuesday. This one is hosted at my request by a good friend, Kevin Kline. Kevin has been a large part of the #sqlfamily as a speaker, teacher, educator, friend, and driving force for PASS and the annual Summit conference.

    This invitation is asking about a conference or event that changed your life, that created an opportunity, or just changed your life. Career, other otherwise. It’s a great topic, and it’s one reason that I am running SQL Saturday. Events and interactions with others can change your life. It did for me, and for many others.

    1999

    This year always stands out as a reminder of Prince (RIP). However, it was also the first year I lived in Denver. I moved out with my wife and two kids to work at a financial services firm. That summer I saw an advertisement for a new professional group, the Professional Association for SQL Server. They were having a conference that October in Chicago. I asked my boss to go, they agreed, and I made arrangements.

    Two things stand out to me. First, my wife, sister-in-law, and toddler son came. We went to the last baseball game of the year at Comisky Park. My wife and SIL  also smoked cigars with me in the hotel bar. A memorable fun trip for me.

    The second thing that stands out is that I met Kalen Delaney there. I’d read her Inside SQL Server book and watched her talk. Afterwards I went up to the stage and asked a question and shook her hand. An exciting moment for a young data professional at the time, and one that spurred greater interest in learning more about SQL Server and becoming a better DBA. I’ve also been honored to know Kalen and meet her at many events around the world since.

    That event kick started me from being just a DBA to being someone that wanted to be involved with the community, that believed I could speak in front of people and teach them things, and I could help my career by attending other events.

    Conferences and Career

    Most years I attend 10-20 conferences and speak at them. I may attend a few others and not speak, and I usually have a few internal or Redgate run events. I’ve very lucky, and I enjoy getting to visit interesting places in the world as a part of my job.

    While I pick up technical bits and might get an idea for how to solve a problem in code, the most valuable parts of conferences are talking with other people and networking. Yes, networking, which is really just shaking a hand and answering a question. Or shaking a hand and asking a question. Or even commiserating with someone next to me about how some technology doesn’t work. Sometimes those last conversations are the most memorable and enjoyable.

    This networking is incredible and I’m amazed how I hear about different opportunities. Even before I ran SQL Server Central, talking to people at events drove my career forward.

    Whether you go to a user group meeting, a local SQL Saturday, or a larger paid conference like the PASS Data Community Summit or SQL Bits, if you make an effort to chat with people, interact, and take notes, I think you’ll find it as invaluable to your technology career as I have.

    Getting Involved

    The last thing I’d note is that I run SQL Saturday as an independent, US charitable 501.c.3 corporation, independent of Redgate. I’ve found user groups to be great, but hard to manage every month. When Brian, Andy, and I set up SQL Saturday, we did so to bring local conferences to people whose employers might not pay to send them to Ignite, the PASS Summit, or other expensive conferences.

    I would love to see more SQL Saturdays in more places in the US. It’s not that hard to run one, and you don’t even have to speak in front of a crowd. If you’re interested, ping me at admin@sqlsaturday.com.