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'
)