Skip to content

Instantly share code, notes, and snippets.

@lionofdezert
Created December 27, 2012 10:42
Show Gist options
  • Save lionofdezert/4387280 to your computer and use it in GitHub Desktop.
Save lionofdezert/4387280 to your computer and use it in GitHub Desktop.
DDL Changes Log additional changes in script to get client IP
GO
USE [ConnectDBA]
GO
/*Alter table to add new column to hold client machine IP*/
ALTER TABLE [dbo].[DDLChangeLog] ADD [ClientMachineIP] [varchar](20) NULL
GO
--Create a stored procedure which will hold logic, how to get data from queue and insert to DDLChangeLog table.
CREATE PROCEDURE [ConnectDBA_Queue_EventNotificatier]
WITH EXECUTE AS OWNER
AS
DECLARE @message_body XML
WHILE ( 1 = 1 )
BEGIN
BEGIN TRANSACTION
-- Receive the next available message FROM the queue
WAITFOR ( RECEIVE TOP ( 1 ) -- just handle one message at a time
@message_body = CONVERT(XML, CONVERT(NVARCHAR(MAX), message_body))
FROM dbo.[ConnectDBA_EventNotificationQueue] ), TIMEOUT 1000 -- if the queue is empty for one second, give UPDATE and go away
-- If we didn't get anything, bail out
IF ( @@ROWCOUNT = 0 )
BEGIN
ROLLBACK TRANSACTION
BREAK
END
INSERT INTO DDLChangeLog
SELECT @message_body.value('(/EVENT_INSTANCE/EventType)[1]',
'varchar(128)') AS EventType,
CONVERT(DATETIME, @message_body.value('(/EVENT_INSTANCE/PostTime)[1]', 'varchar(128)'))
AS PostTime,
@message_body.value('(/EVENT_INSTANCE/ServerName)[1]',
'varchar(128)') AS ServerName,
@message_body.value('(/EVENT_INSTANCE/LoginName)[1]',
'varchar(128)') AS LoginName,
@message_body.value('(/EVENT_INSTANCE/UserName)[1]',
'varchar(128)') AS UserName,
@message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]',
'varchar(128)') AS DatabaseName,
@message_body.value('(/EVENT_INSTANCE/SchemaName)[1]',
'varchar(128)') AS SchemaName,
@message_body.value('(/EVENT_INSTANCE/ObjectName)[1]',
'varchar(128)') AS ObjectName,
@message_body.value('(/EVENT_INSTANCE/ObjectType)[1]',
'varchar(128)') AS ObjectType,
@message_body.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'nvarchar(max)') AS TSQLCommand,
--- To get client machine IP
(SELECT client_net_address
FROM sys.dm_exec_connections
WHERE Session_id =@message_body.value('(/EVENT_INSTANCE/SPID)[1]','varchar(100)'))AS ClientMachine
COMMIT TRANSACTION
END
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment