title: C# .Net SQL Server 2012 - insert file - System.Data.SqlClient.SqlException
body: <p>I am attempting to insert a file into a sql file stream. The file I have been using in my tests is only 87 KB.</p>
  
  <p>I have a SQL Server 2012 Enteprise instance running on a Windows 2012 R2 Server and a web server (2012 R2). The app running on the web serve hundreds of transactions / minute with the SQL Server with no issues.</p>
  
  <p>Every time I attempt to execute a stored procedure that accepts a <code>varbinary(max)</code> parameter I get this error:</p>
  
  <blockquote>
    <p>System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)</p>
    
    <p>System.ComponentModel.Win32Exception: An existing connection was forcibly closed by the remote host</p>
  </blockquote>
  
  <p>I was able to avoid the error when I removed to <code>varbinary(max)</code> from the stored procedure.</p>
  
  <p>This is why I can come to the conclusion that this is were my issue is.</p>
  
  <p>I get the same issue attempting to convert the <code>varbinary</code> to a base64 <code>varchar(max)</code></p>
  
  <p>Code samples - this is an attempt to call the stored procedure synchronously.</p>
  
  <pre><code>//
  //This is an attempt to call the stored procedure synchronously.
  public static void InsertFile1(string ProjectKey, string FileName, string ClientID, string FileType, Byte[] File, int FileCategoryId)
  {
      try
      {
          SqlConnection conn = null;
          SqlDataReader rdr = null;
  
          try
          {
              conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
              conn.Open();
              SqlCommand cmd = new SqlCommand("SPROC", conn);
              cmd.CommandType = CommandType.StoredProcedure;
  
              cmd.Parameters.Add("@ProjectKey", SqlDbType.VarChar, 30).Value = ProjectKey;
              cmd.Parameters.Add("@FileName", SqlDbType.VarChar, 300).Value = FileName;
              cmd.Parameters.Add("@ClientID", SqlDbType.VarChar, 300).Value = ClientID;
              cmd.Parameters.Add("@FileType", SqlDbType.VarChar, 300).Value = FileType;
              cmd.Parameters.Add("@File", SqlDbType.VarBinary, -1).Value = File;
              cmd.Parameters.Add("@FileCategoryId", SqlDbType.Int, 5).Value = FileCategoryId;
  
              rdr = cmd.ExecuteReader();
          }
          catch (Exception ex)
          {
              throw new Exception("InsertClientFileVoid3", ex);
          }
          finally
          {
              if (conn != null)
              {
                  conn.Close();
              }
  
              if (rdr != null)
              {
                  rdr.Close();
              }
          }
      }
      catch (Exception ex)
      {
          throw new Exception("InsertClientFile", ex);
      }
  }
  </code></pre>
  
  <p>This is an attempt to call the stored procedure asynchronously.</p>
  
  <pre><code>//
  //This is an attempt to call the stored procedure asynchronously.
  public static void InsertFile2(string ProjectKey, string FileName, string ClientID, string FileType, Byte[] File, int FileCategoryId)
  {
      try
      {
          E2EStream(CancellationToken.None,  ProjectKey,  FileName,  ClientID,  FileType, File, FileCategoryId).Wait();
      }
      catch (Exception ex)
      {
          throw new Exception("InsertFile", ex);
      }
  }
  
  //
  //Function that does the async call to SQL Server
  private static async Task E2EStream(CancellationToken cancellationToken, string ProjectKey, string FileName, string ClientID, string FileType, Byte[] File, int FileCategoryId)
  {
      using (SqlConnection writeConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ETLWarehouseSalesforce"].ToString()))
      {
          Task openWriteConn = writeConn.OpenAsync(cancellationToken);
          await Task.WhenAll(openWriteConn);
  
          using (SqlCommand writeCmd = new SqlCommand("[Publishing].[Insert_File]", writeConn))
          {
              writeCmd.CommandType = CommandType.StoredProcedure;
  
              writeCmd.Parameters.Add("@ProjectKey", SqlDbType.VarChar, 30).Value = ProjectKey;
              writeCmd.Parameters.Add("@FileName", SqlDbType.VarChar, 300).Value = FileName;
              writeCmd.Parameters.Add("@ClientID", SqlDbType.VarChar, 300).Value = ClientID;
              writeCmd.Parameters.Add("@FileType", SqlDbType.VarChar, 300).Value = FileType;
              writeCmd.Parameters.Add("@File", SqlDbType.VarBinary, -1).Value = File;
              writeCmd.Parameters.Add("@FileCategoryId", SqlDbType.Int, 5).Value = FileCategoryId; 
  
              await writeCmd.ExecuteNonQueryAsync(cancellationToken);
          }
      }
  }
  </code></pre>
  
  <p>This is the stored procedure that is called from C#</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[InsertFile]
      @ProjectKey varchar(30) = ''
      ,@FileName varchar(300) = ''
      ,@ClientID varchar(300) = ''
      ,@FileType varchar(300) = ''
      ,@File varbinary(MAX) = null
      ,@FileCategoryId int = 1
  AS
  BEGIN
  
      print 'Stuff will happen here';
  
  END
  </code></pre>
  
  <p>Calling this version of the procedure works.</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[InsertFile]
      @ProjectKey varchar(30) = ''
      ,@FileName varchar(300) = ''
      ,@ClientID varchar(300) = ''
      ,@FileType varchar(300) = ''
      --,@File varbinary(MAX) = null
      ,@FileCategoryId int = 1
  AS
  BEGIN
      print 'Stuff will happen here';
  END
  </code></pre>
  
  <p>Work around stored procedure on DB-A</p>
  
  <pre><code>USE [DB-A]
  
  ALTER PROCEDURE [Publishing].[Insert_File]
      @ProjectKey varchar(30) = ''
      ,@FileName varchar(300) = ''
      ,@ClientID varchar(300) = ''
      ,@FileType varchar(300) = ''
      ,@File varbinary(MAX) = null
      ,@FileCategoryId int = 1
  AS
  BEGIN
      EXEC [DB-B].[Publishing].[Insert_File] @ProjectKey, @FileName, @ClientID, @FileType, @File, @FileCategoryId;
  END
  </code></pre>
  
  <p>Error message:</p>
  
  <blockquote>
    <p>System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. 
    (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)</p>
    
    <p>System.ComponentModel.Win32Exception: An existing connection was forcibly closed by the remote host</p>
  </blockquote>