Getting SQL value from sqlcmd.exe remotely

Question

I should execute remotely SQL command on database because sqlcmd is not installed.

$Configuration = Invoke-Command -ComputerName $dataserverName -Credential $cred -ScriptBlock {
    sqlcmd.exe -S $using:dataserver -U $using:user -P $using:pass -d $using:database -q $using:SQLQuery
}

So when I execute my request, I got the response in the same block so I can not get value from each column like this:

MinimumSize          MaximumSize          Delimiter             IdFile
-------------------- -------------------- --------- ----------------------
                NULL                 NULL         |                   6

(1 rows affected)

so if I do $Configuration[0], I got

$Configuration[0] = MinimumSize          MaximumSize          Delimiter             IdFile

like a line, so I can not do anything. My question is how to get for example $Configuration.IdFile = 6.

Answer 1

I've also been on systems where Invoke-Sqlcmd was not present and installing software was not allowed. SQLCMD.exe returns an array of strings representing each line returned, not an object like Invoke-Sqlcmd does, so you have to parse $Configuration[2].

But here's a trick: alter the SQL statement to return XML instead of a table and you can let Powershell do the parsing for you. You didn't provide the SQL, but it should be pretty easy to adapt this to suit your needs:

$SQL = @"
   SET NOCOUNT ON
   DECLARE @XML AS XML
   SET @XML = (--change this SELECT statement to match yours, but keep the 'AS Row' table alias:
     SELECT Name, recovery_model_desc 
     FROM master.sys.databases AS Row 
     FOR XML AUTO, ROOT('table') -- also keep this line 
   )
   SELECT CAST (@XML AS nvarchar(max))  -- do this to prevent SQLCMD adding line breaks   
"@
[string] $output = (SQLCMD -S 'YOUR_SQL_SERVER' -E -h-1 -y0 -Q $SQL)
([xml] $output).table.Row[0].Name #now you have an array of rows and can reference each field
([xml] $output).table.Row | Out-GridView #you can also put all the rows into a GridView!