Asterisks in output instead of numbers

Welcome Forums General PowerShell Q&A Asterisks in output instead of numbers

Viewing 1 reply thread
  • Author
    Posts
    • #231934
      Participant
      Topics: 21
      Replies: 41
      Points: 303
      Rank: Contributor

      Hello,

      The following script displays the size of SQL Server databases.  However, some data is displayed as asterisks (*) instead of the full number.  I’m assuming it’s because the length of the column is too small.  Any ideas on how I can adjust the length?

      #Function Get-SpacedUsed {
      [CmdletBinding()]
      
      Param(
      # the mandatory argument indicates that the parameter is required.
      [Parameter(Mandatory=$true)]
      [String] $inputfilename
      
      )
      
      [string] $username = ''
      [string] $password = ''
      
      # the variable to build connection, command, adapter, and dataset
      $sqlConn = New-Object System.Data.SqlClient.SqlConnection
      
      # defining object to use sql commands
      $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
      $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
      
      # dataset to store the adapter result
      $dataset1 = New-Object System.Data.DataSet
      $dataset2 = New-Object System.Data.DataSet
      
      # store the output of PSCustomObject to a hash table
      $object = @()
      
      # loop through each server listed in inputfile
      
      foreach ($srv in get-content $inputfilename) {
      
      # reset the dataset
      $dataset1.Reset()
      $dataset2.Reset()
      
      # build the connection string
      #$sqlConn.ConnectionString = "Server=$srv;Database=master;IntegratedSecurity=True"
      #$sqlConn.ConnectionString = "Server=$srv;Database=master;User ID=$username;Password=$password;"
      $sqlConn.ConnectionString = "Server=$srv;Database=master;User ID=$username;password=$password;"
      
      # fetch database name from sys.databases table and ignore system database
      $sqlCmd.CommandText = "SELECT name FROM master.sys.databases WHERE database_id > 4"
      
      # setup the sql connection using sqlclient namespace
      $sqlCmd.Connection = $sqlConn
      
      # fill the datasets based on the data we are extracting
      $sqlAdapter.SelectCommand = $sqlCmd
      
      # SqlDataAdapter serves as a bridge between a dataset and SQL Server for
      # retrieving and saving data
      $sqlAdapter.Fill($dataset1) | Out-Null
      
      # assign dataset1 to a variable and referring the indicator to start at 0
      $dbs = $dataset1.Tables[0]
      
      # loop through the listed databases
      foreach ($db in $dbs) {
      
      # prepare the sql statement by appending the database name to stored procedure
      $sqlCmd.CommandText = $db.name+"..sp_spaceused "
      
      # setup the sql connection
      $sqlCmd.Connection = $sqlConn
      
      # assign the query to an interface to retrieve data
      $sqlAdapter.SelectCommand = $sqlCmd
      
      # fill dataset; create an in-memory cache of a data table that can easily be manipulated
      $sqlAdapter.Fill($dataset2) | Out-Null
      }
      
      # close the connection
      $sqlConn.Close()
      
      # the below code merges the resultset of two datasets
      for($i = 0; $i -lt $dataset2.Tables[0].Rows.Count; $i++){
      $object += New-Object PSObject -Property @{
      "ServerName" = $srv
      "DatabaseName" = $($dataset2.Tables[0].Rows[$i][0])
      "DatabaseSize" = $($dataset2.Tables[0].Rows[$i][1])
      "UnallocatedSpace" = $($dataset2.Tables[0].Rows[$i][2])
      "Reserved" = $($dataset2.Tables[1].Rows[$i][0])
      "Data" = $($dataset2.Tables[1].Rows[$i][1])
      "IndexSize" = $($dataset2.Tables[1].Rows[$i][2])
      "Unused" = $($dataset2.Tables[1].Rows[$i][3])
      }
      }
      }
      
      # selecting the attributes to console
      $object | Select-Object ServerName, DatabaseName, DatabaseSize, UnallocatedSpace, Reserved, Data, IndexSize, Unused | Format-Table -AutoSize
      $json = $object | Select-Object ServerName, DatabaseName, DatabaseSize, UnallocatedSpace, Reserved, Data, IndexSize, Unused | ConvertTo-Json
      
      #region
      #--------------------------------------------------------------------
      $dateString = (Get-Date).ToString("MMddyyyy")
      $filePath = "C:\Temp\sqlstorage\output\"
      $fileName = ($filePath + "sqlgrowthoutput-" + $dateString + ".json")
      #endregion
      
      # write the output to file
      $json | Out-File $fileName
      
      # invoke process to open JSON file
      #Invoke-Item $fileName
      #} # end Get-SpaceUsed
      
      

      Thanks!

    • #231940
      Participant
      Topics: 12
      Replies: 1643
      Points: 2,665
      Helping Hand
      Rank: Community Hero

      Assuming you’re referencing line 93:

      $object | Select-Object ServerName, DatabaseName, DatabaseSize, UnallocatedSpace, Reserved, Data, IndexSize, Unused | Format-Table -AutoSize
      

      Typically, if the data does not fit, it is represented by a elipsis (…), not an asterisk (*). Try Format-List instead. If you look at the JSON, the data is correct. The continuation only is indicated in console output, the full data should be there for ConvertTo-Json or other cmdlets

Viewing 1 reply thread
  • You must be logged in to reply to this topic.