Powershell: Getting familiar with SQL Querying (Take Two)

Edit: I was lucky enough to have a fellow redditor point out that I had made a mistake after my initial post. (Thanks /u/Lee_Dailey!). There were two primary mistakes I made with the first version of this script.

1. Can't query a database without first building a connection object. (Yeah that might be helpful!)
2. My syntax used for building my query string was incorrect.

That said, here's an updated post :).

Before I begin, I recently realized that I need to start being more organized at work and with my posts. The reason is because I'm finding that it is becoming increasingly difficult to choose a topic for a post and that I am very disorganized. To address this, I've created an "itinerary" for my next three posts. Once I've made it through these three posts, I'm going to re-evaluate and see if any tweaks are needed.

Additionally, I'm going to be categorizing my posts so any potential readers can tell whether or not it's relevant before reading. The first of the three posts, which will also be posted today, is going to discuss various concepts of SQL querying using Powershell.

That said, here's what you can expect for the next three posts. 

1. Powershell: Fun with SQL

2. Powershell: Working in the Pipeline

3. Git: Configuring Powershell, Managing Repos

Fun with SQL

In recent weeks, I've been working on a PS function to query several tables located on a few different databases with the intention of validating whether or not a given employee's account and access levels are configured correctly. When I started this project, I had very little understanding of how queries worked and only knew the basics of the syntax. In addition to my lack of familiarity, I had to figure out how to work with queries within the Powershell environment. I spent a few days doing only research so that I could get myself acquainted and started getting to work.

To begin, I started working in the Microsoft SQL server studio. My goal was to create a few queries in order to get familiar with the syntax and then run a few queries to understand how the control flow works. I started with a very basic query and worked my way up to a more difficult one similar to what I would need for my project.

Query 1: Check employee_id column on emp_info table on srvr\instance for employee_id 1234

SELECT

employee_id

FROM

emp_info..sql\srvr

WHERE

employee_id = '1234'

Query 2: Do the same as query 1 accept make sure it’s the employee’s info from office_id 9999

SELECT

employee_id,office_id

FROM

emp_info..sql\srvr

WHERE

employee_id = '1234'

AND

office_id= '9999'

Query 3: Query a bunch of columns on a couple tables and return all of the information for employee_id on both tables.

SELECT a.employee_id,b.employee_id,office_id,department_id,job_type FROM

emp_info..sql\srvr a,org_info..sql\srvr b

WHERE

a.employee_id = '1234'

AND

b.employee_id = a.employee_id

The first query is pretty self explanatory. I want to check the employee_id column located on database/srvr for employee id 1234. This would end up returning us his basic info like first name, last name, job type. Next, I wanted to be sure that the information I was receiving back was from the correct office. Finally, I needed organizational data in addition to employee information from two different tables. To perform this query, we need to join to separate queries info one, which is done using character (dot) identifiers a.employee_id and b.employee_id.

This reason for this is that both tables have a column named employee_id and we want to ensure that we have the desired information for the specified employee_id returned from both tables. Now that I had a better understanding of querying with SQL, it was time to move on to the shell. 

Note: Rather than using Invoke-SqlCmd, I created my own function to invoke the query on the database. In order to do so, a connection object must be created.

Since my goal for this script is to eventually be automated, I decided it would be easiest to store my query in to an object. to have a function build my query object so if I needed to add functionality to get more than one employee, I would just need to add some looping control flow. Here is an example of thewritten above.

As you can see from the above, I first initialized a function for calling query named Invoke-QueryFunction that accepts the parameter $employeeID. Next, the requested employee ID is fed through the parameter and used to create the query. You may have noticed that I’ve also initialized a variable to accept a string composed of both employee_id columns that I will be selecting from. This was only done to shorten up my query commands. Finally, the function constructs the query, runs it, and stores the return values in the $queryReturn object. Now that we have a query function, we can add a couple more objects and some control flow to run this against a CSV of employee IDs.

There you have it folks. A fully functioning script to iterate through a list of employees and confirm if they are valid or not. On a side note, make sure to be confirming which type of return data you will be receiving because Powershell does not play nice with invalid datatype declarations. Otherwise, thanks again to all of you readers out there. Feel free to let me know if you have any questions, comments, or suggestions.

Pastebin Link: http://pastebin.com/Cxx1SWUw

Here's the Pastebin link for the botched script:  http://pastebin.com/q8reusHn