SQL Query in Powershell

How to connect to, query, and deal with the results of a SQL server.  Source: systemcentercentral.com

Declare Variables:

$SQLServer = [server\instance]
$SQLDB = [database]
$SQLQuery = "select [columns] from [table]"

Connect using integrated security and run query.  Results stored in $DataSet:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDB; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()

Handle each row of results in $DataSet.  1st Field is [0], 2nd field is [1], etc.  Can specify .ToString(), .Trim(), .DateTime, calculations, etc.:

foreach ($row in $DataSet.Tables[0].Rows)
{
    $Field1 = $row[0].ToString()
    Write-Host $Field1
}

Execute Powershell as Scheduled Task

Running a Powershell script as a scheduled task is straight forward.  Create a scheduled task with this "Start a program" action:

Program/script: PowerShell.exe
Arguments: -NoLogo -File "Path\file.ps1"

Detect corrupt WAV files with Powershell

 If you need to check .wav files for corruption, here's a way to accomplish that with PowerShell.  Here's a few things to know:

• The first four ASCII characters in a working WAV file are RIFF: cpfaq.blogspot.com
• PowerShell Get-Content: technet.microsoft.com
• PowerShell array notation: technet.microsoft.com

This script checks the first character of the wav file header.:
 

$c = (Get-Content -Path C:\example.wav -Encoding Ascii -TotalCount 1)[0]
Write-Host $c
if ($c -eq "R" )

    {write-host "File OK"}
else
    {write-host "File Broke"}

Execution Policy for PowerShell

When trying to execute a PowerShell script you may encounter this error message:

File [Path]\[filename].ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.
    + CategoryInfo          : SecurityError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : UnauthorizedAccess

To check your execution policy, run Get-ExecutionPolicy  (be sure to check 32 & 64 bit).
To change your execution policy, run Set-ExecutionPolicy [Policy level] ... I suggest starting with 'AllSigned', one step below 'Restricted'.  If you get the below message you need to launch PowerShell as Administrator:

Set-ExecutionPolicy : Access to the registry key 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell' is denied.

 Once complete, you'll see a registry entry at that location String named 'ExectionPolicy', Data '[Policy level]'

Options for Policy level are:

• Restricted
• AllSigned
• RemoteSigned
• Unrestricted