title: SQL Server connection dropped for specific stored procedures
body: <p>When executing SQL Statements, I sometimes get errors</p>
  
  <p><strong>Client:</strong> </p>
  
  <blockquote>
    <p>"An exception occurred while executing a Transact-SQL statement or
    batch. A transport-level error has occurred when receiving results
    from the server. (provider: TCP Provider, error: 0 -The semaphore
    timeout period has expired)"</p>
  </blockquote>
  
  <p><strong>SQL Server:</strong> </p>
  
  <blockquote>
    <p>"2014-02-09 11:02:31.81 Server      A fatal error occurred while
    reading the input stream from the network. The session will be
    terminated (input error: 64, output error: 0)."</p>
  </blockquote>
  
  <p>I have managed to find one piece of SQL that <em>consistently</em> fails, from my generated database scripts, which I run when deploying a new version (though I see the error intermittently elsewhere too):</p>
  
  <pre><code>/*************************************************/
  /* [dbo].gspFolderDataCache_SEARCH */
  /*************************************************/
  IF EXISTS (SELECT * FROM sys.objects WHERE object_id =     OBJECT_ID(N'[dbo].gspFolderDataCache_SEARCH') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].gspFolderDataCache_SEARCH
  GO
  
  CREATE PROCEDURE [dbo].gspFolderDataCache_SEARCH (@FolderId AS [uniqueidentifier],   @PerformanceFolderId AS [uniqueidentifier], @ShowFolderId AS [uniqueidentifier], @SpaceFolderId AS [uniqueidentifier], @VenueFolderId AS [uniqueidentifier])
  AS
  
  SELECT
  FolderId, PerformanceFolderId, ShowFolderId, SpaceFolderId, VenueFolderId
  FROM
  [dbo].[FolderDataCache]
  WHERE
  ([FolderId] = @FolderId OR @FolderId IS NULL)
   AND ([PerformanceFolderId] = @PerformanceFolderId OR @PerformanceFolderId IS NULL)
   AND ([ShowFolderId] = @ShowFolderId OR @ShowFolderId IS NULL)
   AND ([SpaceFolderId] = @SpaceFolderId OR @SpaceFolderId IS NULL)
   AND ([VenueFolderId] = @VenueFolderId OR @VenueFolderId IS NULL)
  
  GO
  </code></pre>
  
  <p>whilst this, almost identical script, works every time:</p>
  
  <pre><code>/*************************************************/
  /* [dbo].gspDiscounts_SEARCH */
  /*************************************************/
  IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].gspDiscounts_SEARCH') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].gspDiscounts_SEARCH
  GO
  
  CREATE PROCEDURE [dbo].gspDiscounts_SEARCH (@DiscountId AS [uniqueidentifier], @DiscountName AS [nvarchar](50), @DiscountDescription AS [nvarchar](max))
  AS
  
  SELECT
      DiscountId, DiscountName, DiscountDescription
  FROM
      [dbo].[Discounts]
  WHERE
      ([DiscountId] = @DiscountId OR @DiscountId IS NULL)
       AND ([DiscountName] LIKE @DiscountName OR @DiscountName IS NULL)
       AND ([DiscountDescription] LIKE @DiscountDescription OR @DiscountDescription IS NULL)
  
  GO
  </code></pre>
  
  <p>In this particular example, where the SQL is being executed by my database maintenance tool, I am using the SQL Server management objects to run the scripts, but the same error occurs in the main application which uses the Enterprise Library Data Access Block, so I don't think it's in the C# code.</p>
  
  <p>Both the SQL Server (SQL Server v2008 R2) and the client are on Amazon EC2 servers, inside the same VPC. When the webserver was outside the VPC and running Windows Server 2008, this problem didn't occur. Both servers are now on Windows Server 2012</p>
  
  <p>Because the statement that fails is so similar to the one that works, and because it shows as a network error on both the client and the server, I thought it might be something in between... I've turned the Windows Firewall off on both servers. That hasn't helped. </p>
  
  <p>What on earth can be going on?</p>