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

Answer 1

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