Jwalin Khatri

Following store procedure do the 3 things 

1]. Insert record into the Table
2]. Writing into the Text file
3]. Send email  

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE procedure [dbo].[spErrorHandling] 
(

@Error_Number int,
@Error_Procedure varchar(200),
@LineNumber varchar(20)
)

AS 

– Declaration statements

DECLARE @Error_Message varchar(4000)
DECLARE @Error_Severity int
DECLARE @Error_State int
DECLARE @Error_Line int
DECLARE @UserName varchar(200)
DECLARE @HostName varchar(200)
DECLARE @Time_Stamp datetime

/*******************************************
READ THE DESCRIPTION FROM LOG
******************************************/

declare @ErrorNo int, 
     @ErrorDesc varchar(4000) 

SET @ErrorNo = @Error_Number
BEGIN
SET NOCOUNT ON;
CREATE TABLE #Log
(
Data varchar(8000),
ContinuationRow int
)

DECLARE @sql varchar (100)

–  SQL Server 2000 allows to read the SQL Server Error Log using the system stored procedure
SELECT @sql = ‘.master.dbo.xp_readerrorlog‘
INSERT #Log

EXEC @sql

DECLARE @err_no varchar(1000), 

      @err_mesg varchar(1000),
      @date varchar (30), 
      @charidx int 

SELECT TOP 1 @err_no=data FROM #Log

WHERE data LIKE ‘%’ + CONVERT(varchar(4), @ErrorNo) + ‘%’ ORDER BY data DESC

–SELECT * FROM #Log WHERE
– data like ‘%’ + @data + ‘%’

SET  @charidx = CHARINDEX(’spid’, @err_no)
SET @date = SUBSTRING(@err_no,0,@charidx)

SELECT  @err_mesg = data FROM #Log 
WHERE data LIKE ‘%’ + @date + ‘%’ 
AND data NOT LIKE ‘%Error%’

DROP table #Log

SET @err_no = LTRIM(SUBSTRING(@err_no, @charidx + 7, LEN(@err_no)))
SET @err_mesg = LTRIM(SUBSTRING(@err_mesg, @charidx + 7, LEN(@err_mesg)))
SET @ErrorDesc = @err_no + char(13) + ‘Desc: ‘ + @err_mesg + char(13) + ‘Date: ‘ + @date 

–print ‘description ==== ‘ + @ErrorDesc

END

/**************************************
GET ALL THE VALUES
***************************************/

SELECT  @Error_Message=@ErrorDesc, –, @Error_Severity=severity, @Error_State=dlevel,
     @UserName = SUSER_SNAME(), 
    @HostName =  HOST_NAME(), @Time_Stamp = GETDATE()
–FROM master.dbo.sysmessages WHERE error=@Error_Number 

/*************************************
Writing into the DATABASE TABLE
*************************************/

– Insert into the dbo.ErrorHandling table

INSERT INTO ErrorHandling (Error_Number, Error_Message, Error_Severity, Error_State, Error_Procedure, UserName, HostName, Time_Stamp) 
values( @Error_Number, @Error_Message, @Error_Severity, @Error_State, @Error_Procedure, @UserName, @HostName, @Time_Stamp)

–SELECT * FROM master.dbo.sysmessages WHERE error=514

/*************************************
Writing into the TEXT FILE
*************************************/

/*=====================
write the results to a text file
======================*/

DECLARE  @vchrFile VARCHAR(1000) ,
     @vchrFileID INT ,
     @FS INT ,
     @RC INT

–this will be on the sql server, not your pc

SET @vchrFile = ‘c:\SQLError.txt’

EXEC @RC = sp_OACreate ‘Scripting.FileSystemObject’, @FS OUT

IF @RC <> 0 
 BEGIN
     PRINT ‘Error:  Creating the file system object’
 END

– Opens the file specified by the @vchrFile input parameter
EXEC @RC = sp_OAMethod @FS , ‘OpenTextFile’ , @vchrFileID OUT , @vchrFile , 8 , 1

– Prints error if non 0 return code during sp_OAMethod OpenTextFile execution 
IF @RC <> 0 
 BEGIN
       PRINT ‘Error:  Opening the specified text file’
END

DECLARE @vchrText AS VARCHAR(4000)

