When I execute a particular stored procedure in SQL Server 2000 Query Analyzer, the procedure completes and the results pane shows the number of records affected by the procedure. (The number of records affected indicate that the procedure completed successfully. Also, the correct number of records are inserted into a table.) However, the bottom of the query analyzer window says that the "query batch completed with errors." The result pane does not show any errors, only how many records were affected. Unfortunately, the procedure won't complete at all when it is called from a SQL Server job. The job reports failure, and the procedure does not insert all of the records into the table.
All the procedure does is select values into variables from various tables and then insert records into a table.
What might cause this? Is there a way to trap "soft" errors so I can see what is happening?
Your help is much appreciated.