Powershell - Sql Query Result To Variable With Properties

Why the stored output of SQLCMD has only Length property instead of column names?. Is it not possible to store sqlcmd output with its properties? Invoke-sqlcmd stores it correctly

Solution 1:

$var=(SQLCMD -S 'x.x.x.x' -U 'user' -P 'password' -i "C:\query.sql" -W -m 1) 

You're calling sqlcmd.exe, which has no concept of what .Net objects are let alone how to pass them to PowerShell. As far as PowerShell is concerned, that command outputs strings. You will need to convert the strings to objects yourself.

If you have to use sqlcmd.exe, I would suggest something like this:

$Delimiter = "`t"$var = SQLCMD -S 'x.x.x.x' -U 'user' -P 'password' -i "C:\query.sql" -W -m 1 -s $Delimiter |
    ConvertFrom-Csv -Delimiter $Delimiter |
    Select-Object -Skip 1

I'm using tab as the field separator. If your data contains tabs, you'll need a different separator. You could also run into problems if your data contains double quotes. The Select-Object -Skip 1 is to skip the underline row that sqlcmd always creates below the header.

Also be aware that you should use the -w parameter on sqlcmd to prevent any incorrect wrapping. Also beware that null values are always output as a literal string NULL.

That said, I would still probably stick with Invoke-Sqlcmd. It's much less error prone and much more predictable. If I really needed performance, I'd probably use direct .Net methods or SSIS.

Solution 2:

I have written a function for that purpose... ist not fully fleshed out... hope it helps

functionInvoke-MSSqlCommand{
      [CmdletBinding()]
      param
      (
        [Parameter(Position=0, Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string]
        $Query,

        [Parameter(Position=1, Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string]
        $ConnectionString,

        [Switch]
        $NoOutput
      )
      try {
        $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
        $connection.ConnectionString = $ConnectionString$null = $connection.Open()
      }
      catch {
        Throw"$connectionstring could not be contacted"
      }
      $command = New-Object -TypeName System.Data.SqlClient.SqlCommand
      $command.CommandText = $query$command.Connection = $connectionif ($NoOutput) {
        $null = $command.ExecuteNonQuery()
      }
      else {

        if ($dataset.Tables[0].Rows[0] -eq $null) {
          write-verbose -Message 'no record'$connection.Close()
          return$null
        }

        $dataset.Tables[0].Rows
        $connection.close()
      }
    }