Powershell Invoke-Sqlcmd Output Contains Dots '...' and Random Header

Question

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

Try adding " | Format-Table" or some other formatting cmdlet: learn.microsoft.com/en-us/powershell/scripting/…. — jmoon
– jmoon, Commented Aug 11, 2017 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
– beeker, Commented Aug 11, 2017 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
– jmoon, Commented Aug 11, 2017 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
– Matt, Commented Aug 11, 2017 at 18:17

Community · Accepted Answer · 2020-06-20 09:12:55Z

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 11, 2017 at 18:26

Laughing Vergil

3,7661 gold badge17 silver badges28 bronze badges

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

Comments

ribbit · Accepted Answer · 2020-03-04 15:47:12Z

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

answered Mar 4, 2020 at 15:47

ribbit

793 silver badges16 bronze badges

Collectives™ on Stack Overflow

Powershell Invoke-Sqlcmd Output Contains Dots '...' and Random Header

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related