PowerShell调用SQL存储过程获取大于2GB varbinary(max)文件无返回问题

你的判断是准确的，问题出自[system.data.SqlDbType]::varbinary,-1作为输出参数的限制：System.Data.SqlClient组件对存储过程输出参数的varbinary(max)类型有硬编码的2GB大小上限，超过阈值的数据会被静默丢弃，不会触发报错，这就是2GB以上文件无返回的核心原因。
同时你当前的用法也浪费了Filestream的流式读写优势，将整个大文件一次性加载到内存中，既容易触发大小限制，也会带来极高的内存占用。

放弃用存储过程输出参数返回二进制内容，改为直接查询返回行，通过SqlDataReader的流接口读取文件，支持任意大小的Filestream文件，内存占用极低。

1. 修改存储过程 #

去掉@ZIPFile输出参数，直接返回查询结果：

create PROCEDURE [my].[getZIPFile]  (
@checkSum varchar(100) OUTPUT,
@aktScriptVersion decimal(4,2) output
) 
AS 
BEGIN
IF ..... = 'SQLBinary'
    BEGIN
        -- 直接返回二进制列，不再赋值给输出参数
        SELECT [DataFile]
        FROM [deployment].[softwarerepository]
        -- 同时给输出参数赋值
        SELECT @checkSum = [checksum], @aktScriptVersion = version_nr
        FROM [deployment].[softwarerepository]
    END
END

2. 修改PowerShell函数 #

改用ExecuteReader读取流，直接写入本地文件（也可根据需求写入内存流，大文件推荐直接落盘）：

Function Get-ZIPFile
{
    param(
    $outputPath, # 新增参数：ZIP文件输出路径
    .... 其他原有参数
    )

    $conn = New-Object System.Data.SqlClient.SqlConnection
    # 连接字符串增加MultipleActiveResultSets=True支持同时读取输出参数和结果集
    $conn.ConnectionString = "Server=myServer;Database=myDB;Integrated Security=no;User=SQL_user;Password=xxxxx;MultipleActiveResultSets=True"
    $conn.Open() | out-null
    $cmd = new-Object System.Data.SqlClient.SqlCommand 
    $cmd.Connection = $conn
    $cmd.CommandType = [System.Data.CommandType]::StoredProcedure
    $cmd.CommandText = "deployment.getZIPFile"
    #### 移除@ZIPFile输出参数配置
    $cmd.Parameters.Add("@checkSum",[system.data.SqlDbType]::Varchar,100) | out-Null
    $cmd.Parameters['@checkSum'].Direction = [system.data.ParameterDirection]::Output
    $cmd.Parameters.Add("@aktScriptVersion",[system.data.SqlDbType]::decimal) | out-Null
    $cmd.Parameters['@aktScriptVersion'].Direction = [system.data.ParameterDirection]::Output
    $cmd.Parameters['@aktScriptVersion'].Precision=18
    $cmd.Parameters['@aktScriptVersion'].Scale=2

    # 改用Reader读取结果集
    $reader = $cmd.ExecuteReader()
    $ausgabe = [pscustomobject]@{
        zipFileSavePath = $outputPath
        checkSum = ""
        aktScriptVersion = 0
    }
    if($reader.HasRows)
    {
        $reader.Read()
        # 获取二进制列的流，分段写入文件避免内存溢出
        $stream = $reader.GetStream(0)
        $fileStream = New-Object System.IO.FileStream($outputPath, [System.IO.FileMode]::Create, [System.IO.FileAccess]::Write)
        $buffer = New-Object byte[] 8192
        $readCount = 0
        while(($readCount = $stream.Read($buffer, 0, $buffer.Length)) -gt 0)
        {
            $fileStream.Write($buffer, 0, $readCount)
        }
        $fileStream.Flush()
        $fileStream.Close()
        $stream.Close()
    }
    $reader.Close()
    # 读取输出参数
    $ausgabe.checkSum = $cmd.Parameters["@checkSum"].Value
    $ausgabe.aktScriptVersion = $cmd.Parameters["@aktScriptVersion"].Value

    $cmd.Dispose()            
    $conn.Close() 
    $conn.Dispose()
    return $ausgabe
}

如果文件普遍超过10GB，还可以直接查询Filestream的文件路径和事务上下文，通过Win32 API直接读取Filestream的物理文件，性能更高，需要在存储过程中返回DataFile.PathName()和GET_FILESTREAM_TRANSACTION_CONTEXT()，PowerShell侧调用System.Data.SqlTypes.SqlFileStream读取即可。

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