0

I'm running a powershell script to get the results of a SQL query (in JSON format from SQL2016) and the results come back broken up into individual lines with '...' on the end instead of one JSON string and some header info at the top of the file. This makes the JSON unuseable.

I verified that this is on the PowerShell side by running the same query in SSMS and the results came out as expected (valid JSON)

I couldn't find any command line arguments for controlling the output of Invoke-SqlCommand

I'm new to PowerShell... Any ideas how to help me get clean JSON from this PowerShell script?

The powershell script:

Invoke-Sqlcmd -InputFile "C:\Dashboard\sql\gtldata.sql" | Out-File -filepath "C:\Dashboard\json\gtldata.json"

A sample of the returned document:

JSON_F52E2B61-18A1-11d1-B105-00805F49916B                                                                                                  
-----------------------------------------                                                                                                  
{"KPI":[{"BusinessUnit":"Water - Industrial","Location":"SPFIN","TestDate":"2016-09-19T21:11:10.837","TestResult":"Fail","FailReason":"P...
ial":"100161431","PumpType":"xxx","Stages":0},{"BusinessUnit":"Water - Industrial","Location":"SPFIN","TestDate":"2016-09-20T01:48...
"PumpType":"xxx","Stages":0},{"BusinessUnit":"Pre-engineered","Location":"SPSPA","TestDate":"2016-09-20T10:46:38.403","TestResult"...
beeker
  • 780
  • 4
  • 17
  • Try adding " | Format-Table" or some other formatting cmdlet: https://learn.microsoft.com/en-us/powershell/scripting/using-format-commands-to-change-output-view?view=powershell-6. – jmoon Aug 11 '17 at 17:58
  • I get {Out-File : Cannot validate argument on parameter 'Encoding'. The argument "Format-Wide" does not belong to the set "unknown,string,unicode,bigendianunicode,utf8,utf7,utf32,ascii,default,oem" specified by the ValidateSet attribute. Supply an argument that is in the set and then try the command again...} Still learning PowerShell, I'll do some more research on this error – beeker Aug 11 '17 at 18:06
  • Format-Wide takes in a single input. Try one of the other format cmdlets, depending on what your output is. I'm assuming it's a table, so | Format-Table should work better than Format-Wide. – jmoon Aug 11 '17 at 18:07
  • First guess `Invoke-Sqlcmd -InputFile "C:\Dashboard\sql\gtldata.sql" | Select -Expand JSON*`. That will expand the property to just its value which you can then output to file. `Set-Content` might be a better choice. Invoke-SQLCmd should be returning a datatable iirc so you might need to play with that better than I am suggesting. – Matt Aug 11 '17 at 18:17

2 Answers2

0

You need to add the -MaxCharLength option to your invocation. By default, sqlcmd (and thus Invoke-Sqlcmd) use an 80-character output width, as cited in this sqlcmd documentation:

-w column_width

Specifies the screen width for output. This option sets the sqlcmd scripting variable SQLCMDCOLWIDTH. The column width must be a number greater than 8 and less than 65536. If the specified column width does not fall into that range, sqlcmd generates and error message. The default width is 80 characters. When an output line exceeds the specified column width, it wraps on to the next line.

I'd try setting -MaxCharLength 65535 for your output, since unformatted JSON will not have any line breaks in it.

Community
  • 1
  • 1
Laughing Vergil
  • 3,706
  • 1
  • 14
  • 28
0

Try the below as It worked for me. The result was a non truncated output of a varbinary column to string

original post [here][1]

bcp "SELECT CAST(BINARYCOL AS VARCHAR(MAX)) FROM OLTP_TABLE WHERE ID=123123 AND COMPANYID=123" queryout "C:\Users\USER\Documents\ps_scripts\res.txt" -c -S myserver.db.com  -U admin -P password


  [1]: https://stackoverflow.com/questions/60525910/powershell-truncating-sql-query-output?noredirect=1#comment107077512_60525910
ribbit
  • 79
  • 2
  • 13