142 lines
4.1 KiB
Transact-SQL
142 lines
4.1 KiB
Transact-SQL
IF OBJECT_ID('dev.sp_Global_GenerateAsyncAuditAuditPocessing') IS NOT NULL BEGIN
|
|
DROP PROCEDURE dev.sp_Global_GenerateAsyncAuditAuditPocessing
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE dev.sp_Global_GenerateAsyncAuditAuditPocessing
|
|
AS
|
|
BEGIN
|
|
DECLARE @sql nvarchar(max),
|
|
@if nvarchar(max) = ''
|
|
|
|
SELECT @if += '
|
|
IF @tableName = ''' + TABLE_NAME + ''' BEGIN
|
|
EXEC auditLog.usp_AsyncAudit' + UPPER(SUBSTRING(TABLE_NAME, 3, 100)) + ' @message_body;
|
|
END'
|
|
FROM (
|
|
SELECT DISTINCT TABLE_NAME
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE COLUMN_NAME IN ('C_TANEVID', 'C_ALTANEVID')
|
|
AND TABLE_NAME NOT IN ('T_ENTITYHISTORY', 'T_ENTITYATTRIBUTEHISTORY', 'T_LOG', 'T_GLOBALLOCK', 'T_EVFOLYAMTIPUS_OKTATASINEVELE')
|
|
AND TABLE_NAME IN (
|
|
SELECT t.name
|
|
FROM sys.tables t
|
|
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
|
|
WHERE s.name ='dbo'
|
|
)
|
|
) x
|
|
|
|
SET @sql = N'
|
|
IF OBJECT_ID(''auditLog.usp_AuditProcessing'') IS NOT NULL BEGIN
|
|
DROP PROCEDURE auditLog.usp_AuditProcessing
|
|
END
|
|
'
|
|
|
|
EXEC sys.sp_executesql @sql
|
|
|
|
SET @sql = N'
|
|
CREATE PROCEDURE auditLog.usp_AuditProcessing
|
|
AS
|
|
BEGIN
|
|
DECLARE @message_type varchar(100)
|
|
,@dialog uniqueidentifier
|
|
,@message_body nvarchar(max)
|
|
,@tableName nvarchar(80)
|
|
,@conversation_handle UNIQUEIDENTIFIER
|
|
,@message_type_name sysname;
|
|
|
|
WHILE (1 = 1) BEGIN
|
|
BEGIN TRANSACTION;
|
|
WAITFOR
|
|
(RECEIVE TOP(1)
|
|
@conversation_handle = conversation_handle,
|
|
@message_body = message_body,
|
|
@message_type_name = message_type_name
|
|
FROM auditlog.AuditQueue
|
|
), TIMEOUT 1000;
|
|
--INSERT INTO auditlog.Test (xmlData) VALUES (@message_body)
|
|
IF (@@ROWCOUNT = 0)
|
|
BEGIN
|
|
ROLLBACK TRANSACTION;
|
|
BREAK;
|
|
END
|
|
BEGIN TRY
|
|
-- Handle the Message
|
|
IF @message_type_name = N''AuditMessage'' BEGIN
|
|
SELECT @tableName = CAST(@message_body AS xml).value(''/audit[1]/@name'', ''nvarchar(80)'')
|
|
|
|
' + @if + '
|
|
END
|
|
|
|
ELSE IF @message_type_name = ''EndOfStream''
|
|
BEGIN
|
|
-- initiator is signaling end of message stream: end the dialog
|
|
END CONVERSATION @conversation_handle;
|
|
END
|
|
|
|
-- If end dialog message, end the dialog
|
|
ELSE IF @message_type_name = N''http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog''
|
|
BEGIN
|
|
END CONVERSATION @conversation_handle;
|
|
END
|
|
|
|
ELSE IF @message_type_name = N''DEFAULT''
|
|
BEGIN
|
|
END CONVERSATION @conversation_handle;
|
|
END
|
|
|
|
-- If error message, log and end conversation
|
|
ELSE IF @message_type_name = N''http://schemas.microsoft.com/SQL/ServiceBroker/Error'' BEGIN
|
|
DECLARE @error INT;
|
|
DECLARE @description NVARCHAR(4000);
|
|
-- Pull the error code and description from the doc
|
|
WITH XMLNAMESPACES (''http://schemas.microsoft.com/SQL/ServiceBroker/Error'' AS ssb)
|
|
SELECT
|
|
@error = CAST(@message_body AS xml).value(''(//ssb:Error/ssb:Code)[1]'', ''INT''),
|
|
@description = CAST(@message_body AS xml).value(''(//ssb:Error/ssb:Description)[1]'', ''NVARCHAR(4000)'');
|
|
|
|
|
|
--INSERT INTO [dbo].[ErrorLog] (ConversationHandle, message_body, message_type_name)
|
|
--SELECT @conversation_handle, CAST(@message_body AS nvarchar(max)), @message_type_name;
|
|
|
|
RAISERROR(N''Received error Code:%i Description:"%s"'', 16, 1, @error, @description) WITH LOG;
|
|
|
|
-- Now that we handled the error logging cleanup
|
|
END CONVERSATION @conversation_handle;
|
|
END
|
|
|
|
-- If everything succeeded correctly commit the transaction
|
|
COMMIT TRANSACTION;
|
|
|
|
END TRY
|
|
|
|
-- If we have any error get caught we want to throw the error
|
|
-- and then rollback the transaction to put the message on the queue
|
|
BEGIN CATCH
|
|
IF (ERROR_NUMBER() = 1205)
|
|
BEGIN
|
|
ROLLBACK TRANSACTION;
|
|
EXEC [auditlog].[uspLogError];
|
|
CONTINUE;
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
ROLLBACK TRANSACTION --ReceiveSavePoint;
|
|
|
|
EXEC [auditlog].[uspLogError];
|
|
|
|
INSERT INTO [auditlog].[AuditServiceFailedItems]
|
|
([conversation_handle], [message_body])
|
|
VALUES (@conversation_handle, @message_body);
|
|
|
|
THROW;
|
|
END
|
|
END CATCH
|
|
END
|
|
END
|
|
'
|
|
EXEC sys.sp_executesql @sql
|
|
|
|
END
|
|
GO
|