Extract SQL Server database to JSON file for import into Invantive SQL

Wiki (en)
,
1

It is not always possible to directly access data on SQL Server from Invantive SQL, for instance when the SQL Server is only available through RDP.

The following query and import enable you to export the data to JSON-format (actually NDJSON) using the Windows command line and then access the data from within Invantive SQL.

First step is to create a batch file:

sqlcmd -i c:\temp\export.sql -S DNSNAME -U USERNAME -P PASSWORD -d DATABASENAME -h -1 -W -o c:\temp\export.json

The option -h -1 disables the headers from the query output and the -W removes the trailing spaces to reduce line size.

Next, create a file c:\temp\export.sql containing your query such as:

--
-- Export SQL Server data to JSON.
--
set nocount on

--
-- Generate one line of JSON per row to avoid exceeding the maximum
-- output line length of sqlcmd.
--
select ( select ls_id, LS_MaxExecutions
         for    json path, include_null_values, without_array_wrapper
       )
from   TABLE

Then run the batch file.

The NDJSON-file is stored as c:\temp\export.json and can be queried from with Invantive SQL using:

select *
from   ndjsontable
       ( passing file 'c:\temp\x.json'
         columns ls_id          number not null label 'ID' path 'ls_id'
         ,       ls_maxecutions number     null label 'Maximum Executions' path 'LS_MaxExecutions'
      )