Running Parameterized Queries against SQL Server using PowerShell

For many years I didn’t really think about the implications of how I was retrieving data from my SQL Servers in PowerShell. I was just happy that I was able to retrieve the data! As I learned more about SQL Server I started to think of things like SQL injection and using parameterized queries to promote plan reuse. I went back and looked at some of the old PowerShell scripts that I had written and found that I was way off! [Read More]

How many errors can the PowerShell error buffer hold?

PowerShell stores errors that it encounters in a circular error buffer variable named $Error. $Error also contains a count property to count the number of errors in the buffer. To figure out how many errors we could hold was pretty straight forward. All I had to do was create a script that generated an error and see how high $error.count got. At this point I didn’t even know if there was a maximum or not. [Read More]

Present at SQL Saturday

I have nothing but wonderful things to say about the SQL Saturday 386 leadership and the army of volunteers they brought along. It was a very well run event. The venue was nice, everything was very well organized, the people were amazingly helpful and friendly, and it made the perfect place for me to turn my presentation up a notch. It’s amazing how much of a calming effect can be felt when you are surrounded by such an inviting and amazing group of people. [Read More]

T-SQL Tuesday 68 - Just Say No to Defaults

Here is a link to the official #tsql2sday invitation from Andy Yun’s Blog. This month’s subject is “Just Say No to Defaults”. If you’ve read my blog at all you will know that I have a slight obsession with SQL Server and PowerShell. As far as I am concerned PowerShell is the new gold standard when it comes to scripting and automation in the Windows environment. Add a sprinkle of SQL Server with the SQL Server PowerShell module (SQLPS) and you have a deadly combination as a DBA. [Read More]

SQL Agent Job Wrapper Part 3 - Handing the Errors in the Wrapper Script

This is the 3rd installment in a small series of blog posts on how to create a PowerShell wrapper for running SQL Server Agent Jobs. Here are the links to the 2 previous posts and I recommend reading them because all of the posts build on the previous one. Creating a SQL Agent Job Wrapper with PowerShell and SMO - Part 1 SQL Agent Job Wrapper Part 2 – Adding Error Generation to the Cmdlet [Read More]

SQL Agent Job Wrapper Part 2 - Adding Error Generation to the Cmdlet

Last week I posted Creating a SQL Agent Job Wrapper with PowerShell and SMO. In that post I created a couple PowerShell scripts that run a SQL Agent job and wait for it to complete before exiting. That process could be called from the command line, or even from a 3rd party job scheduler if you were so inclined. I recommend checking it out before you continue, because this is a continuation of that post. [Read More]

Creating a SQL Agent Job Wrapper with PowerShell and SMO

I’ve seen this question asked a number of times over the past couple weeks and I thought I would share an approach using PowerShell and SMO. But first, let’s take a step back and try to understand why someone would want to do this. What’s wrong with SQL Agent? Well there’s really nothing wrong with SQL Agent. The main reason I have seen people asking this question is because their company is looking into using an enterprise job scheduler. [Read More]

Using PowerShell to Export SQL Data to CSV. How well does it perform?

So here we are at week 4 of the #SQLNewBlogger challenge. Earlier in the week I responded to the following post on Twitter #sqlhelp, and after I posted my response I thought that this would make a fun blog post. I have used PowerShell to export SQL Server tables to CSV files before so I know that my suggestion works, but I was wondering if I could determine how performance would be for a larger table. [Read More]

Modify SQL Agent Jobs using PowerShell and SMO

So here we are, week 2 of the #SQLNewBlogger Challenge. This is a follow on to last weeks post Monitoring SQL Agent Jobs when you work for Mr Krabs where I showed you how to go about monitoring SQL Server agent jobs using PowerShell and SMO. This can be very helpful if you are on a limited budget and can’t afford any fancy monitoring tools. This week I have decided to stick to the same subject, but I’m gonna kick it up a notch. [Read More]

Monitoring SQL Agent Jobs when you work for Mr Krabs

For those of you who don’t know who Mr. Krabs is, he is a character in the TV show Sponge Bob Square Pants. Mr. Krabs owns the Krusty Krab restaurant and is a very frugal business owner. Every decision he makes is driven completely on how it will impact his bottom line. Thinking about Mr. Krabs reminds me of one of my first SQL Server DBA jobs. I was starting just as the old DBA was leaving and there wasn’t a lot of time for turnover. [Read More]