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
– EBGreen, Commented Mar 14, 2018 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
– cet51, Commented Mar 14, 2018 at 18:14
Code should not matter but it is select job_id, name from table; — user630702
– user630702, Commented Mar 14, 2018 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
– user630702, Commented Mar 14, 2018 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
– EBGreen, Commented Mar 14, 2018 at 18:35

Bacon Bits · Accepted Answer · 2018-03-15 14:31:15Z

4

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

edited Mar 15, 2018 at 14:31

answered Mar 14, 2018 at 18:35

Bacon Bits

32.5k6 gold badges63 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user630702 Over a year ago

Thanks, It works as expected. Thought $Delimiter variable was not accepted for sqlcmd -s switch so I defined it manually for both.

Bacon Bits Over a year ago

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

ForEachCookieInJarGrowALittle · Accepted Answer · 2018-03-14 18:43:18Z

0

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

answered Mar 14, 2018 at 18:43

ForEachCookieInJarGrowALittle

113 bronze badges

1 Comment

user630702 Over a year ago

Thanks for the script. I'll make use of it.

Collectives™ on Stack Overflow

Powershell - SQL query result to variable with properties

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related