Powershell - SQL query result to variable with properties

Question

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 but Invoke-SQLcmd takes a bit longer to process so I'm trying to make it work with SQLcmd as this method will be part of different scripts that are scheduled to run every minute, once ever hour etc.,

Any idea if this is possible or what the issue is?

Store output and echo $var:

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

     PS C:> $var
     job_id name
     ------ ----
     12345-aaaa-1234-5678-000000000000000 Clear DB entries
     12345-bbbb-1234-5678-000000000000000 TempLog DB

Echo $var[0,1,2] which doesn't show property names.

     PS C:> $var[0]
     job_id name
     PS C:> $var[1]
     ------ ----
     PS C:> $var[2]
     12345-aaaa-1234-5678-000000000000000 Clear DB entries

Show $var properties

     PS C:> $var | select *
     Length
     ------
     11
     53

Show $var type

     PS C:> $var.GetType()

     IsPublic IsSerial Name                                     BaseType
     -------- -------- ----                                     --------
     True     True     Object[]                                 System.Array

I've never used SQLCMD but based on what you have shown it returns an array of strings. Not row objects. $var[0] should tell you the property names then you could parse the rest of the lines to create your own custom objects if you wanted. — EBGreen, Mar 14 '18 at 18:14
You should add your SQL query code (C:\query.sql) to this post so we can see what you are trying to accomplish. — cet51, Mar 14 '18 at 18:14
Code should not matter but it is `select job_id, name from table;` — user630702, Mar 14 '18 at 18:27
@EBGreen Can you tell me how to parse the rest of it to create custom object? I'm researching. A one-liner would be cool especially if we can stream to the `sqlcmd` output. — user630702, Mar 14 '18 at 18:29
No, using a cmdlet so you get actual row objects back would be cool. How to parse it depends a lot on what the data looks like. If SQLCMD let's you specify a delimiter then I think I would first try using a comma for a delimiter then treating it as a CSV and use Import-CSV to get an array of objects. If your data breaks that then you will have to parse each line yourself. Regardless, you need to try to write the code yourself then ask specific questions regarding specific issues that you have with the code that you write. — EBGreen, Mar 14 '18 at 18:35

Bacon Bits · Accepted Answer · 2018-03-15T14:31:15.113

$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.

Thanks, It works as expected. Thought $Delimiter variable was not accepted for `sqlcmd -s switch` so I defined it manually for both. — user630702, Mar 15 '18 at 10:05
@User Well, it's not accepted by `sqlcmd`, per se. The command line parser expands the variable before passing it as a parameter to the executable. It amounts to the same thing, but there's a middle man here. — Bacon Bits, Mar 15 '18 at 14:29

score 0 · Answer 2 · answered Mar 14 '18 at 18:43

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

    function Invoke-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 = $connection


      if ($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()
      }
    }

Thanks for the script. I'll make use of it. – user630702 Mar 15 '18 at 10:04 — user630702, Mar 15 '18 at 10:04

Powershell - SQL query result to variable with properties

2 Answers2

Linked