locked
T-SQL "TRY CATCH" and OLE DB error records RRS feed

  • Question

  • Hello All,

    My OLE DB Provider (IBProvider) returns two error records for each OLE DB error:

    1. Message from DBMS
    2. Own hint message

    For example:

    BAD SQL: update IBP_TEST_FB30_D3...DUAL set ID = 123;

    Linked server error messages:

    1. Msg 18052, Level 16, State 1, Line 0
      Operation violates CHECK constraint CHECK_DUAL on view or table DUAL At trigger 'CHECK_2'
    2. Msg 18052, Level 16, State 1, Line 0
      Failed to execute the SQL statement.

    When I try to catch the error (TRY ... CATCH) I always get the second message

    begin try
           update IBP_TEST_FB30_D3...DUAL set ID = 123;
    end try
    begin catch
           print concat('EXCEPTION OCCURRED: ',error_message());
    end catch

    Output:

    EXCEPTION OCCURRED: Failed to execute the SQL statement.

    Question

    How to enumerate all messages of error in CATCH section?

    Wednesday, February 1, 2017 7:52 PM

All replies

  • Hello Kovalenko_Dmitry,

    Please take a look at this link. Does that help?


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Wednesday, February 1, 2017 9:15 PM
  • Please take a look at this link. Does that help?

    No
    Thursday, February 2, 2017 8:29 AM
  • The point I was trying to stress that, it's not about the TRY / CATCH but rather about the method that raises the error. You may want to focus on that?

    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Thursday, February 2, 2017 8:32 AM
  • Dima

    In addition, do you run this statement within a trigger ? How does the linked server play role here?

    SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage;


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, February 2, 2017 8:38 AM
    Answerer
  • My error raised in OLE DB provider.

    In CATCH section I want enumerate ALL messages this error.

    Thursday, February 2, 2017 8:38 AM
  • Dima

    In addition, do you run this statement within a trigger ? How does the linked server play role here?

    SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage;

    Great idea.

    But

    begin try
           update IBP_TEST_FB30_D3...DUAL set ID = 123
    end try
    begin catch
    SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage; 
    end catch
    returns only one record with last (second) message - "Failed to execute the SQL statement." :(

    Thursday, February 2, 2017 8:45 AM
  • Dima

    update IBP_TEST_FB30_D3...DUAL set ID = 123
    BP_TEST_FB30_D3 is linked server? Is that possible the table has a trigger?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, February 2, 2017 8:47 AM
    Answerer
  • Dima

    update IBP_TEST_FB30_D3...DUAL set ID = 123
    BP_TEST_FB30_D3 is linked server? Is that possible the table has a trigger?



    Yes, IBP_TEST_FB30_D3 it is linked server though my OLE DB provider to my TEST database

    Yes, DUAL table has the trigger and CHECK constraint for preventing delete and update.

    I want to catch and process all messages of error in T-SQL code.

    As I understand - at current time, it is impossible.

    Thursday, February 2, 2017 8:57 AM
  • Ok, that means if you disable the trigger the update will completed successfully? 

    Please read Erland's article 

    http://www.sommarskog.se/error_handling/Part1.html


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Thursday, February 2, 2017 9:05 AM
    Answerer
  • https://social.technet.microsoft.com/wiki/contents/articles/29187.t-sql-error-handling-for-check-constraints.aspx

    http://stackoverflow.com/questions/8321619/create-custom-error-message-in-check-constraints-in-sql-server-2008


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, February 2, 2017 9:14 AM
    Answerer
  • You really do not understand my question.

    This messages from my OLE DB provider (written on C++). Not from database/triggers/checks and other.

     try
     {
      m_connection->dangerous__GetActiveRemoteCn()->StmtExecute
       (op_ctx,
        &spTrData->m_handle,
        &m_stmt_data->m_handle,
        in_xparam?in_xparam->get_public_xsqlda():structure::null_ptr,
        out_xparam?out_xparam->get_public_xsqlda():structure::null_ptr); //throw FIRST message
     }
     catch(const std::exception& e)
     {
      t_ibp_error exc(e);
    
      exc.add_error(exc.com_code(),ibp_mce_cmd_execute_SQL_stmt_0); //append SECOND message with hint
    
      exc.raise_me();
     }//catch
    


    I want to process all error messages in T-SQL code. Without modification of existing databases.

    T-SQL not able to enumerate error messages.

    Ok. I will modify my provider for exclude second message.

    Thursday, February 2, 2017 9:36 AM
  • Well, perhaps you ask the question in C++ forum as it has nothing to  do with T-SQL?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, February 2, 2017 9:46 AM
    Answerer
  • Well, perhaps you ask the question in C++ forum as it has nothing to  do with T-SQL?

    Originally this question was asked in forum for MSSQL Engine.

    Because, from my point of view this is problem of MSSQL, not T-SQL.

    Although and T-SQL also.

    Thursday, February 2, 2017 9:50 AM
  • Well, perhaps you ask the question in C++ forum as it has nothing to  do with T-SQL?

    Originally this question was asked in forum for MSSQL Engine.

    Because, from my point of view this is problem of MSSQL, not T-SQL.

    Although and T-SQL also.

    Did you try throw, instead of print
    Thursday, February 2, 2017 11:16 AM
  • Did you try throw, instead of print

    Question about enumeration of error messages. Not about rethrow of error.

    In general - this is problem of one our customer.

    He want obtain all messages of error in CATCH section :)

    It is a pity that the T-SQL does not have a support for this task.

    Thursday, February 2, 2017 11:47 AM
  • Question about enumeration of error messages. Not about rethrow of error.

    In general - this is problem of one our customer.

    He want obtain all messages of error in CATCH section :)

    It is a pity that the T-SQL does not have a support for this task.

    Let me first say I think returning errors as informational messages from T-SQL (PRINT statements) is an odd development pattern. IMHO, it would be better not catch the error in T-SQL at all (or rethrow) and let the client application control handling of the error.

    Only one error message can be returned in a T-SQL CATCH block. Consider upvoting the suggestion in Connect to allow multiples to be returned: https://connect.microsoft.com/SQLServer/Feedback/Details/2785302

    A kluge workaround is to create a SQLCLR proc that executes the statement. The proc can then return the errors as informational messages, similarly to T-SQL PRINT. T-SQL would be oblivious to the messages with this technique so if you have other purposes for catching the error in T-SQL, you would need to instead aggregate the error messages and throw an exception in the SQLCLR code so that it can be caught in T-SQL.

        public static void ExecuteWithErrorsAsInfoMessages(string query)
        {
            try
            {
                using (var connection = new SqlConnection("context connection=true"))
                {
                    connection.Open();
                    var command = new SqlCommand(query, connection);
                    command.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                foreach (SqlError error in ex.Errors)
                {
                    SqlContext.Pipe.Send(error.Message);
                }
            }
            
        }


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com



    • Edited by Dan GuzmanMVP Thursday, February 2, 2017 1:26 PM
    • Proposed as answer by Albert_ Zhang Tuesday, February 14, 2017 7:45 AM
    Thursday, February 2, 2017 1:25 PM