Author Archives: dbaduck

SQL Server Agent PowerShell Step Tips

Set-Location C:

Over the years of using PowerShell with SQL Server I have learned a few things of how to use the PowerShell step type in a SQL Agent job.  When running a job step of PowerShell there are a few things you should know.  There is a post by SQLHammer that illustrates one of them here. The one that shows that if you are using a PowerShell job step, you will want to use a Set-Location C: at the top of the script, especially if you are going to do anything in the FILESYSTEM as the SQL Server Provider does not like the interaction.

Error Handling in the Step

In the job step you will see the dialog below:


What you don’t see is the way you have the job step succeed or fail. When using most commands in modules, you may find that errors still cause the step to fail because of the way they report the failure (some kind of throw or a Stop condition outside your control). So if you want things to fail that normally would show red on the screen but things would continue, remember that the default ErrorAction is Continue, so even though you get an error, PowerShell will just continue.

The tip is that when you want to have something like a dir c:\dbaduck fail even though it is not a critical error and if you did run this in a job step, by default it would show success in the job step even if the command failed because the directory does not exist.  So on those statements that you would like to cause the job to fail and stop you need to use the –ErrorAction Stop on your command.

dir c:\dbaduck –ErrorAction Stop

The above command would stop the job if dbaduck was not a directory in the file system. So remember that failing the job is important when wanting to govern the outcome or the actions after the step.

Why become a PowerShell DBA

If you find this article valuable and you decide that you want to get your learn on with PowerShell and SQL Server, there is a SQLSkills class that I am teaching this year, please join me to get a great start in how to get your toolset growing by adding PowerShell to your toolbelt. EarlyContinue Reading

T-SQL Tuesday #77- SMO is my favorite feature

This is a post for TSQL Tuesday #77 about my favorite SQL Server Feature.  I write a lot of automations using PowerShell and I use SMO (Shared Management Objects) all the time. This is my favorite feature because it allows me to get information from SQL Server using an object model instead of using TSQL. Continue Reading

Using SQL Authentication with SQLPS Module

In PowerShell you know that you can import the module SQLPS when the tools are installed for use on the machine. When you import the module you are dumped to a folder of SQLSERVER:\ > and it automatically uses SSPI or Windows Authentication. Many users would like to be able to use SQL Authentication toContinue Reading

TSQL Tuesday – Monitoring

There are many forms of monitoring. When I thought of monitoring, I used to always refer to things like Red-Gate, SQL Sentry, Idera or Confio Ignite (Solarwinds DPA).  But recently I have began to dive more into monitoring from the perspective of proactive checking things out. I will share a PowerShell script that I useContinue Reading

Skip to toolbar