How to store SQL Server error message in a table

Posted on 2005-05-10
Last Modified: 2010-03-19
I'm writing one procedure say sp_driver_proc which gives call to procedures proc1() . I want to store the sql server error message  thrown while executing proc1() & want to store the error_id & error message in separate log table . error_id is @@Error but how do i trap the sql server error message.

If proc1() raised error saying can not insert NULL value in so & so table. I would requier to store error id & error discription(I do not want to store custom made error discription) in log table.
Following is example

create proocedure proc1()
insert into table1 values select * from table 2 where column id =<value>

create procedure usp_driver_proc()

exec proc1()
if @@error <> 0
insert into error_log_table (@@error,<sql server error msg>)

while executing driver proc proc1() fails & shows following error message

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'column1', table 'ERROR_LOG'; column does not allow nulls. INSERT fails.
The statement has been terminated.

I want to store above error message as i know i will get errorid by @@error

Please let me know how should i store the sql server error msg in log table
Question by:swapnilsardeshpande
    LVL 4

    Expert Comment

    insert into my errortable (errornum, [error])
    select @@error,[description] from sysmessages
    where error = @@error

    Author Comment

    sysmessages stores description in the following format

    if error id = 515 then msg =
    Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column does not allow nulls. %ls fails.

    means SQLserver generates error message dynamically by putting values at '%.*ls'.

    Please suggest me how to do that, its urgent

    LVL 19

    Accepted Solution


    u can create an alert to collect these kind of information ..

    check sp_add_jobstep  from bol..

    here is an example of some params usage

    if [A-ERR] <> -1
    DECLARE @msg nvarchar(4000)
    declare @JobName varchar(200)

    Set @msg = REPLACE("Error: [A-ERR]  <br>
    Severity: [A-SEV] <br>
    Date: [STRTDT]  <br>
    Time: [STRTTM]  <br>
    Database: [A-DBN]  <br>
    Message: [A-MSG] ", "'", "")

    set  @JobName = 'Error no : [A-ERR] '
     insert into table what ever u wannt

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how the fundamental information of how to create a table.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now