Invoke-SqlCmd shortens results-CodePudding

I am working on a script that would help be extract some data from columns into text files. It concatenates several columns into single one.

Problem I am having is that when I use powershell Invoke-SqlCmd the results are shortened with ending being cut off like this

Some example text thats fine
This text is too long so it will be cu...
This is also fine

I tried to add -MaxCharLength parameter, but it does not help.

Just to confirm, results in SSMS are showing up fine with full length of generated columns.

I check the length of problematic column and it has 226 characters. Is there any workaround for that?

EDIT: Same thing happens when I do Out-File, even with extra -Width, but interestingly it does not happen when I do Export-Csv

Invoke-Sqlcmd $CreateScripts -ServerInstance $serverInstace -Database $dbName | Export-Csv C:\Users\glueTorndao\Desktop\test.csv

If someone would like to replicate SQL command is following:

SELECT
'IF EXISTS (SELECT * FROM sys.database_principals WHERE name = '''   USR.name    ''') BEGIN ' 
         
    CASE 
    WHEN DBP.state <> 'W' 
    THEN DBP.state_desc 
    ELSE 'GRANT' 
    END
          SPACE(1)   DBP.permission_name   SPACE(1)   'ON '   QUOTENAME(USER_NAME(OBJ.schema_id))   '.'   QUOTENAME(OBJ.name) 
          CASE 
            WHEN CL.column_id IS NULL 
            THEN SPACE(0) 
            ELSE '('   QUOTENAME(CL.name)   ')' 
            END
          SPACE(1)   'TO'   SPACE(1)   QUOTENAME(USR.name) COLLATE database_default
          CASE WHEN DBP.state <> 'W' THEN SPACE(0) ELSE SPACE(1)   'WITH GRANT OPTION' END   ' END ;' as '--ObjectGrants'
FROM    sys.database_permissions AS DBP
        INNER JOIN  sys.objects AS OBJ  ON DBP.major_id = OBJ.[object_id]
        INNER JOIN  sys.database_principals AS USR  ON DBP.grantee_principal_id = USR.principal_id
        LEFT JOIN   sys.columns AS CL   ON CL.column_id = DBP.minor_id AND CL.[object_id] = DBP.major_id
ORDER BY DBP.permission_name ASC, DBP.state_desc ASC

And problem is caused by single Stored Procedure which has name of 95 characters and one user is having Execute permissions on said procedure.

Powershell command looks like this now:

Invoke-Sqlcmd $createScript -ServerInstance $serverInstace -Database $dbName -MaxCharLength 10000 | Format-Table | Out-File C:\Users\GlueTornado\Desktop\test5.txt -Width 10000

CodePudding user response：

The default output format will only utilize one row of text per result and will only fill the width of the console and no more (no horizontal scrolling), so space is finite. A list formatted output on the otherhand will wrap text onto multiple lines if necessary.

Try piping the output to format-list, for example:

Invoke-SqlCmd "SELECT Description FROM MyTable" | Format-List