PowerShell – executing SQL query – resolving issue – column not returning all characters

Hi All,

Greetings

Today new issue and solution

Background

• I am working on PowerShell script in which I am querying to SQL server
• I am getting the result
• Yet one of the SQL column of type “Text” is not returning all the characters.
• Return string value is truncated
• The column includes the big HTML string which I read and create a page in SharePoint
• I am using “Invoke-Sqlcmd” PowerShell CMDLET to query SQL server from PowerShell
  • We have an article – PowerShell : How to execute SQL queries – https://knowledge-junction.in/2026/01/31/powershell-how-to-execute-sql-queries/

PS C:\> $SiteURL = "https://knowledgejunction1.sharepoint.com/sites/prashamdemo/"
PS C:\> $ClientID = "my client app id"
PS C:\> Connect-PnPOnline -Url $SiteURL -Interactive -WarningAction Ignore -ClientId $ClientID -Force
PS C:\> $sqllink = Invoke-Sqlcmd -Query "SELECT * FROM dbo.mytable" -ServerInstance "myserver" -TrustServerCertificate
PS C:\> $sqllink.body

Value of "$sqllink.body" is returning the string but not complete value. It cut through.

Solution

• After a bit of research, I found that the Invoke-Sqlcmd cmdlet has a default maximum character length. This length is 4000 for character columns. This limit causes truncation for longer strings.
• We can override this with the -MaxCharLength parameter

PS C:\> $sqllink = Invoke-Sqlcmd -Query "SELECT * FROM dbo.mytable" -ServerInstance "myserver" -MaxCharLength ([int]::MaxValue) -TrustServerCertificate

• As we used “-MaxCharLength ([int]::MaxValue)” parameter to our CMDLET, column returned the complete string without truncating

References

• How to execute SQL queries – https://knowledge-junction.in/2026/01/31/powershell-how-to-execute-sql-queries/

Thanks for reading!!! Please feel free to discuss in case any questions / suggestions / thoughts !!!

HAVE A GREAT TIME AHEAD !!! LIFE IS BEAUTIFUL 🙂