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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

1 Response to T-SQL Tuesday #155–Using Dynamic SQL for SQLCMD

  1. Gerard Jaryczewski says:

    This is a beautiful story to show the power of Powershell. Our experience with SQL was more creative without Powershell, but fortunately, this language is so weird for most developers, that creativity is still required.


Comments are closed.