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

executeReader is returning a reader object, it's not a data array. You need to do some extra work to extract data from there — Mike Twc
– Mike Twc, Commented Nov 29, 2018 at 3:29

Mike Twc · Accepted Answer · 2018-11-29 02:56:35Z

8

If you are using sql server express 2016 or later you should be able to do it on the database side using FOR JSON clause. Try something like

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

answered Nov 29, 2018 at 2:56

Mike Twc

2,3752 gold badges17 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Darrin Over a year ago

Best answer, at this point (in 2019), for JSON output, you should let the database take care of the JSON, and only deal with writing the output to a file.

BeanyTheSane Over a year ago

As @AlMo320 mentions below, when I tried to use 'FOR JSON AUTO' in the query, for a long result, my data was truncated by this process, and I did NOT get the full query results.

riezebosch Over a year ago

$reader = $command.ExecuteReader(); while ($reader.Read()) { $reader[0] | Out-File temp.json -Append -NoNewline }; $reader.Close()

postanote · Accepted Answer · 2018-11-29 01:46:18Z

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
}

Ihor Patsian · Accepted Answer · 2020-04-08 06:05:28Z

2

The "rub" here is that the SQL command FOR JSON AUTO even with execute scalar, will truncate JSON output, and outputting to a variable with VARCHAR(max) will still truncate. Using SQL 2016 LocalDB bundled with Visual Studio if that matters.

edited Apr 8, 2020 at 6:05

Ihor Patsian

1,2982 gold badges17 silver badges27 bronze badges

answered Apr 7, 2020 at 18:42

AlMo320

1352 silver badges10 bronze badges

1 Comment

riezebosch Over a year ago

see my response on how to do that with an ExecuteReader: stackoverflow.com/questions/53529801/…

Collectives™ on Stack Overflow

Export from SQL Server to json text file using PowerShell

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related