How to log all erros occes in store procedure? [Resolved]

Posted by Allemahesh under Sql Server on 7/22/2013 | Points: 10 | Views : 830 | Status : [Member] [MVP] | Replies : 3
How to log all erros occes in store procedure? Can any one tell me.




Responses

Posted by: Pandians on: 7/23/2013 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
1.Sample Table
CREATE TABLE ErrorLog

(
LogID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
ProcedureName VARCHAR(150) NULL,
ErrorMessage VARCHAR(MAX),
ErrorDate DATETIME
)
2.Sample Procedure
CREATE PROC Usp_Test

AS
BEGIN
SET NOCOUNT ON
DECLARE @Err VARCHAR(MAX)
BEGIN TRY
SELECT 1/0 /*I have forced DIVIDE BY ZERO Error */
END TRY
BEGIN CATCH
SELECT @Err = 'Line No :' + CAST(ERROR_LINE() AS VARCHAR) + ', Description :' + ERROR_MESSAGE()

INSERT ErrorLog(ProcedureName,ErrorMessage,ErrorDate)
SELECT ERROR_PROCEDURE(), @Err, GETDATE()
END CATCH
END
3. Execute the Procedure
EXEC Usp_Test
4.To See the Errors Logged
SELECT * FROM ErrorLog
5.Result
LogID	ProcedureName	ErrorMessage	                                              ErrorDate

1 Usp_Test Line No :9, Description :Divide by zero error encountered. 2013-07-23 18:48:51.030


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 7/23/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
You can use a table called "ErrorLog" and Log the Errors from the Procedure inside the Catch block!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 7/23/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Can you give me some example so that i can implement this.

Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response