CODEWITHSUNDEEP
sql-serverpowershell
lit
lit

Reputation: 16266

Get Invoke-Sqlcmd to show '0' and '1' for BIT columns

Invoke-Sqlcmd displays 'True' or 'False' for BIT columns.

SSMS displays '1' or '0' for BIT columns.

Yes, you and I know that 0=false and 1=true. Some more "business-oriented" users want to see it just like SSMS does it.

I can think of code to do it, but it is not brief. How can I get Invoke-Sqlcmd to display '0' and '1' for BIT column values?

SELECT TOP 1 value FROM master.dbo.MSreplication_options;

Output in SSMS:

Output is SSMS

Output in PowerShell console:

PS C:\> Invoke-Sqlcmd -ServerInstance ADB -TrustServerCertificate -Query 'SELECT TOP 1 value FROM master.dbo.MSreplication_options;'

value
-----
 True

Upvotes: 1

Views: 172

Answers (1)

mklement0
mklement0

Reputation: 439777

Based on your update it sounds like you have the same problem as the one in the linked post, so this answer there offers a solution, based on modifying your queries:

SELECT TOP 1 cast(value as int) FROM master.dbo.MSreplication_options;

If you don't want to modify your queries, you'll need to resort to post-processing, which, however, is (a) more cumbersome and (b) performs worse:

$query = 'SELECT TOP 1 value FROM master.dbo.MSreplication_options;'

# An ordered helper hashtable (dictionary) for storing
# copies of each input object's properties, from which
# a [pscustomobject] can be constructed.
$helperDict = [ordered] @{}

Invoke-SqlCmd -Query $query <# ... #> |
  ForEach-Object {
    # Fill the helper dictionary with the current row's
    # properties, translating [bool] into [int] values.
    foreach ($prop in $_.psobject.Properties) {
      $name, $value = $prop.Name, $prop.Value
      # Exclude standard properties of DataRow instances.
      if ($name -in 'RowError', 'RowState', 'Table', 'ItemArray', 'HasErrors') { continue }
      $helperDict[$name] = if ($value -is [bool]) { [int] $value } else { $value }
    }
    # Construct and output a custom object from the helper dictionary.
    [pscustomobject] $helperDict    
  }

Note:

  • Since the data types of the properties of DataRow instances are immutable, updating them directly is not an option, so the above creates [pscustomobject] copies of them, including only those properties that represent actual column values (by explicitly including standard properties).

  • You could optimize the above by caching the names of the properties as well as the names of those that have [bool] values after having analyzed the first row, which saves you from having to use reflection in each iteration.

If you additionally want (modified) DataRow instances as output, a different approach is required; here it is wrapped in a custom function, Convert-BitColumsToInt:

# Accepts a stream of DataRow objects, assumed to be from the same
# table, such as output by Invoke-SqlCmd,
# and creates modified copies with [bool] column values replaced
# by [int] ones.
function Convert-BitColumsToInt {
  param(
    [Parameter(Mandatory, ValueFromPipeline)]
    [System.Data.DataRow] $InputObject
  )
  begin {
    $dateTableClone = $null
  }
  process {
    if ($null -eq $dateTableClone) { # First row received.
      # Create an empty clone of the input objects' data table.
      $dataTableClone = $InputObject.Table.Clone()
      # Change the data type of [bool] columns to [int]
      foreach ($col in $dataTableClone.Columns.Where({ $_.DataType -eq [bool] })) {
        $col.DataType = [int]
      }
    }
    # Copy the row's values to a new row in the clone, which implicitly
    # converts the [bool] values to [int], and pass the new row through
    # (thanks to enclosure in (...))
    ($dataTableClone.Rows.Add($InputObject.ItemArray))
    $dataTableClone.Clear()
  }
}

# Sample call
Invoke-SqlCmd -Query $query <# ... #> | 
  Convert-BitColumsToInt

Upvotes: 1

Related Questions