Invoke SQL Queries

Running SQL queries from powershell can be extremely powerfull. However some implementation I have see often result with off object types such as DBNull. This is my method that seems to work quite nicely for me.

$tsqlQuery = @"
Select AccountName
From SUSDB.dbo.tbDownstreamServerTarget
Try {
Catch  {

$command = $conn.CreateCommand()
$command.CommandText = $tsqlQuery
$table = new-object System.Data.DataTable
$data = $command.ExecuteReader()


Working with .NET Types, Assemblies, Etc

Here are some tricks and snippets that I find quite useful when dealing with objects, types and using .NET assemblies, etc.

Find all loaded Assemblies



Adding Assemblies

Add-Type -AssemblyName Windows.Forms

Add-Type -AssemblyName "Microsoft.SqlServer.SMO, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

#With Partial Name (deprecated) 

#Targeted version by file(Much better in terms of reliability) 

#Targeted version by Assembly String 
[Reflection.Assembly]::Load('Microsoft.UpdateServices.Administration, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35') 

Note: You cannot unload assemblies from the application domain so once they’re in, they’re in. Note also to find the Assembly Strings use:

[AppDomain]::CurrentDomain.GetAssemblies() | Select FullName


Find Method Overloads

If you call a method without the brackets powershell will show you a list of all the different overload options available


static int Compare(string strA, string strB)
static int Compare(string strA, string strB, bool ignoreCase)


Find Hidden Properties
There’s often more than get-member will normally show, hidden properties and methods:

$p | GM -Force


Get a List of TypeNames