my setup is: a SQL Server (2016), a Table (Filestream active) with a varbinary(max) column containing some ZIP-Files. There is a stored procedure with an output parameter returning the ZIP-File. I'm using a PowerShell-Function that fires this stored procedure returning the ZIP-File. As long, as the ZIP file is <= 2 GB, there is no problem. All bigger files will not be returned. When I try to debug the script, there is even no Error value. I know the problem is the SQLDBType mapping: [system.data.SqlDbType]::varbinary,-1). Thanks for your help.
- The table:
CREATE TABLE [my].[table]( [datafile] [varbinary](max) FILESTREAM NULL, [checksum] [varchar](100) NULL, [build] [varchar](50) NULL)
- the stored procedure:
create PROCEDURE [my].[getZIPFile] ( @ZIPFile varbinary(max) OUTPUT, @checkSum varchar(100) OUTPUT, @aktScriptVersion decimal(4,2) output ) AS BEGIN IF ..... = 'SQLBinary' BEGIN SELECT @ZIPFile = [DataFile], @checkSum = [checksum], @aktScriptVersion = version_nr FROM [deployment].[softwarerepository] END
- PowerShell-Function
Function Get-ZIPFile { param( .... some parameter ) $conn = New-Object System.Data.SqlClient.SqlConnection $conn.ConnectionString = "Server=myServer;Database=myDB;Integrated Security=no;User=SQL_user;Password=xxxxx" $conn.Open() | out-null $cmd = new-Object System.Data.SqlClient.SqlCommand #Proz $cmd.Connection = $conn $cmd.CommandType = [System.Data.CommandType]::StoredProcedure $cmd.CommandText = "deployment.getZIPFile" # ProzedurName #### Proz-Parameter # $cmd.Parameters.Add("@ZIPFile",[system.data.SqlDbType]::varbinary,-1) | out-Null # HIER MUST BE THE ERROR $cmd.Parameters["@ZIPFile"].Value = [System.DBNull]::Value $cmd.Parameters['@ZIPFile'].Direction = [system.data.ParameterDirection]::Output # $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 #kranke Scheisse! sonst ist die Ausgabe: 3 und nicht 3,01 !!! $cmd.Parameters['@aktScriptVersion'].Scale=2 $i = $cmd.ExecuteNonQuery() # DEBUG: NO ERROR! $ausgabe = [pscustomobject]@{ zipFile = "" checkSum = "" aktScriptVersion = 0 } if($i -gt 0) { $ausgabe.zipFile = $cmd.Parameters["@ZIPFile"].Value $ausgabe.checkSum = $cmd.Parameters["@checkSum"].Value $ausgabe.aktScriptVersion = $cmd.Parameters["@aktScriptVersion"].Value } $cmd.Dispose() $conn.Close() $conn.Dispose() return $ausgabe }
As I said: when the zip file <= 2 GB it works just fine, otherwise there is no Error and I get nothing. Thanks for your help.