2

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?

2
  • 3
    Hi there, welcome to the site. It's a bit hard to tell what's actually happening without more information. Could you share the input, the actual powershell script and the truncated output?
    – Tom V
    Jun 21, 2017 at 18:12
  • How are you checking the .csv file - by opening in Excel, or in a text editor?
    – RDFozz
    Jun 22, 2017 at 17:04

1 Answer 1

1

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
1
  • Just a note, but you can use the built-in format operator, -f, instead of String.Format(). Like so: $connectionString = $connectionTemplate -f $server, $database. I'd also tend to use $command = $connection.CreateCommand() to instance the command and assign the connection in one line.
    – Bacon Bits
    Jun 25, 2017 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.