0

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"
ribbit
  • 79
  • 2
  • 13
  • 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 Mar 04 '20 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 Mar 04 '20 at 12:29
  • @AlwaysLearning - Yes the binary column is always > 9999 and that's where I seem to get pinned. – ribbit Mar 04 '20 at 12:43
  • @Larnu - ah didn't think of that, will try it now thank you. – ribbit Mar 04 '20 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 Mar 04 '20 at 13:05

2 Answers2

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
ribbit
  • 79
  • 2
  • 13
0

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

Ken King
  • 11
  • 3