Hash Tables in PowerShell–Advent of Code Day 3

I continue to work on solving the Advent of Code puzzles in both PowerShell and T-SQL after completing them in Python.

When I hit day 3 in PowerShell, it was a few new tricks to learn, one of which was reading a large string from a file. However the interseting thing for me was learning to work with hash tables.

The Day 3 puzzle looks at moving Santa around on a grid. We don’t know the size or shape of the grid, just that we get directions in 1 of 4 ways (^<>V) and then must move to a new house. The first puzzle asks how many houses get one present.

This is interesting, and naturally to me the first thing that occurs is a dictionary. I used one in Python, adding new elements as I received new coordinates. I wasn’t sure how to do this in PowerShell, and ended up searching a bit about two dimensional arrays, thinking I could perform a count there, adding indexes as needed. However I ran into hash tables while searching, and this was a better solution for me.

The short part of working with hash tables is that you declare them with a simple command. In my case, since I had a delivery at coordinates (0,0), I wrote this:

$houses = @{“0,0” = 1}

This uses the @{} syntax to set up a key value pair. Essentially a dictionary. From here, I computed new “keys” from the directions, and could add new values like this:

$houses.Add(“1,1”, 1)

Of course, I had to check for existing values, and if there were existing values, I had to increment them.

$houses.Set_Item(“1,1”, $houses[“1,1”] + 1)

With that code, I could easily solve the puzzle. However I was struck by the various ways I work with the hash tables. I use braces, {}, to declare the table. I use brackets, [], to access elements and then parenthesis, (), when calling methods. All of that makes programming sense, but it’s something to keep in mind, especially as those three marks mean different things in Python.

I also learned how to search with ContainsKey and ContainsValue, as well as how to Set_Item and Get_Item, which didn’t appear to work with the ISE Intellisense.

All in all, it was interesting working with a hash table and good to learn PowerShell supports them. They are very handy constructs when building up a set of data that you’ll need to work with, and you need more than the simple buckets an array provides.

Advent of Code Day 1, Puzzle B

As I continue through the Advent of Code, albeit slowly, I’m solving each puzzle 3 days. I worked through 6 of them in Python before moving on to other languages, and this is my chance to catch up with both PowerShell and T-SQL. I likely won’t post all solutions, but I was having fun rewriting code, so here are the ways I looked at things.

 

Note: You should try this on your own. I logged into the AventofCode with GitHub and things worked great for me.

 

Go on, give it a try.

 

I’ll wait.

 

Solutions coming.

 

 

Python

I started here, using iterations, which are very powerful in Python. In this case, I took advantage of the multiple variable assignment in Python to enumerate the array and get each value and index. I think use a comparison to determine if I add or subtract one. Finally, an IF returns the current index if I hit –1. I should probably have a break in there for efficiency as well.

def calculate_negative(directions):
start = 0
for i, c in enumerate(directions):
if c == '(':
start += 1
else:
start -= 1
if start == -1:
print(i)

PowerShell

This was a bit trickier for me. I wasn’t sure how to work with a string and pull out values. I did some searching and ran across the .ToCharArray function. That doesn’t feel like the best way to do this, but I decided to use it.

The rest of the function is similar and gave me the correct answer, so there you go.

$count = 0
$floor = 0
foreach ($c in $input.ToCharArray())
{
if ($c -eq '(') {$floor += 1}
elseif ($c -eq ')') {$floor -= 1 }
$count += 1
if ($floor -eq -1)
{
$count
break
}
}

T-SQL

A more complex situation here, given that we need to work through a string, calculating a running total. I broke the input up using a tally table and a splitter with substring. This was fairly easy, and not complex as I was just getting individual charaters.

Then it was a simple running total of the CTE to get me the totals at each point in time. This worked well, but I needed the first –1 total.

Finally, I used the outer query to get the min value out of the code and use that, which gave me the lowest value where the –1 occurred.

WITH tally (n)
AS
( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) a(n) -- 10
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) b(n) -- x 10 = 100
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) c(n) -- x 10 = 1000
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) d(n) -- x 10 = 10000
)
, direction (n, d)
AS
(
SELECT tally.n
, d = CASE WHEN SUBSTRING(@input, n, 1) = '(' THEN 1 ELSE -1 end
FROM tally
), currfloor
as
(SELECT
d.n
, 'currentfloor' = SUM(d.d) OVER (ORDER BY d.n ROWS UNBOUNDED PRECEDING)
FROM direction d
)
SELECT MIN(currfloor.n)
FROM currfloor
WHERE currentfloor = -1

Advent of Code Day 1, Puzzle A

I have been working my way through the Advent of Code series across the last few weeks. This is a side project, allowing me to use some Python skills to solve the various puzzles. I also started to use PowerShell to solve the puzzles, mostly becauase this means I practice new skills.

