Powershell script executing SQL procedure with parameters has truncated output

Question

I have a Powershell script that is successfully connecting to a SQL Server database and executing a procedure. The SQL procedure contains a select as follows:

SELECT @sql AS 'ColDemo1'

The variable @sql is nvarchar(max). I want the full contents of @sql returned to a new sql file however I only get the first line and an unwanted column heading as below:

ColDemo1
------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
First line of data...                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

The Powershell script is as follows:

$server = "SERVER\DEMO"
$database = "dbDemo"
$pZero = "PVal0"
$pOne = "PVal1"
$pTwo = "PVal2"
$pThree = "PVal3"

function Run-SQLUSP {
    param (
        $pZero,
        $pOne,
        $pTwo,
        $pThree
    )

    $conn = New-Object System.Data.SqlClient.SqlConnection("Server=${server};Database='${database}';Integrated Security=TRUE")
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = "dbo.demoSp '$pZero', '$pOne', '$pTwo', '$pThree'"
    $adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
    $dataset = New-Object System.Data.DataSet
    [void]$adapter.Fill($dataset)
    $dataset.tables[0]
}

Run-SQLUSP $pZero $pOne $pTwo $pThree | Out-File "c:\DemoFolder\DemoScriptName.sql" -width 8000

How can I amend my Powershell script to return all lines of the output to the new sql file? When executing the procedure in SSMS the resultset is returned in a single table cell (not multiple rows).

Answer 1

Two changes were required:

To avoid truncating the output: Change Out-File to Set-Content -Path and remove -width 8000.

To only return the first cell: Append .rows[0][0] to $dataset.tables[0] within the function.

Below is the revised code in full:

$server = "SERVER\DEMO"
$database = "dbDemo"
$pZero = "PVal0"
$pOne = "PVal1"
$pTwo = "PVal2"
$pThree = "PVal3"

function Run-SQLUSP {
    param (
        $pZero,
        $pOne,
        $pTwo,
        $pThree
    )

    $conn = New-Object System.Data.SqlClient.SqlConnection("Server=${server};Database='${database}';Integrated Security=TRUE")
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = "dbo.demoSp '$pZero', '$pOne', '$pTwo', '$pThree'"
    $adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
    $dataset = New-Object System.Data.DataSet
    [void]$adapter.Fill($dataset)
    $dataset.tables[0].rows[0][0]
}

Run-SQLUSP $pZero $pOne $pTwo $pThree | Set-Content -Path "c:\DemoFolder\DemoScriptName.sql"