first thing you need to enable SQL logging through the IAS snap in
then you need to create a SQL script ( based on your needs) that will create
the SQL database and the fields
here is an example of the sql script, it meant just as an example you need
to modify it based on your needs:
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE (
id = OBJECT_ID('dbo.event_data') AND
OBJECTPROPERTY(id, 'IsUserTable') = 1
)
)
DROP TABLE dbo.event_data
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE (
id = OBJECT_ID('dbo.event_main') AND
OBJECTPROPERTY(id, 'IsUserTable') = 1
)
)
DROP TABLE dbo.event_main
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE (
id = OBJECT_ID('dbo.report_event') AND
OBJECTPROPERTY(id, 'IsProcedure') = 1
)
)
DROP PROCEDURE dbo.report_event
GO
CREATE TABLE dbo.event_main (
event_id uniqueidentifier NOT NULL
PRIMARY KEY CLUSTERED,
record_timestamp datetime NOT NULL
)
GO
CREATE TABLE dbo.event_data (
event_id uniqueidentifier NOT NULL
REFERENCES event_main (event_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
attribute_type nvarchar (64) NOT NULL,
attribute_value nvarchar (1024) NULL
)
GO
CREATE PROCEDURE dbo.report_event
@doc ntext
AS
SET NOCOUNT ON
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
DECLARE @event_id uniqueidentifier
SET @event_id = NEWID()
DECLARE @record_timestamp datetime
SET @record_timestamp = GETUTCDATE()
BEGIN TRANSACTION
INSERT dbo.event_main VALUES (
@event_id,
@record_timestamp
)
INSERT dbo.event_data
SELECT
@event_id,
attribute_type,
attribute_value
FROM OPENXML(@idoc, '/Event/*')
WITH (
attribute_type varchar(64) '@mp:localname',
attribute_value nvarchar(1024) 'child::text()'
)
COMMIT TRANSACTION
EXEC sp_xml_removedocument @idoc
GO
