Invoke-Sqlcmd Returns Columns RowError and HasErrors

I’ve been performing some SQL queries recently using PowerShell and Invoke-SqlCmd, but I’ve noticed that by default Invoke-Sqlcmd returns columns RowError and HasErrors.  Here is a simple example of returning a list of devices (a single column of data) from a database:

Invoke-Sqlcmd -ServerInstance "AlkaneSQLInstance" -Database "AlkaneSQLDatabase" -Query "SELECT Device FROM Devices"
PowerShell

Of course I pipe this into Export-CSV like so:

Invoke-Sqlcmd -ServerInstance "AlkaneSQLInstance" -Database "AlkaneSQLDatabase" -Query "SELECT Device FROM Devices" | Export-Csv -NoTypeInformation -Path "C:\Temp\Alkane.csv"
PowerShell

When we read the exported data in the CSV we expect one column of data called ‘Device’.  However, instead we can also see columns called RowError, HasErrors and others!  It appears that Invoke-Sqlcmd returns columns RowError and HasErrors.  And whilst I’m not sure why these appear, we can omit them like so:

Invoke-Sqlcmd -ServerInstance "AlkaneSQLInstance" -Database "AlkaneSQLDatabase" -Query "SELECT Device FROM Devices" | Select * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors
Visual Basic

and of course if we wanted to pipe this into a CSV we can do so like this:

Invoke-Sqlcmd -ServerInstance "AlkaneSQLInstance" -Database "AlkaneSQLDatabase" -Query "SELECT Device FROM Devices" | Select * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors | Export-Csv -NoTypeInformation -Path "C:\Temp\Alkane.csv"
Visual Basic

And voila.  We only get the columns of data that we asked for!

Check if System.Object[] contains a value (Updated)

This isn’t the most interesting of blog posts admittedly.  But I was using the SQL Server Management Objects (SSMO) in my PowerShell script to return a result set from a stored procedure in SQL server.  The data set returned from the Invoke-sqlcmd command was a System.Object[] type, and this blog explains how to check if System.Object[] contains a value.

Check if System.Object[] contains a value

I wanted to see if a column (the Department column) in the result set contained a specific value. So firstly I obtained the result set like so:

# Load the SQL Server SMO library
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") 
Import-Module "sqlps" -DisableNameChecking

#SQL Server
$SQLServer = "ALKANESQLSERVER"
#Database
$SQLDatabase = "ALKANESQLDATABASE"
#Return results from stored procedure
$sql_departments = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query 'exec [dbo].[ALKANESTOREDPROCEDURE]'
PowerShell

In PowerShell 3 it was a trivial exercise:

if ($sql_departments."Department" -contains "AlkaneDepartment")
{

}
PowerShell

however in PowerShell 2 it required slightly more legwork, and was a little slower to run:

if (($sql_departments | Select -ExpandProperty Department) -contains "AlkaneDepartment")
{

}
PowerShell

To provide another example, consider we’re iterating through some local profile folders and we want to see if it contains a folder contains a certain name.  We can run:

$localprofiles = get-childitem c:\Users
PowerShell

and check which properties it contains by running:

$localprofiles | Select *
PowerShell

From this, we can see that the property we want to check is called name, so we simply check (using PowerShell 3+) by writing:

if ($localprofiles.Name -contains "profilename") {
    #do something
}
PowerShell

Easy!