–SET @vchrText = ‘ERROR NUMBER: ‘ + CAST(@Error_Number as Varchar(20)) + CAST(‘ # ‘ as Varchar(3)) + ‘DESCRIPTION: ‘ + CAST(@Error_Message as varchar(3500)) + CAST(‘ # ‘ as Varchar(3)) + CAST(@LineNumber as varchar(20)) + CAST(‘ # ‘ as Varchar(1)) + ‘Severity: ‘ + CAST(@Error_Severity as varchar(3)) + CAST(‘ # ‘ as Varchar(3)) + ‘State: ‘ + CAST(@Error_State as varchar(10)) + CAST(‘ # ‘ as Varchar(3)) + ‘STORE PROCEDURE NAME: ‘ + CAST(@Error_Procedure  as varchar(100)) + CAST(‘ # ‘ as Varchar(3)) + ‘USERNAME: ‘ + CAST(@UserName as varchar(50)) + CAST(‘ # ‘ as Varchar(3)) + ‘HOST NAME: ‘ + CAST(@HostName as varchar(50))+ CAST(‘ # ‘ as Varchar(3)) + ‘DATE TIME: ‘ + CAST(@Time_Stamp as varchar(30))

SET @vchrText = ‘STORE PROCEDURE NAME: ‘ + CAST(@Error_Procedure  as varchar(100)) + char(13) + @LineNumber + char(13) + @ErrorDesc + char(13) +  ’=======================’

 – Appends the string value line to the file specified by the @vchrFile input parameter
 EXEC @RC = sp_OAMethod @vchrFileID, ‘WriteLine’, Null , @vchrText

   — Prints error if non 0 return code during sp_OAMethod WriteLine execution
 IF @RC <> 0 
  BEGIN
        PRINT ‘Error:  Writing string data to file’
  END

EXECUTE @RC = sp_OADestroy @vchrFileID
EXECUTE @RC = sp_OADestroy @FS

/**********************************************
SEND AN EMAIL IN TEXT FORMAT
***********************************************/

DECLARE @body VARCHAR(4000),
      @Subject varchar(100),
      @to varchar(100)

–SET @body =  Replace(@vchrText, char(13), ‘<br>’)

SET @body =  @vchrText
SET @Subject = ‘Error Occured in STORE PROCEDURE’
–SET @to = ‘email@gmail.com; email1@gmail.com’
SET @to = ‘email@gmail.com’

BEGIN 

–        EXEC master..xp_sendmail 
–            @recipients=’email@gmail.com’, 
–            @message = @body, 
–            @subject = ‘Error Occured in DTS.’
–Error: xp_sendmail: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.

–

– EXEC master..xp_smtp_sendmail 
–            @TO = ‘email@gmailcom’, 
–            @from = ‘email@gmail.com’, 
–            @message = @body, 
–            @subject = ‘Error Occured in DTS.’, 
–            @server = ‘127.0.0.1′ 

–Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object ‘master..xp_smtp_sendmail’. The stored procedure will still be created.

DECLARE  

        @handle INT,  
        @return INT,  
        @s VARCHAR(64),  
        @sc VARCHAR(1024),  
        @up CHAR(27),  
        @from VARCHAR(64),  
        @server VARCHAR(255),  
        @filename VARCHAR(255)  

    SET @s = ‘”http://schemas.microsoft.com/cdo/configuration/’  

    SELECT  

        @s = ‘Configuration.Fields(‘ + @s,  
        @up = ‘Configuration.Fields.Update’,  
        @from = ‘email@gmail.com’,  
        @server = ‘127.0.0.1′ –’smtp.yourdomain.com’ 
        – or IP address, e.g. ‘127.0.0.1′ 

        – if you want an attachment:  
        –,@filename = ‘C:\new folder\file.ext’  

    EXEC @return = sp_OACreate ‘CDO.Message‘, @handle OUT  
    SET @sc = @s + ’sendusing”).Value’  
    EXEC @return = sp_OASetProperty @handle, @sc, ‘2′  
    SET @sc = @s + ’smtpserver”).Value’  
    EXEC @return = sp_OASetProperty @handle, @sc, @server  
     EXEC @return = sp_OAMethod @handle, @up, NULL  
     EXEC @return = sp_OASetProperty @handle, ‘To’, @to  
     EXEC @return = sp_OASetProperty @handle, ‘From’, @from  
    EXEC @return = sp_OASetProperty @handle, ‘Subject’, @subject  
    EXEC @return = sp_OASetProperty @handle, ‘TextBody’, @body  

    IF @filename IS NOT NULL  
        EXEC @return = sp_OAMethod @handle, ‘AddAttachment’, NULL, @filename  

    EXEC @return = sp_OAMethod @handle, ‘Send’, NULL  
    IF @return <> 0  

BEGIN  
       PRINT ‘Mail failed.’  
       IF @from IS NULL  
             PRINT ‘From address undefined.’  
       ELSE  
             PRINT ‘Check that server is valid.’  
END

ELSE  
        PRINT ‘Mail sent.’  

EXEC @return = sp_OADestroy @handle  

END 

============================================

Following store procedure call the ’spErrorHandling’ store procedure 

ALTER PROCEDURE [dbo].[usp_insert_notnull]

as

DECLARE @value int

DECLARE @myERROR int – Local @@ERROR,
         @myRowCount int – Local @@ROWCOUNT

INSERT notnull_test VALUES (@value)

SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT

IF @myERROR != 0
       GOTO HANDLE_ERROR

HANDLE_ERROR:

      –  SQL Server 2000 allows to log the errors in the SQL Server Error Log using the sp_altermessage system stored procedures. We can make a particular error to be logged by using the following statement
      exec sp_altermessage @myERROR, ‘WITH_LOG’, ‘TRUE’

     — Calling spErrorHandling store procedure
      exec spErrorHandling @myERROR, ‘usp_insert_notnull’, ‘LINE : 10′    
RETURN @myERROR

This store proceudre give the following error and you can catch in the spErrorHandling 
Msg 515, Level 16, State 2, Procedure usp_insert_notnull, Line 10
Cannot insert the value NULL into column ‘a’, table ‘DataBaseName.dbo.notnull_test’; column does not allow nulls. INSERT fails.

==================
 PLEASE NOTE:
==================

When using ASP.NET, the default security context is the ASPNET (or “Network Service” account, for an application that runs on IIS 6.0)
If we use the “Windows Authentication” and run the applications on IIS, this account will be used to connect to the database.

To send Database mail, users must be a user in the msdb database and a member of the DatabaseMailUserRole database role in the msdb database.

We can open the SQL Server Management Studio, expand Security -> Logins, right-click “machinename\ASPNET” (or NT AUTHORITY\NETWORK SERVICE) and open Properties dialog box. In the User Mapping tab, check the DatabaseMailUserRole under the msdb database.

We can also execute the statement below for the login:

EXEC msdb.dbo.sp_addrolemember @rolename = ‘DatabaseMailUserRole’, @membername = ‘ machinename\ASPNET ‘