Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    4

    Unanswered: Getting error description

    Hi all,
    I need to get the error description from the SQL Server in a SP. For ex:

    I have one insert statement which is inserting some values in a tabUserMaster table. If user tries to insert any duplicated row then following error is retruned [in Query analyzer].

    Server: Msg 2601, Level 14, State 3, Procedure csp_ProvisionUser, Line 70
    Cannot insert duplicate key row in object 'CoreUser' with unique index 'IDX_CoreUser_UserName'.
    The statement has been terminated.

    I want to trap this whole message in a variable. How to do this.....

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    Hope someone has the answer, if it's possible to do this.

    If not, can't you use @@ERROR to just trap the kind of error and then print the details about the object, index etc manually ?

  3. #3
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135
    ...since I'm not sure what you'd like to do once you have captured it, I'm not sure how to answer...but if you lookt at BOL and search on error it will bring up several topics which might apply (e.g. @@error)...

  4. #4
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    hi,
    <your-DML-qry>
    select @in_ErrorNumber=@@error()
    if @in_ErrorNumber > 0
    Begin
    insert into <table> values (dbo.fun_error_desc(@err_no,@vr_JobNumber))
    End

    create function fun_error_desc(@err_no int)
    returns varchar(2000)
    as
    Begin
    declare @err_message varchar(200)
    select @err_message = description from master.dbo.sysmessages where error=@err_no
    return @err_message
    End

    pavan

  5. #5
    Join Date
    Jul 2002
    Posts
    229
    With this method, you'll get the generic message, without any parameter substitution?

  6. #6
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    I am even stuck in the same situation.
    If u have a single table involved in DML queries,then better send the table-name as a parameter and replace it accordingly.

Posting Permissions

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