locked
sys.dm_sql_referenced_entities acts differently in a try catch block than when not in one, why? RRS feed

  • Question

  • I first created a stored procedure that references a table using a linked server. I then deleted the linked server. I am trying to test the behavior of sys.dm_sql_referenced_entities in this condition. When I run the function by itself it throws an exception, actually two of them both at a level > than 10.

    SELECT *
    from sys.dm_sql_referenced_entities('dbo.MissingLinkedServer', 'OBJECT');
    

    Msg 7202, Level 11, State 2, Procedure MissingLinkedServer, Line 6

    Msg 2020, Level 16, State 1, Line 2

    When this is run in a try/catch block I don't get any error at all.

    BEGIN TRY
    SELECT *
    from sys.dm_sql_referenced_entities('dbo.MissingLinkedServer', 'OBJECT');
    END TRY
    BEGIN CATCH		PRINT 'Catch block';
    END CATCH;
    
    
    What is the explanation for this different behavior?

    Thanks.


    Tom G.

    Sunday, May 5, 2013 5:54 PM

Answers

  • You did not say which version of SQL Server you are using, but I assume that you are using SQL 2012, because when I tested it, I found that it works as expected on SQL 2008. I even found a Connect item which suggested this technique. So I say that this is a regression bug.

    I have submitted a Connect item for the issue:
    https://connect.microsoft.com/SQLServer/feedback/details/786389/error-in-dm-sql-referenced-entities-is-not-caught-in-sql-2012-regression

    My repro does not use a linked server, but a dropped column, but the behaviour is the same.

    If this is critical for you, you should open a case. A bug on Connect could be fixed next week, in the next release or never.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Monday, May 6, 2013 10:51 PM
    • Marked as answer by Allen Li - MSFT Monday, May 13, 2013 9:12 AM
    Sunday, May 5, 2013 6:58 PM
  • Hi Tom,

    As Erland suggested, I tested with SQL Server 2008 R2 SP2 and found the same result, if we put the codes into a try catch block, it will not generate error message.

    begin try 
    	SELECT *
    	from sys.dm_sql_referenced_entities('dbo.MissingLinkedServer', 'OBJECT');
    end try
    
    begin catch
    	print(error_message())
    end catch
    

    If you have any feedback on our support, please click here.



    Allen Li
    TechNet Community Support

    Tuesday, May 7, 2013 5:46 AM

All replies

  • You did not say which version of SQL Server you are using, but I assume that you are using SQL 2012, because when I tested it, I found that it works as expected on SQL 2008. I even found a Connect item which suggested this technique. So I say that this is a regression bug.

    I have submitted a Connect item for the issue:
    https://connect.microsoft.com/SQLServer/feedback/details/786389/error-in-dm-sql-referenced-entities-is-not-caught-in-sql-2012-regression

    My repro does not use a linked server, but a dropped column, but the behaviour is the same.

    If this is critical for you, you should open a case. A bug on Connect could be fixed next week, in the next release or never.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Monday, May 6, 2013 10:51 PM
    • Marked as answer by Allen Li - MSFT Monday, May 13, 2013 9:12 AM
    Sunday, May 5, 2013 6:58 PM
  • Hi Tom,

    The reason for different behaviour in Try Catch block is as mentioned below from - http://msdn.microsoft.com/en-us/library/ms175976.aspx

    The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

    Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

    The following example shows how an object name resolution error generated by a SELECT statement is not caught by the TRY…CATCH construct, but is caught by the CATCH block when the same SELECT statement is executed inside a stored procedure.

    USE AdventureWorks2012;
    GO
    BEGIN TRY
        -- Table does not exist; object name resolution
        -- error not caught.
        SELECT * FROM NonexistentTable;
    END TRY
    BEGIN CATCH
        SELECT 
            ERROR_NUMBER() AS ErrorNumber
            ,ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    The error is not caught and control passes out of the TRY…CATCH construct to the next higher level.

    Running the SELECT statement inside a stored procedure will cause the error to occur at a level lower than the TRY block. The error will be handled by the TRY…CATCH construct.

    -- Verify that the stored procedure does not exist.
    IF OBJECT_ID ( N'usp_ExampleProc', N'P' ) IS NOT NULL 
        DROP PROCEDURE usp_ExampleProc;
    GO
    -- Create a stored procedure that will cause an 
    -- object resolution error.
    CREATE PROCEDURE usp_ExampleProc
    AS
        SELECT * FROM NonexistentTable;
    GO
    BEGIN TRY
        EXECUTE usp_ExampleProc;
    END TRY
    BEGIN CATCH
        SELECT 
            ERROR_NUMBER() AS ErrorNumber
            ,ERROR_MESSAGE() AS ErrorMessage;
    END CATCH;

    For other details on SQL Server Object dependencies - http://sathyadb.blogspot.in/2012/08/sql-server-object-dependencies.html

    sathya --------- Please Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Monday, May 6, 2013 2:54 AM
  • The error is not caught and control passes out of the TRY…CATCH construct to the next higher level.

    No, this is not correct. I would recommend that you test before you make any such statements. You will find that even if you have a TRY-CATCH block in an outer procedure, the error is not caught in SQL 2012. It is caught in SQL 2008, but then it is also caught in the innermost CATCH block.

    This is a regression bug, please see my first post.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, May 6, 2013 7:23 AM
  • My code had an outer try/catch and that did not catch it.


    Tom G.

    Monday, May 6, 2013 11:50 PM
  • Hi Tom,

    As Erland suggested, I tested with SQL Server 2008 R2 SP2 and found the same result, if we put the codes into a try catch block, it will not generate error message.

    begin try 
    	SELECT *
    	from sys.dm_sql_referenced_entities('dbo.MissingLinkedServer', 'OBJECT');
    end try
    
    begin catch
    	print(error_message())
    end catch
    

    If you have any feedback on our support, please click here.



    Allen Li
    TechNet Community Support

    Tuesday, May 7, 2013 5:46 AM
  • there is a workaround at ms website and i found it useful. i already tested it on 2014 and it works.

    just call sp_refreshsqlmodule right before a function query.

    https://connect.microsoft.com/SQLServer/feedback/details/786389/error-in-dm-sql-referenced-entities-is-not-caught-in-sql-2012-regression


    Paul P.S.

    Friday, July 25, 2014 8:16 AM