For many reasons you may want to retrieve data from SQL to be used in PowerShell. Maybe it’s data from a service’s database or data that was stored in SQL from another script. I wrote this function to quickly get a SQL table into a PowerShell DataTable object. It’s provides parameters for setting the target server, database, table, and where statement. Timeout defaults to 60 seconds for the SQL client connection but can be overridden. Security defaults to SSPI, but can be set, and I didn’t add any credentials arguments as the intent is that the script is run as a user with the appropriate permissions to select data from the database. In the case of Microsoft SQL the dbreader role at a minimum is required assigned to your Windows credentials.

In the function the SqlClient.SqlCommand.CommandText is “Select $Filter FROM $Target $Where” #Filter defaults to * , Target is mandatory for the table name, and where is blank so if used add “WHERE [Column Name] LIKE ‘Name*'”

The SqlClient.SqlConnection.ConnectionString is “Server=$SQLServer;Database=$SQLDB;Integrated Security=$Security;Connection Timeout=$Timeout” #Security defaults to SSPI and Timeout to 60 unless specified with the functions parameters.

Click here to download the PS file Get-SqlToDataTable

EXAMPLE:

#Note: Don't put instance name if using default instance name in SQL
$MyTable = Get-SqlToDataTable -SQLServer fitchmsql01 -SQLDB DBName -Target dbo.table_name -Filter "[Col 1],[Col 2],[Col 3]" -Where "WHERE [Column Name] LIKE 'Name*'"
#Or the minimum requirements:
$MyTable = Get-SqlToDataTable -SQLServer fitchmsql01\namedinstance -SQLDB DBName -Target dbo.table_name

CODE:

function Get-SqlToDataTable
{
<#
    .EXAMPLE
    $DataTable = Get-SqlToDataTable -SQLServer fitchmsql01 -SQLDB DBName -Target dbo.table_name
    .PARAMETER SQLServer
    Named instances SERVERNAME\INSTANCENAME | If it's a default instance only use server name, do not add \MSSQLSERVER.
    .PARAMETER SQLDB
    Name of the database where the target table is.
    .PARAMETER Target
    The Table in which you're targeting
    .PARAMETER Filter
    Default="*" Used in selecting the columns from the target, make sure it's a valid SQL statement. Example "[Parent Container],[In AD],[Domain]"
    .PARAMETER Security
    Default="SSPI" Used in connection string to SQL DB.
    .PARAMETER Timeout
    Default="60" Used in connection string.
    .PARAMETER Where
    Variable in command text after Select * From Target. Example: "WHERE [Column Name] LIKE 'Name*'"
#>
param(
    [Parameter(Mandatory=$true)][string]$SQLServer,
    [Parameter(Mandatory=$true)][string]$SQLDB,
    [Parameter(Mandatory=$true)][string]$Target,
    [string]$Filter= "*",
    [string]$Security="SSPI",
    [Int]$Timeout="60",
    [string]$Where
)
begin {
#Create connection object to SQL instance
    $DataTable = $null
    if ($SQLConnection.State -ne [Data.ConnectionState]::Open) {
        $SQLConnection = New-Object System.Data.SqlClient.SqlConnection
        $SQLConnection.ConnectionString = "Server=$SQLServer;Database=$SQLDB;Integrated Security=$Security;Connection Timeout=$Timeout"
        $SQLConnection.Open()
    }
}
process {
    if ($SQLConnection.State -ne [Data.ConnectionState]::Open){"Connection to SQL DB not open"}
    else
    {
        #Command to be executed
        $SQLCommand = New-Object System.Data.SqlClient.SqlCommand
        $SQLCommand.Connection = $SQLConnection
        $SQLCommand.CommandText = "Select $Filter FROM $Target $Where"
        #Empty DataTable to be filled by SQL Adapter
        $DataTable = New-Object System.Data.DataTable
        #SQL Adapter used to execute SQL command
        $SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
        $SQLAdapter.SelectCommand = $SQLCommand
        $SQLAdapter.Fill($DataTable) | Out-Null
        Return ,$DataTable
    }
}
end{
        #Closing connection to SQL server if open
        if ($SQLConnection.State -eq [Data.ConnectionState]::Open) {
            $SQLConnection.Close()
        }
    }
}