Integrating SQL commands with PowerShell

Since this chapter mainly deals with how to run queries to get results, it is important for us to know the integration of SQL commands with PowerShell. Here is a quick example that demonstrates how to integrate SQL commands with PowerShell.

First, we will see how to create a generic function in PowerShell, which will accept a query as a parameter, execute the query, and return the result. Let's create a generic function by the Extract-Report that accepts $SQLCmd (which is assumed to be a working SQL query) as a parameter.

Assuming that the $SQLServer variable is assigned the SQL server name and $SQLDatabase is assigned the SQL instance name, we can use the following function. The function is assuming that the login that is running this PowerShell script has access to the SQL database and uses the Windows authentication method:

$global:Log = ".\Log.txt"
Function Extract-Reports([string]$SQLCmd)
{
  Set-StrictMode -Version Latest
  $authentication = "Integrated...