AS2 SQL Script

If you are not using the MSDE database included with the EFT Server installer, refer to Using a SQL Database as the Auditing Database for information about using SQL as the database. Administrators that prefer using command-line tools can use oSQL to create the auditing database and tables. Refer to the procedure in Alternative Method for Creating EFT Database Tables on SQL for the procedure for creating EFT Server database tables on SQL.

The SQL script below adds the necessary tables to a SQL database for monitoring AS2-related transactions.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tbl_AS2Actions_tbl_AS2Transaction]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tbl_AS2Actions] DROP CONSTRAINT FK_tbl_AS2Actions_tbl_AS2Transaction
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tbl_AS2Files_tbl_AS2Transaction]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tbl_AS2Files] DROP CONSTRAINT FK_tbl_AS2Files_tbl_AS2Transaction
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tbl_AS2Actions_tbl_AS2Files]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tbl_AS2Actions] DROP CONSTRAINT FK_tbl_AS2Actions_tbl_AS2Files
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_AS2Actions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_AS2Actions]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_AS2Files]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_AS2Files]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_AS2Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_AS2Transactions]
GO
CREATE TABLE [dbo].[tbl_AS2Transactions] (
	[TransactionID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[MIC] [varchar] (50) NULL ,
	[StartTime] [datetime] NULL ,
	[CompleteTime] [datetime] NULL ,
	[Direction] [numeric](18, 0) NULL ,
	[MDN] [varchar] (5000) NULL ,
	[MDNSignatureType] [varchar] (50) NULL ,
	[MDNType] [numeric](18, 0) NULL ,
	[LocalHost] [varchar] (300) NULL ,
	[RemoteHost] [varchar] (300) NULL ,
	[TransactionStatus] [numeric](18, 0) NULL ,
	[SiteName] [varchar] (300) NULL ,
	[FromAS2ID] [varchar] (300) NULL ,
	[ToAS2ID] [varchar] (300) NULL ,
	[ErrorCode] [varchar] (300) NULL ,
	[PayloadSignatureType] [varchar] (50) NULL ,
	[PayloadEncryptionType] [varchar] (50) NULL ,
	[PayloadHeaders] [varchar] (5000) NULL ,
	[MDNAS2MessageID] [varchar] (300) NULL ,
	[PayloadAS2MessageID] [varchar] (300) NULL ,
	[MDNHTTPResponse] [varchar] (300) NULL 
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_AS2Files] (
	[FIleID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[TransactionID] [numeric](18, 0) NULL ,
	[FileName] [varchar] (50) NULL ,
	[ContentType] [varchar] (50) NULL ,
	[LocalPath] [varchar] (50) NULL ,
	[RemotePath] [varchar] (50) NULL 
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_AS2Actions] (
	[ActionID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[TransactionID] [numeric](18, 0) NULL ,
	[ActionStatus] [numeric](18, 0) NULL ,
	[ActionType] [numeric](18, 0) NULL ,
	[ErrorCode] [varchar] (1000) NULL ,
	[TimeStamp] [datetime] NULL ,
	[FileID] [numeric](18, 0) NULL 
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_AS2Transactions] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_AS2Transaction] PRIMARY KEY  CLUSTERED 
	(
		[TransactionID]
	)  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[tbl_AS2Files] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_AS2Files] PRIMARY KEY  CLUSTERED 
	(
		[FIleID]
	)  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[tbl_AS2Actions] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_AS2Actions] PRIMARY KEY  CLUSTERED 
	(
		[ActionID]
	)  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[tbl_AS2Files] ADD 
	CONSTRAINT [FK_tbl_AS2Files_tbl_AS2Transaction] FOREIGN KEY 
	(
		[TransactionID]
	) REFERENCES [dbo].[tbl_AS2Transactions] (
		[TransactionID]
	)
GO
ALTER TABLE [dbo].[tbl_AS2Actions] ADD 
	CONSTRAINT [FK_tbl_AS2Actions_tbl_AS2Files] FOREIGN KEY 
	(
		[FileID]
	) REFERENCES [dbo].[tbl_AS2Files] (
		[FIleID]
	),
	CONSTRAINT [FK_tbl_AS2Actions_tbl_AS2Transaction] FOREIGN KEY 
	(
		[TransactionID]
	) REFERENCES [dbo].[tbl_AS2Transactions] (
		[TransactionID]
	)
GO