title: Calling stored procedure from stored procedure intermittently fails
body: <p>I have a report that calls a stored procedure which in turn calls a stored procedure. </p>
  
  <p>The parent sp is one that aggregates expenses vs revenue over a multi-year contract.</p>
  
  <p>The child sp returns raw data for a single budget year.  </p>
  
  <p>This works beautifully most of the time. However, sometimes we get an error thrown that the first field in the child sp cannot be found. This field comes from a simple select statement.</p>
  
  <p>The problem appears to occur at times of the day when there is a heavier load on the server.</p>
  
  <p>Here is the portion of the parent sp where the child sp is called:</p>
  
  <pre><code>declare c_by cursor for (select ccbyId from #budgetYears)
  open c_by
  fetch next from c_by into @c_byId
  while @@FETCH_STATUS=0
  Begin
  
  begin try
  
      insert into #fullDataSet 
      EXEC [dbo].[rptByToDate]
          @ClientContractBudgetYear   = @c_byId 
          ,@userID                    = @userID 
          ,@DBName                    = @DBName 
  
  
  end try
  begin catch
  
      IF @@TRANCOUNT &gt; 0
      ROLLBACK
  
      DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
      SELECT @ErrMsg = ERROR_MESSAGE(),
           @ErrSeverity = ERROR_SEVERITY()
  
      RAISERROR(@ErrMsg, @ErrSeverity, 1)
  
  end catch
  
  fetch next from c_by into @c_byId
  End
  close c_by
  deallocate c_by
  </code></pre>
  
  <p>What am i missing?</p>