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"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"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, HasErrorsand 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"And voila. We only get the columns of data that we asked for!