USE master
GO
IF OBJECT_ID('sp_LogError') IS NULL EXEC ('CREATE PROCEDURE sp_LogError AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_LogError
@ErrorTable VARCHAR(128) = NULL OUTPUT,
@TruncateTable BIT = 0
AS
DECLARE @ErrorNumber INT = @@ERROR,
@InputBuffer VARCHAR(4000),
@SQL NVARCHAR(MAX)
IF ISNULL(@ErrorNumber, 0) = 0
RETURN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET @ErrorTable = CAST(CONTEXT_INFO() AS VARCHAR(128))
DECLARE @Buffer TABLE (EventType VARCHAR(30),
PARAMETERS INT,
EventInfo VARCHAR(4000))
INSERT @Buffer
EXEC ('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS')
SELECT @InputBuffer = ei
FROM @Buffer
CROSS APPLY (SELECT CHARINDEX ('EXEC sp_LogError', EventInfo) AS ci) c1
CROSS APPLY (SELECT CASE WHEN ci> 0 THEN ci ELSE CHARINDEX ('EXEC dbo.sp_LogError', EventInfo) END AS ci) c2
CROSS APPLY (SELECT CASE WHEN c2.ci > 0 THEN SUBSTRING (EventInfo, 1, c2.ci- 1) ELSE EventInfo END AS ei) e
IF @ErrorTable IS NULL
BEGIN
SET @ErrorTable = RTRIM('##ERROR_' + RIGHT(REPLACE(CAST(NEWID() AS VARCHAR(128)), '-', ''), 8))
DECLARE @ErrorTableBinary VARBINARY(128)= CAST(@ErrorTable AS VARBINARY(128))
SET CONTEXT_INFO @ErrorTableBinary
END
IF NOT EXISTS ( SELECT *
FROM tempdb.sys.tables (NOLOCK)
WHERE name = @ErrorTable )
EXEC('CREATE TABLE ' + @ErrorTable + ' (
Id INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
ErrorNumber int NULL,
InputBuffer varchar(4000) NULL,
DateAdded DATETIME NOT NULL DEFAULT (GETDATE()))
')
ELSE
IF @TruncateTable = 1
EXEC('TRUNCATE TABLE ' + @ErrorTable)
SET @SQL = 'INSERT ' + @ErrorTable + +'(ErrorNumber, InputBuffer)
VALUES(@ErrorNumber, @INPUTBUFFER)'
EXEC sys.sp_executesql
@SQL,
N'@ErrorNumber INT, @InputBuffer VARCHAR(4000)',
@ErrorNumber,
@InputBuffer
GO
EXEC sys.sp_ms_marksystemobject sp_LogError
GO