Export from SQL Server to json text file using PowerShell

Question

You should read this if: you are trying to find out how to transform SQL server data into JSON and put it into a text .json file

Question:

Can someone tell me what's wrong with this code? My goal is to read data from a SQL Server table, convert it to JSON and then save the result as a JSON text file. The code runs but the resulting .json file just has:

{
        "FieldCount":  11
    },
    {

repeated over and over again and nothing more.

My code:

$instance = "localhost\SQLEXPRESS"
$connectionString = "Server=$Instance; Database=myDB;Integrated Security=True;"
$query = "Select  * from myTable"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$result | ConvertTo-Json | Out-File "file.json"

$connection.Close()

Update:

Will award the answer to postanote as technically he/she answered my original question (although I will caveat and say I have not tried it).

However I would recommend either Mike's answer or what I eventually ended up going with, using BCP:

bcp "select * from myTable FOR JSON AUTO" queryout "C:\filepath\testsml.json"  -c -S ".\SQLEXPRESS" -d myDBName -T

• Note that the JSON AUTO will automatically come up with a json scehma for you vs. JSON pipeline which allows you to customize it.
• You have to install BCP first: https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017

Answer 1

Try something like this …

### Exporting SQL Server table to JSON

Clear-Host

#--Establishing connection to SQL Server --# 

$InstanceName = "."
$connectionString = "Server=$InstanceName;Database=msdb;Integrated Security=True;"

#--Main Query --# 

$query = "SELECT * FROM sysjobs"

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()

$table = new-object "System.Data.DataTable"

$table.Load($result)

#--Exporting data to the screen --# 

$table | select $table.Columns.ColumnName | ConvertTo-Json

$connection.Close()

# Results

{
    "job_id":  "5126aca3-1003-481c-ab36-60b45a7ee757",
    "originating_server_id":  0,
    "name":  "syspolicy_purge_history",
    "enabled":  1,
    "description":  "No description available.",
    "start_step_id":  1,
    "category_id":  0,
    "owner_sid":  [
                      1
                  ],
    "notify_level_eventlog":  0,
    "notify_level_email":  0,
    "notify_level_netsend":  0,
    "notify_level_page":  0,
    "notify_email_operator_id":  0,
    "notify_netsend_operator_id":  0,
    "notify_page_operator_id":  0,
    "delete_level":  0,
    "date_created":  "\/Date(1542859767703)\/",
    "date_modified":  "\/Date(1542859767870)\/",
    "version_number":  5
}