Note: I urge you to try to work on this yourself, withough reading my solutions. It’s fun and worth some practice. Once you’ve done that, feel free to read on.

Go ahead

Write your own.

Here’s the input:

DECLARE @input VARCHAR(MAX) = ‘()(((()))(()()()((((()(((())(()(()((((((()(()(((())))((()(((()))((())(()((()()()()(((())(((((((())))()()(()(()(())(((((()()()((())(((((()()))))()(())(((())(())((((((())())))(()())))()))))()())()())((()()((()()()()(()((((((((()()())((()()(((((()(((())((())(()))()((((()((((((((())()((()())(())((()))())((((()())(((((((((((()()(((((()(()))())(((()(()))())((()(()())())())(()(((())(())())()()(()(()((()))((()))))((((()(((()))))((((()(()(()())())()(((()((((())((((()(((()()(())()()()())((()((((((()((()()))()((()))()(()()((())))(((()(((()))((()((()(()))(((()()(()(()()()))))()()(((()(((())())))))((()(((())()(()(())((()())))((((())))(()(()(()())()((()())))(((()((()(())()()((()((())(()()((())(())()))()))((()(())()))())(((((((()(()()(()(())())))))))(()((((((())((((())((())())(()()))))()(())(()())()())((())(()))))(()))(()((()))()(()((((((()()()()((((((((()(()(())((()()(()()))(())()())()((())))()))()())(((()))(())()(())()))()((()((()(()()())(())()()()((())())))((()()(()()((()(())()()())(((()(()()))))(())))(()(()())()))()()))))))()))))((((((())))())))(()(())())(()())))))(()))()))))))()((()))))()))))(()(()((()())())(()()))))(((())()))())())())(((()(()()))(())()(())(())((((((()()))))((()(()))))))(()))())(((()()(()))()())()()()())))))))))))))(())(()))(()))((()(())(()())(())())(()())(())()()(()())))()()()))(())())()))())())(())((())))))))(())))(())))))()))))((())(()(((()))))(()))()((()(())))(()())(((((()))()())()()))))()))))()))())(()(()()()))()))))))((()))))))))))()((()))((()(())((())()()(()()))()(()))))()()(()))()))(((())))(())()((())(())(()())()())())))))))())))()((())))()))(()))()()))(((((((()))())(()()))(()()(()))()(()((()())()))))))(((()()()())))(())()))()())(()()))()()))))))))(())))()))()()))))))()))()())))()(())(())))))()(())()()(()()))))())((()))))()))))(()(((((()))))))))())))())()(())()()))))(())))())()()())()()())()(()))))()))()))))))))())))((()))()))()))())))()())()()())))())))(()((())()((()))())))))())()(())((())))))))))))())()())(())())())(()))(()))()))())(()(())())()())()()(()))))(()(())))))))(())))())(())))))))())()()(())())())))(())))))()))()(()())()(()))())())))))()()(()))()))))())))))))))()))))()))))))())()())()()))))()())))())))))))))))()()))))()()(((()))()()(())()))))((()))))(()))(())())))(())()))))))(()))()))))(())())))))()))(()())))))))))))))())))))))))()((()())(()())))))))((()))))(())(())))()(()())())))())())(()()()())))()))))))())))))())()()())))))))))))()()(()))))()())()))((()())(()))))()(()))))))))))()())())(((())(()))))())()))()))()))))))()))))))(()))))()))))()(())))(())))(()))())()()(()()))()))(()()))))))))()))(()))())(()()(()(()())()()))()))))))))(())))))((()()(()))())())))))()))())(()())()()))())))()(()()()()))((())())))())()(()()))()))))))))(()))(())))()))))(()(()())(()))))()())())()))()()))())))))))))))())()))))))()))))))))())))))()))))())(()())))(())()))())())))))()()(()()())(()())))()()))(((()))(()()()))))()))))()))))((())))()((((((()()))))))())))))))))))(((()))))))))))))(())())))))())(()))))))(()))((()))())))()(()((()))()))()))))))))))())()))()(()()))))())))())(())()(()))()))())(()))()))))(()()))()()(())))))()))(())(()(()()))(()()())))))(((()))))))()))))))))))))(())(()))))()())())()()((()()))())))))(()))))())))))))()()()))))))))())))()(((()()))(())))))(((())())))))((()))()(()))(()))))(()())))(()))())))))()))))(())(())))()((()))(())())))()()))()))))))))()))(()()()(()()()(()))())(())()())(((()))(())))))))))(((()())))()()))))))))()(())(()))()((((())(())(()())))()))(((())()()()))((()))(()))())())))())))(()))())()())())(()(())())()()()(())))())(())))(())))(())()))()))(()((()))))))))())(()))))))())(()()))()()))()(()(()())))()()(()((()((((((()))(())))()()()))())()))((()()(()))())((()(()(()))(()()))))()())))()))()())))))))()()((()())(())))()))(()))(())(()))())(()(())))()()))))))(((()(((()()))()(()(())())((()()))()))()))()))()(()()()(()))((()())()(())))()()))(((())()()())(())()((()()()()(()(())(()()))()(((((()())))((())))))(()()()))))(((()(())))()))((()((()(())()(()((())))((()())()(()))(((()())()()(()))(())(((()((()())()((())()())(((()()))((()((())(()))(()())(()()()))((()))(())(()((()()())((()))(())))(())(())(())))(()())))(((((()(()(((((()())((((()(()())(())(()()(((())((()(((()()(((()()((((((())))())(()((((((()(()))()))()()((()((()))))()(()()(()((()()))))))(((((()(((((())()()()(())())))))))()))((()()(())))(())(()()()())))))(()((((())))))))()()(((()(()(()(()(()())()()()(((((((((()()())()(()))((()()()()()(((((((()())()((())()))((((((()(()(()(()())(((()(((((((()(((())(((((((((())(())())()))((()(()))(((()()())(())(()(()()(((()(())()))())))(())((((((())(()()())()()(((()(((())(()(((())(((((((()(((((((((()))(())(()(()(()))))((()))()(())())())((()(()((()()))((()()((()(())(())(()((())(((())(((()()()((((((()()(())((((())()))))(())((()(()((())))(((((()(()()())())((())())))((())((()((()()((((((())(((()()(()())())(()(()))(()(()))())())()(((((((()(((()(())()()((())((()(()()((()(()()(((((((((((())((())((((((())((()((((()(()((((()(((((((())()((()))))())()((()((((()(()(((()((()())))(())())(((()(((())((((((()(((((((((()()(())))(()(((((()((((()())))((()((()((()(()()(((())((((((((((((()(((())(()(((((()))(()()(()()()()()()((())(((((((())(((((())))))())()(()()(()(()(((()()(((((())(()((()((()(((()()((()((((())()))()((((())(())))()())(((())(())(()()((()(((()()((((((((((()()(()())())(((((((((())((((()))()()((((())(()((((()(((())())(((((((((((()((((())))(())(()(((()(((()((())(((((()((()()(()(()()((((((()((((()((()(()((()(()((((((()))))()()(((((()((()(()(())()))(())(((((((()((((()())(()((()((()(()))())))(())((()))))(((((((()()()())(()))(()()((()())()((()((()()()(()(()()))(()())(())(((((()(((((((((((()((()(((()(((((((()()((((((()(((((()(()((()(((((())((((((()))((((())((()()((())(((())()(((((()()(((((()((()(()(((((((()(((((()((()((()((())(())((())(()))()()))(()()(()(()()(((((((()(((()(((())()(((((()((((((()())((((())()((()((()(()()())(()))((((()()((((((()((()(()(()((((()((()((())((((((()(()(())((((((()((((((((((()((())()))()(()(()(((((()()()))((())))()(()((((((((((((((()(((()((((()((())((()((()(((()()(()(((()((())(()()())))()(()(()(((((()()(()(()((((()(((((())()(()(()))(((((()()(((()()(())((((((((((((((())((())(((((((((((())()()()(())()(()(()(((((((((())(((()))(()()())(()((((()(())(((((()())(())((((((((())()((((()((((((())(()((()(())(((()((((()))(((((((((()()))((((()(())()()()(())(()((())((()()))()(((())(((((())((((((()()))(((((((((()((((((())))(((((((()((()(()(())))())(()(()))()(((((()())(()))()(()(())(((()))))())()())))(((((()))())()((()(()))))((()()()((((((()))()()((((((((())((()(()(((()(()((())((()())(()((((())(()(((()()()(()(()()))())())((((((((((())())((()))()((())(())(())))())()(()()(())))())(()))(((()(()()(((()(((())))()(((()(())()((((((())()))()))()((((((()(()(((((()())))()))))())()()(((()(((((())((()()(()((()((()(()(()(())))(()()()()((()(())(((()((()))((((()))())(())))())(()))()()()())()))(((()()())()((())))(())(()()()()(()())((()(()()((((())))((()((()(())((()(()((())()(()()(((()())()()())((()))((())(((()()(())))()()))(((()((())()(((((()())(())((())()())())((((((()(()(((((()))(()(‘;

Now solve it.

Your turn.

Here come my solutions, so don’t scroll down until you’re ready.

 

 

Python

I’ve been trying to learn Python, so this was a good chance to practice. Day 1 is fairy simple in Python, since I can do a .count of a string. In my case, I made a function that takes the input and does a count of a particular string.

def calculate_floor(directions):
print(str(directions.count('(') - directions.count(')')))

This function takes the input and counts the number of open parenthesis, an up, and then subtracts the count of the close parenthesis, the down. I print that, and with the input given on the site, this gives me the correct answer.

PowerShell

I have been trying to improve my PowerShell skills, and this doesn’t really help with lots of the types of things I do in PoSh, but it does help me learn a bit more about the language and practice some code.

In this case, I take a similar approach, which may not be the best. I start by converting the input to an array and then using Where-Object to look for the open parenthesis. I do the same for the close, and then subtract. I don’t know if I could do this in one statement, and didn’t research much. What I found wasn’t helpful, but I was able to solve the puzzle. Perhaps someone will tell me if I can assign two variables at once with different counts.

I did try RegEx, but coulnd’t get it to work, so I moved on. Perhaps I’ll go back to that. Input abbreviated.

$input = ‘()(((()))(()()()((((()(((())(()(()((((((()(()(((()’

$up = ($input.ToCharArray() | Where-Object {$_ -eq ‘(‘} ).Count
$down = ($input.ToCharArray() | Where-Object {$_ -eq ‘)’} ).Count

Write-Host(“Up:” + $up)
Write-Host(“Down:” + $up)
Write-Host(“Final:” + ($up-$down))

T-SQL

This was interesting to me since some of the puzzles I did in Python (I was 6 ahead when I tried new languages), required some parsing. In this case, I decided that I’d do some replacement. I thought that I wanted to count all open parens. How could I do this?

The easy way is to pull out the opens, or remove the closes. REPLACE() does this, so my up was a CTE

WITH UpFloors (UpCount)
AS
( SELECT LEN(REPLACE(@input, ‘)’, ”)) ‘UpFloors’)

Then I did the same for the closes.

WITH UpFloors (DownCount)
AS
( SELECT LEN(REPLACE(@input, ‘(‘, ”)) ‘DownFloors’)

Now I can put them together.

WITH UpFloors (UpCount)
AS
( SELECT LEN(REPLACE(@input, ‘)’, ”)) ‘UpFloors’)
,
DownFloors (DownCount)
AS
( SELECT LEN(REPLACE(@input, ‘(‘, ”)) ‘DownFloors’)
SELECT UpFloors.UpCount – DownFloors.DownCount
FROM UpFloors,  DownFloors

Running Powershell with Task Scheduler

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as#SQLNewBloggers.

One of the things I needed to do with my SQL Saturday download process was run this automatically. I don’t want to remember to do this, so I decided to set this up on a schedule.

I ran a quick search and it seemed to be a simple process. Essentially I run the Task Scheduler application and then call my script as a parameter to the PowerShell exe. The first step is to run Task Scheduler.

2015-11-05 11_43_36-Task Scheduler

This has a list of jobs, which I found some to be interesting. I disabled Adobe and a few others. Those are really annoying. I clicked "Create a basic task".

2015-11-05 11_43_47-Task Scheduler

The basic task is a wizard to walkthrough. First enter a name and description. Use something that will help you in 5 months when this breaks.

2015-11-05 11_44_07-Create Basic Task Wizard

I decided to run this daily. It’s not a big deal to me, so I’ll let it just go.

2015-11-05 11_44_16-Create Basic Task Wizard

The time doesn’t matter, and I let it run in the middle of the day. This way I might notice it running sometimes and that will motivate me to keep working on this process.

2015-11-05 11_44_22-Create Basic Task Wizard

I want to run a program, so I choose that in the next step.

2015-11-05 11_44_29-Create Basic Task Wizard

The dialog wants to know which program. In my case, I’ll have to browse to my PoSh code.

2015-11-05 11_44_36-Create Basic Task Wizard

I pick my code and it puts the path in here. However that won’t run as is. If you’ve used PoSh, you know this doesn’t work.

2015-11-05 11_44_44-Create Basic Task Wizard

What I need to do is call powershell.exe. I found this in the reference item below and what I do is add "powershell" to the front of my file, and then use the "-file" parameter. I also enclose my PoSh script in quotes.

2015-11-05 11_44_58-Create Basic Task Wizard

When I click next, I get the warning, which I accept.

2015-11-05 11_45_05-Task Scheduler

There’s a summary after this, but I say OK to that, and then run the script from the Task Scheduler. It works perfectly, so I stop there.

Now we’ll see how it works over time. And if I get the next step working.

SQLNewBlogger

This was a quick process for me. A google search, a 2 minute read, and then 2 minutes to build the task.

It took maybe 10 minutes to write this post, most of which was taking the screen shots and resizing them.

You can do this.

References

I just used one link, which was from the Scripting Guy blog.

Ending My Loop in PowerShell Early

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as#SQLNewBloggers.

I was modifying my PowerShell (PoSh) script to download SQL Saturday files recently to not re-download files. However when I did this, I also realized that I didn’t necessarily want the script to run too long.

One of the challenges of downloading the data is that I don’t know how many events exist. We don’t keep that number handy, and it changes regularly. One of the things I decided to do was run my process in a loop.

While ($i -lt 9999) {

That’s fine, but it’s not a great loop. It runs 9999 times, which isn’t what I want. It works, but it’s an unnecessary use of resources. However I don’t want to break the loop when the file file isn’t found. There have been issues generating a file, like #350, when #351 exists and is there.

I decided to use a shortcut technique I had learned as a kid. I set a variable and then incremented it when I missed a file. When the increment reaches some value, I break the loop.

I decided to use 12 as my number of missed. No good reason, but that’s what I picked. I started by putting a variable outside of the loop.

$missedXML = 0
While ($i -lt 9999) {

Then I increment this variable in the CATCH section of my error handler.

Catch 
{ 
  # if we can't load the file, assume we're done for now. 
  $missedXML++ 
  Write-Host "error with  #" $i 
}

Finally, I set up an IF loop at the bottom of the loop. If I’ve missed 12 times, I break the loop by setting the counter to the last value.

$i = $i + 1
if ($missedXML -ge 12) { 
 $i = 9999 
}

I tested this with some debugging information and what I found was that when I got to 494, I started missing files. As soon as I hit 12, the loop ended.

Enhancement complete.

SQLNewBlogger

This post came about when I started working on the script. I made the modifications from the previous post and decided to also fix the extra loops with this technique.

This post took about 10 minutes to write.

References

No resources needed here. I’ve got enough PoSh knowledge to handle this task myself.

Test if a File Exists with Powershell

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as#SQLNewBloggers.

Awhile back I wrote a PowerShell script (PoSh) to download the SQL Saturday XML files used for the Guidebook application. These contain a bunch of the information that I wanted to collect, transform, and keep around.

However the script wasn’t great. It basically looped a set number of times and re-downloaded the files. Not the more efficient solution, especially if I want this to run regularly.

One of the enhancements I wanted to make was check if the file exists, and if not, then download it. However, if it does exist, then I’ll skip the file. I know this means I don’t get updated files if schedules change, which is possible, but in that case, I can just delete the file from my desktop and go from there.

I made a quick search, and found a few links to the Test-Path cmdlet. Essentially you give this a file path and it returns true or false. Almost exactly what I need. This is what I added to my code:

if (Test-Path $DestinationFile) {

#do something

}

However I want to take action if the file doesn’t exist. In most languages, I’d add a ! in front to signify "not". However that doesn’t work in PoSh, just like > doesn’t mean greater than.

Another search showed me the -NOT operator. That’s what I need, but I can’t do this:

if (-NOT Test-Path $DestinationFile) {

#do something

}

Instead, I need to have a single expression for -NOT, which means more parenthesis. Not a big deal. I used this code:

if (-NOT (Test-Path $DestinationFile)) {

#do something

}

That worked great and now I only download the files I need. If I want to re-download (for newer events), I just delete those files and re-run the script.

SQLNewBlogger

This post came about when I started working on the script. It actually took longer to write this than find the solution, add it to code, and test it. That process was about 5 minutes.

This post took about 10 minutes to write. I also had a second post based on similar modifications to the script, so I’m did that in another 5 minutes.

References

A few of the links I used:

Boring or Scripting

Do you want to continue to perform boring, repetitive, mundane tasks as a part of your job? Many of you might not be challenged at work, or you might be burdened with a series of requests that repeat themselves over and over. They’re easy tasks, many of them probably take minutes. I’m sure there’s also a level of mindlessness that you find comforting at times with just working through a familiar task.

However many of you also get busy. You have no shortage of new tasks that get assigned to you on a regular basis. You probably also get stressed from your heavy workload at times. What do you do when you’re too busy to work through the mundane tasks, but they still need to be done? It can be a challenge to manage that burst in a workload if you haven’t prepared for it.

There is a way to remove some of the mundane administrative work from your job. It’s not simple, and it’s not going to solve all your issues right away, but over time, you can certainly reduce the burden of working on dull tasks over and over again, across multiple machines.

Learning PowerShell (PoSh) or some other scripting language. VBScript works fine, as does Perl, and there are others, but if you’re a Microsoft person, especially a SQL Server person, learn PoSh. It’s used in all the products, it’s becoming a standard for all Microsoft products, it works in the cloud, and it works with SQL Server. It takes some getting used to, and it certainly can help with repetitive tasks. It can also run all your SQL scripts for you, just in a more automated fashion.

You can grow your career, add a new skill, reduce your workload, and become more efficient at your job.

Or continue to be boring.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.3MB) podcast or subscribe to the feed at iTunes and LibSyn.

 

The Demo Setup–Attaching Databases with Powershell

I found another use for Powershell, one actually suggested by someone else: attaching specific SQL Server databases.

TL;DR I have a script that detaches all user databases from a SQL Server instance and then reattches certain ones. Full script at the end.

The Issue

We have a lot of demo databases on our demo VMs for Red Gate. Some specific databases are used to show things with different products, but it ends up with us having a few dozen databases on an instance of SQL Server.

That’s not the best way to show things to users, as they can get confused with so many databases. Specifically for us, we have a set of databases for one of our classes, a different set for a second class, and a third set for a third class. We do this because things need to be set in different stages for each class.

One of our sales engineers said it would be great if we could hide some databases when we didn’t need them. I immediately saw a use for Powershell here.

Approach

My approach to this problem would be this.

  • detach all user databases
  • attach specific databases by specifying the name of the database, and the mdf/ldf/ndf file names.
  • use a batch file the user can double click on the desktop to run the Powershell script.

This seemed to make sense, and I started to tackle this on one of my machines in this manner. However because I detached all my databases first, all of a sudden working on things was a pain. As a result, I setup a new VM and created dummy databases there. I first worked on the attach piece, and then the detach part.

Detaching User Databases

This was fairly simple, and I’ve written about it before. In this case, I merely cut and pasted this code into my script.

$srv = New-Object ‘Microsoft.SqlServer.Management.SMO.Server’ $instance

#detach all user databases
$dbnames = $srv.Databases.name

  foreach ($dbn in $dbnames) {
    Write-Host $dbn
    if ($dbn -ne "master" -and $dbn -ne "model" -and $dbn -ne "msdb" -and $dbn -ne "tempdb") {
      $srv.DetachDatabase($dbn, $false)
   
      }
    }

The first line is actually needed for both parts of the script, and we re-use that object later.

The script gets a handle to the databases object and then a collection of all the names. We loop through the collection and if we aren’t looking at one of the four system databases, we call the detachDatabase method.

Note that this means I’m in control of the instance and I know I don’t have a distribution database or anything else that might break. For me, I can safely drop everything other than master/model/msdb/tempdb.

Attaching Databases

I had to search around for some example code. I guess I didn’t have to, but the docs from MS can be tricky to put together, so I searched and found a few examples. Specifically, I ran across this post that described how to attach a single database.

I decided to begin by building up the db name and paths to the files. I started by setting a variable to the path and database name.

$sqldatapath = "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\"

$dbn = "sandbox"

One of my databases is “Sandbox” and the path for all my database files is given as the default.

Next I build up the mdf/ldf files. In my case, I don’t have anything other than single mdf file databases.

$mdffiles = $sqldatapath + $dbn + ".mdf"
write-host $mdffiles
$ldffiles = $sqldatapath + $dbn + "_Log.ldf"
write-host $ldffiles

With these, I now can tell what I’m doing. I write the data out to the host, mostly so that if something breaks, the user can determine where. We’re all technical, but it’s nice to know what’s broken.

These are the important bits, but now I need a place to store them. At only one time in the script, I create a new StringCollection object.

$dbfiles = New-Object System.Collections.Specialized.StringCollection

I’ll reuse this object for each database. In this object, I store the database file names. I use the .Add method to get them in here.

$dbfiles.Add($mdffiles)
$dbfiles.Add($ldffiles)

Now I have all my parameters. I can call the AttachDatabase method.

$srv.AttachDatabase($dbn, $dbfiles, "sa", "None")

The documentation says I need an owner, and for simplicity, I use “sa”. I also can specify options, but I don’t care in this case.

This attaches my first database. However, I need to repeat this. I could build some loop and use some array, which is probably better, but for the sake of simplicity here, and preventing issues, I copy and paste this code multiple times. In my case, I have no more than 4 databases, for any environment, so I merely copy/paste this code and change the database name.

However, I don’t want to keep adding to my StringCollection each time. In between each set of databases I need to call, I add this:

$dbfiles.Clear()

Now I have a few simple scripts I can modify easily, and others can understand them.

The Batch File

The other thing I learned with the batch file is that it doesn’t have the same context as my editing session. I had to add a line to load the SQLPS stuff at the beginning for it to work.

Import-Module "sqlps" -DisableNameChecking

I also had to ensure the execution policy is set on each machine, but we tend to do that when we set up the machines.

Simplicity

This is the simple way. It’s really not the best way, and if these scripts change much, this is a problematic way of doing things. I really should have a loop with a list of databases in one place in the script. That way if I add or remove a database, I can easily do it.

That’s an improvement I’ll make.

Let me also say that I have a pattern of database names, and files. If I needed to handle different file locations and varying numbers of files, I think this approach actually works better. Each section of the script can be edited easily, and separately, without worrying about complex logic.

I like simple.

Scripts

The batch script is this.

powershell c:\Utilities\attach_demodbs.ps1

I call the Powershell host and give a fully qualified path to the script.

Here is one of my demo scripts, for two databases: sandbox and EncryptionPrimer:

<#

Attach Demo Databases

This script detaches all user databases and then attaches the following databases

Attaches
– Sandbox
– EncryptionPrimer

#>

Import-Module "sqlps" -DisableNameChecking

$srv = New-Object ‘Microsoft.SqlServer.Management.SMO.Server’ $instance

#detach all user databases
$dbnames = $srv.Databases.name

  foreach ($dbn in $dbnames) {
    Write-Host $dbn
    if ($dbn -ne "master" -and $dbn -ne "model" -and $dbn -ne "msdb" -and $dbn -ne "tempdb") {
      $srv.DetachDatabase($dbn, $false)
   
      }
    }

$dbfiles = New-Object System.Collections.Specialized.StringCollection

$sqldatapath = "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\"

$dbn = "sandbox"

write-host "Instance: " $srv.Name
write-host "Attach " $dbn

$mdffiles = $sqldatapath + $dbn + ".mdf"
write-host $mdffiles
$ldffiles = $sqldatapath + $dbn + "_Log.ldf"
write-host $ldffiles

$dbfiles.Add($mdffiles)
$dbfiles.Add($ldffiles)

$srv.AttachDatabase($dbn, $dbfiles, "sa", "None")

$dbfiles.Clear()

#attach staging
$dbn = "EncryptionPrimer"

write-host "Instance: " $srv.Name
write-host "Attach " $dbn

$mdffiles = $sqldatapath + $dbn + ".mdf"
write-host "MDF: " $mdffiles
$ldffiles = $sqldatapath + $dbn + "_Log.ldf"
write-host "LDF: " $ldffiles

$dbfiles.Add($mdffiles)
$dbfiles.Add($ldffiles)

$srv.AttachDatabase($dbn, $dbfiles, "sa", "None")

$dbfiles.Clear()

Parsing SQL Saturday Data – Getting Titles from the XML document

I’m continuing on with my project to grab SQL Saturday data and automatically insert it into a SQL Server database. In this piece, I’m picking up from the last one where I had a loop to load all XML documents in a folder based on a pattern.

This time I want to query the XML and get out specific elements and capture them.

The Source

The XML source looks like this for the sessions:

</event>
<event>
  <importID>2102</importID>
  <speakers>
    <speaker>
      <id>2102</id>
      <name>Jason Strate</name>
    </speaker>
  </speakers>
  <track>Track 3</track>
  <location>
    <name>2520C (Conference room)</name>
  </location>
  <title>Using XML to Query Execution Plans </title>
  <description>SQL Server stores its execution plans as XML in dynamic management views. The execution plans are a gold mine of information. From the whether or not the execution plan will rely on parallelism to what columns are requiring a key lookup after a non-clustered index seek. Through a the use of XML this information can be available at your fingertips to help determine the value and impact of an index and guide you in improving the performance of your SQL Server databases. In this session we’ll look at how you can begin to understand and query the structure of the execution plans in the procedure cache. Also, we’ll review how to uncover some potential performance issues that may be lurking in your SQL Server.</description>
  <startTime>9/18/2010 12:15:00 PM</startTime>
  <endTime>9/18/2010 1:30:00 PM</endTime>
</event>
<event>
  <importID>2109</importID>
  <speakers>
    <speaker>
      <id>2109</id>
      <name>Jason Strate</name>
    </speaker>
  </speakers>
  <track>Track 4</track>
  <location>
    <name>2520D (Seminar room)</name>

I’m showing the end of one element, one whole one, and the start of a third. There is a lot of extraneous information in the document that I don’t want (for now). As a result, it’s not as simple to query this as I’d thought before. Especially as I’ll want to capture each session title and insert it into a database.

I decided to use a SelectNodes to get to the <event> nodes and then loop through them. The code looks like this:

# get the event node
$sessions = $doc.SelectNodes("//event")

# loop through the various //event nodes
foreach ($session in $sessions) {

Note that this is inside of the code from the previous post.

Inside of this loop, I decided to create another loop. Initially I didn’t, but that made it more difficult to determine the end of the event node and capture the values, especially the speakers. As a result, I have a sub loop at well:

# probably a better way, but I wanted to loop through the various elements and only pick out certain ones
foreach ($detail in $session.ChildNodes) {

If anyone has a better way, let me know. I’ll have all the code below, but this technique allows me to look for specific nodes. I know I could query for them, but since I’m looking for a few specific items, I thought I’d do this rather than multiple queries later.

Get the Title

I actually need the title and the speaker child node, but I’m doing titles only here. Here’s the whole node loop code:

foreach ($detail in $session.ChildNodes) {

  # If we’re on the title node, get the value
  if ($detail.Name -eq "title") {
    $title = $detail.’#text’
   }

  if ($detail.Name -eq "speakers") {
    #placeholder
   }
#end foreach for $detail
}

Here if I have the title element in the foreach loop, I capture it. This allows me to use this variable later. I’ll go into the speaker code later, but for now, I left a placeholder.

That’s really it. At the end of the outer foreach, I write out the $event and $title variables. This gives me a nice output to the screen. From here I can easily substitute some ADO code to send this to SQL Server instead of the write-host, but that’s a good programming technique for me to see if I’ve got the data I want.

sqlsatloop_d

As you can see, there are sessions that I don’t want, but there’s nothing in the data for me to tag them as non-educational sessions. I’m not sure I care, since the speakers associated with these won’t impact my results for reports, so I’ll leave them.

Next Steps

From here I need to extract the speakers before I insert data into SQL Server. That will be the next step before I create the database and then insert data.

The Code

Here’s the entire code:

#ViewXML_Basic
# View XML file data from a website

$debug = 0;
# counter for events
$i = 1

#when do we stop?
$loopend = 400
$baseURL = "E:\SQLSatData\SQLSat"
$loop = 1
$doc = New-Object System.Xml.XmlDocument

do {
#start large loop

  # get the filename
  $sourceURL = $baseURL + $i + ".xml"

  # debug information
  if ($debug -eq 2) {
    Write-Host $sourceURL
    }

  #test the path first. If it exists, load the XML
  if (Test-Path $sourceURL) {
    $doc.Load($sourceURL)

    #trap the event number. This will be the ID I use in the database table.
    $event = "SQL Saturday #" + $i

    # get the event node
    $sessions = $doc.SelectNodes("//event")

    # loop through the various //event nodes
    foreach ($session in $sessions) {
 
    # probably a better way, but I wanted to loop through the various elements and only pick out certain ones
    foreach ($detail in $session.ChildNodes) {

      # If we’re on the title node, get the value
      if ($detail.Name -eq "title") {
        $title = $detail.’#text’
       }

      if ($detail.Name -eq "speakers") {
        #placeholder
       }
     #end foreach for $detail
     }

    write-host $event ": " $title
   
    # placeholder – insert into table here. $i, $title

    $title = ""
    $speakers = ""

   #end foreach for $sessions   
   }

   # end test path
   }
  # increment loop
  $i++

#end outer loop
} while ($i -lt $loopend)

write-host "end"

Powershell Quick Parameters for Scripts

I was working on a script recently to manage a particular process and wanted to make it generic by allowing the user to pass in a parameter. I have seen lots of examples, especially those that work with SQL Servers, using text files and other items as parameters, but in this case I wanted an easy, quick, command like parameter.

This post looks at what I chose to check parameters. I had a couple requirements.

  • display message if no parameter is passed in.
  • display some help if /? is passed in.

I know that my cmdlets should contain help from the PoSh command line, and I’ll get to that. For now, I’m managing things the way I was taught when I wrote C. A /? should get me help.

$Args

I did a little research on parameters and found a few things, but decided to use the $args variable. This is an array of undeclared parameters. I grab the first value (the only one I care about like this.

$instance = $args[0]

Note the [0]. As with many things in Computer Science, we’re zero based arrays.

I could allow for other parameters, but this gets me what I want.

Testing

The test for /? is easy. That’s like this:

if ($instance -eq "/?") {
  write-host "Please enter the instance you wish to detach all databases from as a parameter."
  }

If this is equal to my help request, write something out.

Next I needed to add another test. In this case I found that I could easily look for NULL variables, or blanks, with the !. As in this:

if (!$instance -or $instance -eq "/?") {
  write-host "Please enter the instance you wish to detach all databases from."
  }

That worked well and lets me remind myself if I’ve forgotten to pass in a parameter. The one thing I experimented a few times with was the OR clause. I tried these, none of which worked:

  • if (!$instance OR $instance -eq "/?") {
  • if (!$instance) or ($instance -eq "/?") {
    if (!$instance) -or ($instance -eq "/?") {

A little experimenting got me to remember that PoSh is fairly consistent, and the plain -or should work inside the parenthesis.

Everything Else

When I first ran this without a parameter, my script froze. That’s because I hit the IF clause, wrote out the message, and then executed.

Fortunately I’ve done this type of stupid programming before, so I added this:

if (!$instance -or $instance -eq "/?") {
  write-host "Please enter the instance you wish to detach all databases from."
  }
else {

The rest of my script fits in the else clause.

Reference