Post reply

Based on @error how to omit SQL err msg

  • November 29, 2004 at 7:06 am

    #182959

    Hi,

    Question on Error handling. How do you remove SQL error messages and only keep your own defined msg?

    I have this statement:

     INSERT INTO tmpCDR_Hold_Table EXECUTE sp_executesql @resQuery Set @errCode = @@Error 

       IF @errCode = 208

        BEGIN

         Print 'Table ' + @strTableName + ' does not exist in the database. Make sure that table is within defined range. Aborted.'

         Break

        END

    If error is raised, I get SQl error description and mine too, I want only to keep my message? Here is what I get:

    Server: Msg 208, Level 16, State 1, Line 1

    Invalid object name 'mdCallDetailApr2004'.

    Table mdCallDetailApr2004 does not exist in the database. Make sure that table is within defined range. Aborted.

    Thanks for any help.

  • November 29, 2004 at 7:48 am

    #531719

    Jonas,

    You are actually getting 2 messages there, one generated by Sequel Server and your own printed immediately afterwards.

    Working in Query Analyser, I dont think there is any way of turning off the internal error message system. You can SET ANSI_WARNINGS { ON | OFF } but this only turns off divide by zero / count / mathematical error messages.

    Best regards

    Steve

    We need men who can dream of things that never were.

  • November 30, 2004 at 2:01 pm

    #531963

    You can check for the existence of the table before the insert, raise your error if the table doesn't exist, and bypass the insert statement.

    if exists (select * from sysobjects where id = object_id(N'[dbo].[tmpCDR_Hold_Table]') and OBJECTPROPERTY(id, N'IsTable') = 1)

      INSERT INTO tmpCDR_Hold_Table EXECUTE sp_executesql @resQuery

    else

        BEGIN

         Print 'Table ' + @strTableName + ' does not exist in the database. Make sure that table is within defined range. Aborted.'

         Break

        END

  • December 1, 2004 at 2:26 pm

    #532111

    Hoo,

    tmpCDR_Hold_Table is a temporary storage for any data that EXECUTE sp_executesql @resQuery returns. @resQuery is the one that might retun invalid table name, so it has to be where it is right now.

    Thanks for advise anyway.

  • December 2, 2004 at 7:44 am

    #532193

    Yes, of course.  Sorry about that.  However, the general idea will still work.  Just plug in @strTableName where I have tmpCDR_Hold_Table in the IF EXISTS

    Steve

  • December 3, 2004 at 5:36 am

    #532398

    Yeap. You are right. There are two options

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply