Powershell and dbo.sysjobsteps.Command field being truncated - not a length issue On the accepted answer to Export SQL Agent jobs with steps and scripts, there is a comment:


  Command is unlikely to get pulled into Excel very nicely. – Aaron Bertrand ♦ Feb 2 '15 at 21:27


This seems very true.

I am trying to pull the dbo.sysjobsteps.Command field into a csv file using Powershell Export-Csv.  The data seems to be truncated as of the first space found in the Command field (has nothing to do with a length limitation).

Do you know why?
Using the script Powershell export query to csv, I was able to run it under Powershell and successfully export the job commands without encountering any truncation.  Are you doing something different?  Here is the script I ran:

$server = "."
$database = "msdb"
$query = "SELECT 
    j.Name 
    , j.[Description] 
    , js.Step_ID 
    , js.Step_Name
    , js.Database_Name 
    , js.Command 
    --, j.*, js.* 
FROM dbo.sysjobs j
INNER JOIN dbo.sysjobsteps js
    ON js.job_id = j.job_id 
--WHERE j.enabled = 1
ORDER BY j.name, js.Step_ID"

$extractFile = "C:\Scripts\TestCsv.csv"

$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

$DataSet.Tables[0] | Export-Csv $extractFile