Posted by: jwalin on: February 27, 2009
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 ‘
