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-Sqlcmdcmdlet 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
-MaxCharLengthparameter
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 🙂
