none
Is Fulltext Search Considered Lower Level by Catch Block?

    Question

  • I am trying to isolate an issue and created several test cases:

    • Good multi-row single resultset.
    • Good multi-row single resultset with informational message.
    • Good multi-row single resultset with Raiserror (less than level 11).
    • Good multi-row single resultset with Print.
    • Good empty single resultset.
    • Bad syntax error.
    • Bad syntax error with try/catch.
    • Bad error with Raiserror (level 11).
    • Bad error with Raiserror (level 11) and try/catch.
    Between these tests, the syntax error with try/catch behaved differently than what I expected. Two resultsets (one empty and the second from the catch block) come back.

    According to the article ms175976 (http://msdn.microsoft.com/en-us/library/ms175976.aspx), the catch block won't trigger unless the syntax error is in a lower level. Does this mean that the fulltext search is done in a lower level?

    Here is my procedure:
    AS
    BEGIN
    BEGIN TRY
      SELECT '1' [myfield] FROM test_fulltext (nolock) WHERE CONTAINS(Command,'a monkey')
      RETURN 0
    END TRY
    BEGIN CATCH
      SELECT ERROR_NUMBER() [ErrorNumber]
    END CATCH
    RETURN -9999
    END
    
    

    Here is my test table:
    CREATE TABLE test_fulltext (SPID INT NOT NULL, Command VARCHAR(32) NULL)
    CREATE UNIQUE INDEX fulltextui ON test_fulltext(SPID);
    CREATE FULLTEXT CATALOG fulltextft AS DEFAULT;
    CREATE FULLTEXT INDEX ON test_fulltext(Command) KEY INDEX fulltextui;
    


    Saturday, January 29, 2011 3:13 AM

Answers

  • > Thank you for your help. I compared your test and there is some difference that I am missing. For me the 7630 does not appear in the Messages tab, it is returned as the second resultset in the Results tab (which is what causes issues with app logic). At this point, I have to concede that there must be an environmental or configuration combination in our setup that is responsible.

    Are you saying that if you run the script that I posted, you get a result set, that is the CATCH handler is invoked? In such case, which version? The one the argument to CONTAINS is a constant or where is a variable? Or does your script look different?

    Invoking the CATCH handler would the right thing to happen. Whether there should be one or two results sets can be disputed, but SQL Server may produce the metadata for the first result set, before it finds out that the argument to CONTAINS is incorrect. If that second result set causes problem for the application, I think you should reimplment the CATCH handler to use RAISERROR or similar.

    The one difference in your setup is that you have SQL 2008 SP1 and I have SQL 2008 SP2. I can't seem to find an SP1 instance with fulltext installed, so I cannot test on that version.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Marked as answer by cscode Wednesday, February 09, 2011 6:46 PM
    Monday, January 31, 2011 10:17 PM

All replies

  • When I try this in SQL 2008:

    CREATE TABLE test_fulltext (SPID INT NOT NULL, Command VARCHAR(32) NULL)
    CREATE UNIQUE INDEX fulltextui ON test_fulltext(SPID);
    CREATE FULLTEXT CATALOG fulltextft AS DEFAULT;
    CREATE FULLTEXT INDEX ON test_fulltext(Command) KEY INDEX fulltextui;
    go
    create proc pelle as
    BEGIN
    PRINT 'Hej'
    BEGIN TRY
       SELECT '1' [myfield] FROM test_fulltext (nolock) WHERE CONTAINS(Command,'a monkey')
       RETURN 0
    END TRY
    BEGIN CATCH
      SELECT ERROR_NUMBER() [ErrorNumber]
    END CATCH
    RETURN -9999
    END
    go
    exec pelle
    go
    drop proc pelle
    DROP TABLE test_fulltext
    DROP FULLTEXT CATALOG fulltextft

    I get this output:

    Msg 7630, Level 15, State 3, Procedure pelle, Line 5
    Syntax error near 'monkey' in the full-text search condition 'a monkey'.
    Warning: The fulltext catalog 'fulltextft' is being dropped and is currently set as default.

    My interpretation of this is that the procedure is created, but since the PRINT statement is not executed, this means that when the optimizer builds a query plan, the full-text error is detected.

    Then I changed the batch to:

    DECLARE @x varchar (20) = '''a monkey'''
    BEGIN TRY
       SELECT '1' [myfield] FROM test_fulltext WHERE CONTAINS(Command,@x)

    The output this time:

    Hej
    Msg 7630, Level 15, State 3, Procedure pelle, Line 6
    Syntax error near 'monkey'' in the full-text search condition ''a monkey''.
    Warning: The fulltext catalog 'fulltextft' is being dropped and is currently set as default.

    This time we entered the procedure, but nevertheless the CATCH handler was not invoked. If wrap the EXEC statement in TRY-CATCH:

    BEGIN TRY
       exec pelle
    END TRY
    BEGIN CATCH
       PRINT 'Error in pelle: ' + error_message()
    END CATCH  

    This CATCH block catches the error.

    The behaviour is not entirely unknown. The same issue arises when you have something like:

    BEGIN TRY
       SELECT * FROM doesnotexist
    END TRY
    BEGIN CATCH
       PRINT 'This is not printed'
    END CATCH

    This is a compilation error that appears at run-time. Apparently fulltext is handled in a similar way, although I think it's crazy that an error in a variable cannot be caught in the local scope.

    Unfortunately, while TRY-CATCH did improve the situation a lot, there are still so many weired situations with error handling in SQL Server.

    I need to add the disclaimer that I don't have any experience of working with fulltext myself.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Saturday, January 29, 2011 6:31 PM
  • Thank you for your help. I compared your test and there is some difference that I am missing. For me the 7630 does not appear in the Messages tab, it is returned as the second resultset in the Results tab (which is what causes issues with app logic). At this point, I have to concede that there must be an environmental or configuration combination in our setup that is responsible. I have the @@options printed and the version list from the About box for Mgt Studio, in case it shows anything blatant.

    Set options:

    5496
    ANSI_WARNINGS
    ANSI_PADDING
    ANSI_NULLS
    ARITHABORT
    QUOTED_IDENTIFIER
    ANSI_NULL_DFLT_ON
    CONCAT_NULL_YIELDS_NULL
    
    

    About version list:
    Microsoft SQL Server Management Studio	10.0.2531.0
    Microsoft Analysis Services Client Tools	10.0.1600.22
    Microsoft Data Access Components (MDAC)	6.1.7600.16385
    Microsoft MSXML						3.0 4.0 5.0 6.0 
    Microsoft Internet Explorer				8.0.7600.16385
    Microsoft .NET Framework				2.0.50727.4952
    Operating System					6.1.7600

    Monday, January 31, 2011 7:46 PM
  • > Thank you for your help. I compared your test and there is some difference that I am missing. For me the 7630 does not appear in the Messages tab, it is returned as the second resultset in the Results tab (which is what causes issues with app logic). At this point, I have to concede that there must be an environmental or configuration combination in our setup that is responsible.

    Are you saying that if you run the script that I posted, you get a result set, that is the CATCH handler is invoked? In such case, which version? The one the argument to CONTAINS is a constant or where is a variable? Or does your script look different?

    Invoking the CATCH handler would the right thing to happen. Whether there should be one or two results sets can be disputed, but SQL Server may produce the metadata for the first result set, before it finds out that the argument to CONTAINS is incorrect. If that second result set causes problem for the application, I think you should reimplment the CATCH handler to use RAISERROR or similar.

    The one difference in your setup is that you have SQL 2008 SP1 and I have SQL 2008 SP2. I can't seem to find an SP1 instance with fulltext installed, so I cannot test on that version.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Marked as answer by cscode Wednesday, February 09, 2011 6:46 PM
    Monday, January 31, 2011 10:17 PM
  • Yes, the CATCH handler is invoked using the first script where the CONTAINS is constant.

    When the CONTAINS is a variable, no result set is returned and the error prevents execution. Only the Messages tab has warnings and error information just like your description.

    I did not attempt the third version using the EXEC because I can see how the EXEC will cause the statement to run at a lower level and the documentation shows that a lower level will trigger the CATCH.

    The last script (using table "doesnotexist") behaves the same as your description. This is actually the acceptable behavior for my situation based on the documentation. Two result sets, to me, seems very unusual. I would be more prepared to accept a compile error than to receive two result sets.

    I agree our CATCH handler needs adjustment to expose the exception out to the app logic, in this scenario. I think we have to examine our blanket application of TRY/CATCH. We adopted a company standard to use the TRY/CATCH in every new stored procedure over a year ago.

    Wednesday, February 09, 2011 6:44 PM
  • > The last script (using table "doesnotexist") behaves the same as your description. This is actually the acceptable behavior for my situation based on the documentation. Two result sets, to me, seems very unusual. I would be more prepared to accept a compile error than to receive two result sets.

    Here is another example with two result sets:

    BEGIN TRY
       SELECT name, object_id, 1.0 / (object_id - 98)
       FROM   sys.objects
    END TRY
    BEGIN CATCH
       SELECT error_message()
    END CATCH

    Essentially, if you get an error when a SELECT statement is running,
    the metadata will still be produced.

    Then again, it seems in your case, this has been "fixed" in SP2.     


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Wednesday, February 09, 2011 11:20 PM
  • Very nice. Initially, when I used this statement verbatim I received one result set of 585 rows. However, when I changed the value 98 to the highest value I have for object_id it forced the division exception and showed the two result sets. Terrific! I will make this a permanent addition to my test cases. Thank you.
    Friday, February 11, 2011 5:50 PM