Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    Melbourne
    Posts
    11

    Unanswered: How to get the correct error message

    Hi Everyone!

    In my stored procedure I check for any errors during .
    If there are any errors I log them. (by checking @@ERROR)

    What my problem is, the error message that's been logged contain place holders like %s, %l, %d,etc. along with the error message.

    How can I get the full error message, with place holders replaced by real error values/text?

    Here is a sample what I get as the error:

    * Error ID: 547
    * Error Desc: %ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls.

    using this query
    SELECT @errdesc=description FROM master.dbo.sysmessages WHERE error = @errid


    When I run the stored proc in Query Analyser it gives the actual error message as:

    Server: Msg 547, Level 16, State 1, Procedure sp_register_change, Line 366
    INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_TBL_EQUIPMENT'. The conflict occurred in database 'EquipManWT', table 'TBL_EQUIPMENT', column 'unitno'.
    The statement has been terminated.



    Thanks heaps,

    rochana

  2. #2
    Join Date
    Jan 2004
    Posts
    49

  3. #3
    Join Date
    Jan 2004
    Location
    Melbourne
    Posts
    11

    Smile

    Thanx for your reply buser

    Well.. I could use FORMATMESSAGE if i know the parameter values to pass. Because the system issued the RAISERROR I do not have a way of retrieving (or passing) the parameter values to FORMATMESSAGE


    I do not know if there is a way to get parameter values for the error raised by the sql server.

    But I think there should be a way, because the Query Analyzer displays the full error message when I execute the stored proc in there.

    I hope what I am telling is clear

    Code:
     Syntax
    
       FORMATMESSAGE ( msg_number , param_value [ ,...n ] )

  4. #4
    Join Date
    Jan 2004
    Posts
    49

    Unhappy

    I think not... Because who is catching error also should do form the message for a mistake... In this case do rase exception sql server...
    You can write any errors (or some) to SQL server log file and then using DTS transfer this to some table...
    I ask me to correct if it not so...
    Sorry for my english
    see sysmessages col:=dlevel (0x80) and
    sp_altermessage [ @message_id = ] message_number
    , [ @parameter = ] 'write_to_log'
    , [ @parameter_value = ] 'value'

  5. #5
    Join Date
    Jan 2004
    Location
    Melbourne
    Posts
    11
    Hi Buser

    One guy pointed me to this on another forum.

    http://www.nigelrivett.net/spFormatOutputBuffer.html

    And it seems to give the full error message, but not properly formatted.


    Thanks for ur help

  6. #6
    Join Date
    Jan 2004
    Posts
    49
    Permissions
    DBCC OUTPUTBUFFER permissions default only to members of the sysadmin fixed server role, who can see any SPID. Permissions are not transferable.

    select @cmd = 'dbcc outputbuffer(' + convert(varchar(10),@spid) + ')'
    exec (@cmd)

  7. #7
    Join Date
    Jan 2004
    Location
    Melbourne
    Posts
    11

    Unhappy

    yes. can only view if u have admin rights

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •