title: TSQL error checking code not working
body: <p>I'm trying to add an alert to my log for a running insert of a view into a table when there is an error executing the query in the view.  When I run the view alone, I get an invalid input into SUBSTRING (the exact wording of the error I can't remember).  When I run it as part of my view -> table stored procedure, the error is ignored, then I have to go digging for the offending line and make an exception in the view's code to omit that line from the results (I know, it sounds kludge-y, but I'm doing data reduction on huge web-log files from a specialized webapp), but I digress.</p>
  
  <p>I've tried two different methods for trying to catch the error and neither are triggered in such a way to insert the row indicating an error in my execution result table (refresh_results).  I think I may be missing some fundamental - perhaps the errors are being encapsulated in come way.  If I can't detect the error, the only way to notice an error is if someone notices the number of entries into the table is low for a given period of time.</p>
  
  <pre><code>    SELECT @TransactionName = 'tname';
      BEGIN TRANSACTION @TransactionName;
      BEGIN TRY
        print 'tname ***In Try***';
        if exists (select name from sysobjects where name='tablename')
        begin
          drop table tablename;
        end
        select * into tablename 
        from opendatasource('SQLNCLI', 'Data Source=DATABASE;UID=####;password=####').dbo.viewname;
        COMMIT TRANSACTION @TransactionName;
      END TRY
      BEGIN CATCH 
        print 'tablename ***ERROR - check for SUBSTRING***';
        begin transaction
        set   @result_table = 'tablename ***ERROR - check for SUBSTRING***'
        select    @result_time = getdate(),
              @result_rows = count(logtime)
        from tablename
        insert INTO   [dbo].[refresh_results] (result_time, result_table, result_rows)
                  values (@result_time, @result_table, @result_rows);
        commit transaction
        ROLLBACK TRANSACTION @TransactionName;
      END CATCH 
  </code></pre>
  
  <p>or</p>
  
  <pre><code>    if exists (select name from sysobjects where name='tablename')
      begin
        drop table tablename;
      end
      select * into tablename 
      from opendatasource('SQLNCLI', 'Data Source=DATABASE;UID=####;password=####').dbo.viewname;
  
      print '@@error' 
  print @@error
  
  if @@error &lt;&gt; 0 
  Begin
        print 'tablename ***ERROR - check for SUBSTRING***';
        set     @result_table = 'tablename ***ERROR - check for SUBSTRING***'
        select  @result_time = getdate(),
            @result_rows = count(logtime)
        from tablename
        insert INTO   [dbo].[refresh_results] (result_time, result_table, result_rows)
                  values (@result_time, @result_table, @result_rows);
      End
  </code></pre>