Invalid Object Name for Table which is successfully updated in the same Stored Procedure

Question

We are seeing 'interesting behavior with our SQL Sever Database. We have a merge statement which selects a table X. In the match clauses there is a subselect to table X. When we execute the stored procedure from the SQL Server Tools it works fine. But when executed from IPC (an ETL Tool) we get an exception Invalid object name 'X'.

So far nothing special as I understand there can get lots of things wrong with permissions and stuff.

The strange thing: The merge statement is in a try block and in the catch block the error message gets written into the table X via an update statement! How is this possible when Sql Server complains it can't find a table X?

Also everything works fine with another stored procedure which is constructed in the same way (via code generation) but on a different set of tables.

The code looks like this

    merge ...
    using
    (select ...
    from dbo.X
    where ...
    when not matched by target 
    and not exists (select 1 from dbo.X q2 where ...)
    then insert (...
    )
    values (...
    )
    when matched and q.ACTION='D'
    then delete
    when matched AND NOT exists (select 1 from dbo.X q3 where ...)
    then update 
    set
      ...

    OUTPUT $action INTO @l_SummaryOfChanges;
    -- Query the results of the table variable.
    SELECT ACTION, COUNT(*) AS CountPerChange
    FROM @l_SummaryOfChanges
    GROUP BY ACTION;
  end try
  begin catch
    update dbo.X
    set LAST_ERROR_MSG=ERROR_MESSAGE(), ERROR_COUNTER=ERROR_COUNTER+1
    where SYNC_ID=@l_SyncID
  end catch

Any ideas what is going on?Invalid object name 'sync$_tabTeiledaten'.

Answer 1

We found it. gbn's question triggered the realization that the usage of X had nothing to do with the exception. In fact on target table of the merge was a trigger which is referencing X but from a different schema without actually specifying the schema.

May somebody will benefit from the way we debugged this shit:

• we duplicated X with a new name (Y) and still got the error message saying 'Invalid object name 'X'. At that point we thought we might reference a view or something so ..
• we removed all the columns (there where lots of) from the merge statement, except those which where necessary due to Not Null Constraints. The problem persisted
• We removed 1 of the branches of the merge statement at a time. The problem persisted.
• we removed the complete merge statement. The error was gone. At that point we realized that something fishy might go on with the target table.
• On inspection we found the trigger from hell.