title: SQL Server: Intermittent Invalid object name 'dbo.computed_view1'. Could not use view or function 'dbo.view2' because of binding errors
body: <p>I'm using SQL Server 2008 R2. </p>
  
  <p>I have a view; let's call it view1. This view is complex and slow. It cannot be made into an indexed view because it uses left joins and various other trickery. As such, we created a stored procedure which basically: </p>
  
  <ol>
  <li>obtains an exclusive lock</li>
  <li>selects * into computed_view1_tmp from view1;  (slow)</li>
  <li>creates indexes on the above computed table (slow)</li>
  <li>renames computed_view1 to computed_view1_todelete; and does the same for its indexes (assumed fast)</li>
  <li>renames computed_view1_tmp to computed_view1; and does the same for its indexes (assumed fast)</li>
  <li>drops the table computed_view1_todelete (slow) </li>
  <li>releases the lock. </li>
  </ol>
  
  <p>We run this procedure when we know we're changing the data in our web application. We then have other views, such as view2 using computed_view1 instead of view1. </p>
  
  <p>Once in a while, we get:</p>
  
  <blockquote>
    <p>Invalid object name 'dbo.computed_view1'. Could not use view or
    function 'dbo.view2 because of binding errors.</p>
  </blockquote>
  
  <p>I assume this is because we're trying to access dbo.computed_view1 at the same time as it's being renamed. I assume this is a very short period, but the frequency I am seeing this error in my logs makes me wonder if something else might be at play. I'm getting the error many times per day on a site with about a dozen users active throughout the day. </p>
  
  <p>In development, this procedure takes about five seconds given the amount of data in the view. Renaming is instantaneous. In production, it must be taking longer but I don't understand why.  I once saw the procedure fail to obtain the exclusive lock within 90 seconds. </p>
  
  <p>Any thoughts on how to fix or a better solution? </p>
  
  <p>Edit: Extra notes on my locking - maybe I'm not doing this right:</p>
  
  <pre><code>BEGIN TRANSACTION
  
  DECLARE @result int
  EXEC @result = sp_getapplock @Resource = 'lock_computed_view1', @LockMode = 'Exclusive',  @LockTimeout = 90
  IF @result NOT IN ( 0, 1 )   -- Only successful return codes
      BEGIN
        PRINT @result
        RAISERROR ( 'Lock failed to acquire...', 16, 1 )
      END 
      ELSE
      BEGIN
             // rest of the magic 
          END
  
  EXEC @result = sp_releaseapplock @Resource = 'lock_computed_view1'  
  COMMIT TRANSACTION
  </code></pre>