0

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.

  1. The table:
    CREATE TABLE [my].[table](
    [datafile] [varbinary](max) FILESTREAM  NULL,
    [checksum] [varchar](100) NULL,
    [build] [varchar](50) NULL)
    
  2. 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
    
  3. 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.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Purclot
  • 483
  • 7
  • 22
  • 3
    [2G _is_ the maximum size of the `VARBINARY` type](https://learn.microsoft.com/en-us/sql/t-sql/data-types/binary-and-varbinary-transact-sql?view=sql-server-ver15#:~:text=varbinary%20%5B%20(%20n%20%7C%20max),the%20data%20entered%20%2B%202%20bytes.) – Mathias R. Jessen Sep 22 '21 at 13:09
  • 1
    In truth, it's generally not a great idea to store the items in the SQL Server anyway; store the data about the files, including their paths, in the database and store the files in the file system. Use the systems that are good at what they do for that purpose; Database for Data, File System for Files. – Thom A Sep 22 '21 at 13:14
  • @MathiasR.Jessen - [yes, but also no](https://stackoverflow.com/questions/7611394/maximum-size-of-a-varcharmax-variable) – Damien_The_Unbeliever Sep 22 '21 at 13:22
  • @Damien_The_Unbeliever My impression was (and I might be completely wrong here), was that you needed to add the FILESTREAM feature to support column values beyond that 2GB threshold – Mathias R. Jessen Sep 22 '21 at 13:24
  • as you can see, I activated filestream for the database and in the table as well (see the table definition). so there is no solution for my problem? I'll try to re-design the sql table, but for now..? – Purclot Sep 22 '21 at 13:24

0 Answers0