SQL Server输出txt时Source字段内容截断问题求助

问题描述 #

SQL Server脚本查询结果正常，但通过PowerShell输出到TXT文件时，Source字段内容末尾出现省略号，无法完整显示。

解决方法及建议 #

方法1：调整Invoke-Sqlcmd的输出参数 #

Invoke-Sqlcmd默认会截断过长文本，可通过以下参数修改：

• -MaxCharLength：指定单个字段的最大显示长度，设为-1可适配MAX类型字段的完整内容
• -Width：控制输出的整体宽度，避免因宽度不足导致的截断

修改后的PowerShell命令示例：

Invoke-Sqlcmd -Query $query2 -ServerInstance $Global:Server -Database $Global:Database -username $SQLusername -password $SQLpass -ErrorAction Stop -MaxCharLength -1 -Width 2000 | Out-File -filePath $Global:QueryLogPath

方法2：直接用SQL导出到文件 #

绕开PowerShell的格式限制，在SQL中通过bcp命令直接导出：

-- 执行前需确保xp_cmdshell已启用且有权限
EXEC xp_cmdshell 'bcp "SELECT ''Adding: ''+XX.Source+'' ''+XX.Type AS Result FROM (Select  * from (SELECT Source, Type FROM Temp_Hold_Inv Thi WHERE NOT EXISTS (SELECT 1 FROM Hold_Inv Hi WHERE  Thi.Source = Hi.Source) AND Thi.Source <> ''NOT FOUND'' AND LEN(TRIM(ISNULL(Thi.Source, ''''))) > 0 UNION ALL SELECT  Source, Type FROM Temp_Hold_Inv Thi WHERE NOT EXISTS (SELECT 1 FROM Hold_Inv Hi WHERE  Thi.Source = Hi.Source) AND Thi.Source = ''NOT FOUND'' UNION ALL SELECT  Source, Type FROM Temp_Hold_Inv Thi WHERE NOT EXISTS (SELECT 1 FROM Hold_Inv Hi WHERE  Thi.Source = Hi.Source) AND LEN(TRIM(ISNULL(Thi.Source, ''''))) = 0) T1 except select * from (SELECT Source, [Type] FROM Daily_Proc) T2) XX" queryout "C:\指定路径\QueryLog.txt" -S 你的服务器名 -d 你的数据库名 -U 用户名 -P 密码 -c -t " " -r "\n"'

方法3：PowerShell中直接提取字段文本 #

将查询结果转换为纯字符串数组，避免表格格式的截断：

$results = Invoke-Sqlcmd -Query $query2 -ServerInstance $Global:Server -Database $Global:Database -username $SQLusername -password $SQLpass -ErrorAction Stop
$output = $results | ForEach-Object { $_.Result }
$output | Out-File -filePath $Global:QueryLogPath

相关脚本 #

原SQL查询脚本 #

-- Printing Adding Records which are exists in Temp_Hold_Inv Table and Not exists in Hold_Inv Table

SELECT 'Adding: '+XX.Source+' '+XX.Type AS Result
FROM (
    Select  * from (
        SELECT Source, Type
        FROM Temp_Hold_Inv Thi
        WHERE NOT EXISTS (SELECT 1 FROM Hold_Inv Hi WHERE  Thi.Source = Hi.Source)
        AND Thi.Source <> 'NOT FOUND'
        AND LEN(TRIM(ISNULL(Thi.Source, ''))) > 0
        UNION ALL
        SELECT  Source, Type 
        FROM Temp_Hold_Inv Thi
        WHERE NOT EXISTS (SELECT 1 FROM Hold_Inv Hi WHERE  Thi.Source = Hi.Source)
        AND Thi.Source = 'NOT FOUND'
        UNION ALL
        SELECT  Source, Type 
        FROM Temp_Hold_Inv Thi
        WHERE NOT EXISTS (SELECT 1 FROM Hold_Inv Hi WHERE  Thi.Source = Hi.Source)
        AND LEN(TRIM(ISNULL(Thi.Source, ''))) = 0) T1
    except select * from (SELECT Source, [Type] FROM Daily_Proc) T2
) XX
GO

原PowerShell脚本 #

Invoke-Sqlcmd -Query $query2 -ServerInstance $Global:Server -Database $Global:Database -username $SQLusername -password $SQLpass -ErrorAction Stop | Out-File -filePath $Global:QueryLogPath

内容的提问来源于stack exchange，提问作者aasenomad