Try to solve Invoke-SqlCmd reduced output

_{Photo by Andres Siimon on Unsplash}

We use Invoke-SqlCmd to execute short sql scripts modified in git repository.

But once we have tried to select a large JSON column, and get the result truncated.

The popular answer is to add the following option

 -MaxCharLength 65534

but it did not work for us.

I find a workaround using Export-Csv.
If you are using Powershell 7, you can use -UseQuotes Never .

$files= git diff --name-status HEAD^ HEAD

...

if($line.Change -ne "D")
{
if($line.Path)
{
Write-Host $line.Path
$outputfile= $line.Path.Split('/')[1] -replace  ".sql", ".txt"
Write-Host "$(Build.ArtifactStagingDirectory)/$($outputfile)"
$result = Invoke-Sqlcmd -ServerInstance "$env:Server" -Database "$env:Db" -Username "$env:UserLogin" -Password "$env:UserPassword" -InputFile $line.Path 
$result | Export-Csv "$(Build.ArtifactStagingDirectory)/$($outputfile)"  -NoTypeInformation  -Delimiter "," -UseQuotes Never
$result

Sadly, the ouptut type was NVARCHAR(MAX), so it's still truncated.

Hope this help !