"Query completed with errors."  - but there are no errors...

Posted on 2006-05-26
Last Modified: 2010-08-05
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.
Question by:dbaGrant
    LVL 15

    Expert Comment

    In QA click on the Messages tab (right next to the Results tab).  It will show the errors.

    Author Comment

    No, there are no errors listed on the messages tab.  All the messages tab shows is the number of rows affected.  That is what is confusing.  Usually, if there is an error, you see it.  The only indication that there is an error here is that the status bar says "Query batch completed with errors."
    LVL 15

    Expert Comment

    You can test @@ERROR after each SQL statement to determine which statements are giving you errors.
    LVL 28

    Expert Comment

    >>then insert records into a table.
    Does those tables have any triggers on them?
    LVL 23

    Accepted Solution

    Run a Profiler Trace taking all the "Error and Warnings" section as it may give you more precise indication to the nature of the error (last statement executed before the error warning)...Hope this helps...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now