Powershell truncating SQL Query Output

Question

I'm running the following query from a powershell command which unfortunately truncates or adds dots to the end of the result string. I had adjusted the screen buffer size for powershell i.e. right click on powershell Properties>Layout>Screen Buffer Size> width and height set to 9999 (max). It seems like the output is limited to the powershell screen buffersize and I can't seem to get around this one. Any help is appreciated please.

Invoke-Sqlcmd -ServerInstance myrds.com -Database mydb -Username dbuser -Password passord -maxcharlength 80000 -Query "SELECT CAST(BINARYCOL AS VARCHAR(MAX)) FROM OLTP_TABLE WHERE ID=123123 AND COMPANYID=123" | Out-File -FilePath "C:\Users\USER\Documents\ps_scripts\res3.txt"

If you want to output the result of a query to a file, why not use bcp instead? That's designed to have the output from a command saved to a file. — Thom A
– Thom A ♦, Commented Mar 4, 2020 at 12:21
Is it safe to assume that select datalength(binarycol) from oltp_table where id=123123 and companyid=123 returns a number greater than 9999? — AlwaysLearning
– AlwaysLearning, Commented Mar 4, 2020 at 12:29
@AlwaysLearning - Yes the binary column is always > 9999 and that's where I seem to get pinned. — ribbit
– ribbit, Commented Mar 4, 2020 at 12:43
@Larnu - ah didn't think of that, will try it now thank you. — ribbit
– ribbit, Commented Mar 4, 2020 at 12:44
If this is truly a case of truncating due to screen sizing, then you can pipe your data to Format-List. However, if you are outputting a collection of items within a property, the default shell is limited to 4 items before truncating with .... To fix this, you need to change $formatenumerationlimit = -1 (for unlimited) or some other appropriate number. — AdminOfThings
– AdminOfThings, Commented Mar 4, 2020 at 13:05

Ken King · Accepted Answer · 2022-07-14 22:30:14Z

1

The default output size it 4000 characters. 8000 characters can be contained in a text type field. If your using Invoke-SqlCmd, you can do this:

Invoke-Sqlcmd -MaxCharLength 8000

answered Jul 14, 2022 at 22:30

Ken King

195 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ribbit · Accepted Answer · 2020-03-04 14:53:27Z

0

Thanks to everyone who's replied. I had formulated a bcp line below to get me the non-truncated result from varbinary to string.

bcp "SELECT CAST(BINARYCOL AS VARCHAR(MAX)) FROM OLTP_TABLE WHERE ID=123123 AND COMPANYID=123" 
queryout "C:\Users\USER\Documents\ps_scripts\res.txt" -c -S myserver.db.com  -U admin -P password

answered Mar 4, 2020 at 14:53

ribbit

793 silver badges16 bronze badges

Collectives™ on Stack Overflow

Powershell truncating SQL Query Output

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related