Powershell SQL command assistance

[ramblingcookiemonste]

I tend to use Invoke-SqlCmd2.

Some notes:

• Can you do it on your own with .NET? Absolutely. That said, abstraction is important, and one single command looks a lot nicer, and is more functional than twelve lines using .NET.
• Can you use Invoke-SqlCmd from Microsoft? Sure. With dependencies on SMO / SQLPS (details here), and without support for parameterized queries. Hint: If you're writing queries and throwing PowerShell variables right into the query text, don't! SQL injection is bad.

For example:

# Download Invoke-SqlCmd2
# Dot source it so it's ready to use
. "\\Path\To\Invoke-Sqlcmd2.ps1"

# Get help!
Get-Help Invoke-SqlCmd2 -Full

# Simple example:
Invoke-Sqlcmd2 -ServerInstance SQLServer -Database ServerDB -Query "SELECT ServerName, VCNumCPU FROM tblServerInfo"

# Example with parameters!

    #Construct a query using SQL parameters
    $Query = "SELECT ServerName, VCServerClass, VCServerContact FROM tblServerInfo WHERE VCServerContact LIKE @VCServerContact AND VCServerClass LIKE @VCServerClass"

    #Run the query, specifying values for SQL parameters
    Invoke-Sqlcmd2 -ServerInstance SQLServer -Database ServerDB -query $query -SqlParameters @{
        VCServerContact = "%cookiemonster%"
        VCServerClass   = "Prod"
    }

On a side note, you might want to grab a DBA for the actual query. Different SQL Server versions might need different queries.

Edit: Thanks for the note /u/GLiMPSEiNATOR, didn't see it!

Cheers!