1

One of our tables has very very long records in a particular column. These queries are used infrequently, but are necessary.

I have been using sqlcmd utility in bash like so:

-- getfile.sql

SET NOCOUNT ON;

-- Disable message for next USE command
:setvar SQLCMDERRORLEVEL 1
USE myDb;
SELECT myField
FROM myTable 
GO

-- Reenable
:setvar SQLCMDERRORLEVEL 0

# getfile.sh

sqlcmd \
    -S $server \
    -U $username \
    -P $password \
    -y 0 \
    -I getfile.sql > filename.txt

-y 0 being the trick here that allows me to get my text without it being truncated.

So now I need to convert this to PowerShell. 

Invoke-Sqlcmd `
    -ServerInstance $server `
    -Username $username `
    -Password $password `
    -InputFile 'get_clientfile.sql' `
    -Variable $StringArray `
    | Out-File 'filename.txt'

But sadly my output is limited to:

myField
-----------------
BEGIN…

I also tried using MaxCharLength and MaxBinaryLength to no avail.

So far I have not discovered a way to prevent it from truncating output.

Also notice it is not respecting my sqlcmd commands like :setvar. One way around this, what -y really does (I think) might be to set the SQLCMDMAXVARTYPEWIDTH to 0. But I can't find a way to set variables like this when using Invoke-SqlCmd.

Any help much appreciated!

Frankie
  • 11,508
  • 5
  • 53
  • 60
  • I hope the [linked answer](https://stackoverflow.com/a/38576389/45375) provides a solution; let us know if not. – mklement0 Mar 13 '20 at 21:08
  • 1
    I was able to do it by adding this to my query `| Format-Table -Wrap -HideTableHeaders`. Thanks! – Frankie Mar 14 '20 at 15:20

0 Answers0