Powershell, Invoke-SqlCmd: How to get all the data from a column

Question

I have a table that has a column containing JSON data. It can be fairly large. I want to run a query to select the JSON data from a single row, using PowerShell. The command looks like this:

Invoke-Sqlcmd -ServerInstance xxxxxx 
  -Database xxxxxx -Username xxxxxx -Password xxxxxx 
  -Query 'select [data] from jsontable where versionid=1922' 
  -MaxCharLength 700000 | Out-File .\my.json

The command works, but the result is only a small portion of the whole. e.g. in the output file I see:

data                                                                                                                                                                                                                              
----                                                                                                                                                                                                                              
{"$type":"System.Collections.Generic.List`1[[TBSM.Vision.FpFtp.Common.Domain.Data.FeedDerivedAttributeRuleData, TBSM.Vision.FpFtp.Common.Domain.Data]], mscorlib","$values":[{"$type":"TBSM.Vision.FpFtp.Common.Domain.Data.Fee...

but the data actually begins:

{"$type":"System.Collections.Generic.List`1[[TBSM.Vision.FpFtp.Common.Domain.Data.FeedDerivedAttributeRuleData, TBSM.Vision.FpFtp.Common.Domain.Data]], mscorlib","$values":[{"$type":"TBSM.Vision.FpFtp.Common.Domain.Data.FeedDerivedAttributeRuleData, TBSM.Vision.FpFtp.Common.Domain.Data","FeedCode":"All","id":"a513ede8-d520-77b1-65f8-6377a24fdd83","mappingRules":{"$type":"System.Collections.Generic.List`1[[TBSM.Vision.FpFtp.Common.Domain.Data.DerivedAttributeRuleDataCollection, TBSM.Vision.FpFtp.Common.Domain.Data]], mscorlib","$values":[{"$type":"TBSM.Vision.FpFtp.Common.Domain.Data.DerivedAttributeRuleDataCollection, TBSM.Vision.FpFtp.Common.Domain.Data","Rule Sequence":27,"classification":"Default","id":"3ad4c21c-7e69-e1ce-473f-d477767054ec","mappingRules":{"$type":"System.Collections.Generic.List`1[[TBSM.Vision.FpFtp.Common.Domain.Data.RuleData, TBSM.Vision.FpFtp.Common.Domain.Data]], mscorlib","$values":[

and goes on from there.

I specified MaxCharLength as you can see. How can I stop the truncation and get all of my JSON data

Try this at the end of the script -> Select-Object date | format-list — Mahmood Shehab, Aug 22 '19 at 19:43
I think you’re piping a DataRow or DataTable into Out-File and it’s serializing *that* into your file. Try something like “$result = Invoke-SqlCmd ...; $result.Rows[0][“data”].Value | out-file .\my.json” instead and it should just write your data. I’ll check when I get to a computer tomorrow. — mclayton, Aug 22 '19 at 20:13
as @mclayton said , after getting the command as a variable try [ Format-List -InputObject $result] — Mahmood Shehab, Aug 22 '19 at 20:23
Try `-MaxCharLength 700000 | Select -Expand Data | Out-File .\my.json` — TheMadTechnician, Aug 22 '19 at 23:46

mclayton · Accepted Answer · 2019-08-24T20:23:54.887

As per my comment above, Invoke-Sqlcmd is returning a System.Data.DataRow and Out-File is serializing (and truncating) that to the file, not the raw string in your [data] column.

As a simple repro, this script

PS> Invoke-Sqlcmd -ServerInstance ".\sqlexpress" -query "SELECT '{...my json...}' AS data" | out-file "c:\temp\temp.txt"

writes this content to temp.txt:


data 
---- 
{...my json...}

And just to prove the return value is a DataRow:

PS> $result = Invoke-Sqlcmd -ServerInstance ".\sqlexpress" -query "SELECT '{...my json...}' AS data"
PS> write-host $result.GetType().FullName
System.Data.DataRow

If you want to write the value from the [data] column in your result set you'll need to extract that from the result of Invoke-Sqlcmd:

PS> $result = Invoke-Sqlcmd -ServerInstance ".\sqlexpress" -query "SELECT '{...my json...}' AS data"
PS> $value = $result["data"]
PS> $value | out-file "c:\temp\temp.txt"

and now the output file contains:

{...my json...}

Note that if there were more than one row in the result set it would be an Object[] array.

PS> $result = Invoke-Sqlcmd -ServerInstance ".\sqlexpress" -query "SELECT '{...my json...}' AS data UNION SELECT '{...my other json...}' AS data"
PS> write-host $result.GetType().FullName
System.Object[]

and you'd need to specify which row to write out (e.g. [0]):

PS> $result = Invoke-Sqlcmd -ServerInstance ".\sqlexpress" -query "SELECT '{...my json...}' AS data UNION SELECT '{...my other json...}' AS data"
PS> $value = $result[0]["data"]
PS> $value | out-file "c:\temp\temp.txt"

Hope this helps...

very interesting and useful! Interestingly, I found that the result is longer than expected and longer than reported by SQL Server. When I upped the maxlength to 4M, I got it all. — user1443098, Aug 26 '19 at 13:32

Powershell, Invoke-SqlCmd: How to get all the data from a column

1 Answers1