Экспорт из SQL Server в текстовый файл JSON с помощью PowerShell

Question

Вам следует прочитать это, если: вы пытаетесь выяснить, как преобразовать данные сервера SQL в JSON и поместить их в текстовый файл.json.

Вопрос:

Может кто-нибудь сказать мне, что не так с этим кодом? Моя цель - прочитать данные из таблицы SQL Server, преобразовать их в JSON и затем сохранить результат в виде текстового файла JSON. Код работает, но в результате .json Файл просто имеет:

{
        "FieldCount":  11
    },
    {

повторяется снова и снова и не более того.

Мой код:

$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()

Обновление:

Присудит ответ на постаноте, поскольку технически он / она ответил на мой первоначальный вопрос (хотя я сделаю оговорку и скажу, что не пробовал).

Тем не менее, я бы порекомендовал либо ответ Майка, либо то, что я в итоге получил, используя BCP:

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

• Обратите внимание, что JSON AUTO автоматически предложит вам схему json против JSON, которая позволит вам настроить его.
• Сначала вы должны установить BCP: https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017

Answer 1

Попробуйте что-то вроде этого...

### 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
}

Answer 2

Если вы используете SQL Server Express 2016 или более поздней версии, вы можете сделать это на стороне базы данных, используя предложение FOR JSON. Попробуйте что-то вроде

$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"

Answer 3

Проблема здесь в том, что команда SQL FOR JSON AUTO даже при выполнении скаляра будет обрезать вывод JSON и выводить в переменную с VARCHAR(max)все равно будет усекать. Использование SQL 2016 LocalDB в комплекте с Visual Studio, если это важно.