MailAlertServer/SQL_Create_Scripts.sql at main · AppendAutomation/MailAlertServer · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
USE [MailAlertDB]
GO
/****** Object:  Table [dbo].[MailAlerts]    Script Date: 5/7/2020 9:55:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MailAlerts](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Enabled] [bit] NOT NULL CONSTRAINT [DF_MailAlerts_Enabled]  DEFAULT ((1)),
	[Query] [varchar](3000) NOT NULL,
	[Subject] [varchar](100) NOT NULL,
	[Message] [varchar](3000) NULL,
	[Recipients] [varchar](1000) NOT NULL,
	[RecipientsBCC] [varchar](1000) NULL,
	[RecipientsCC] [varchar](1000) NULL,
	[LastTrue] [bit] NOT NULL CONSTRAINT [DF_MailAlerts_LastTrue]  DEFAULT ((0)),
	[LastTrueTime] [datetime] NULL,
	[LastCheckTime] [datetime] NULL,
	[MinutesResend] [int] NULL,
	[MessageQuery] [varchar](3000) NULL,
	[LastStatus] [varchar](300) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[MailConfig]    Script Date: 5/7/2020 9:55:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MailConfig](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ServerURL] [varchar](100) NOT NULL,
	[ServerPort] [int] NOT NULL,
	[Username] [varchar](50) NOT NULL,
	[Password] [varchar](150) NOT NULL,
	[SenderName] [varchar](50) NOT NULL,
	[SenderAddress] [varchar](50) NOT NULL,
	[ServiceCommand] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[MailRequests]    Script Date: 5/7/2020 9:55:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MailRequests](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Subject] [varchar](100) NOT NULL,
	[Message] [varchar](max) NULL,
	[RecipientTo] [varchar](1000) NOT NULL,
	[RecipientBCC] [varchar](1000) NULL,
	[RecipientCC] [varchar](1000) NULL,
	[Sent] [bit] NOT NULL CONSTRAINT [DF_MailRequests_Sent]  DEFAULT ((0)),
	[CreatedTime] [datetime] NOT NULL CONSTRAINT [DF_MailRequests_CreatedTime]  DEFAULT (getdate()),
	[SentTime] [datetime] NULL,
	[LastStatus] [varchar](500) NULL CONSTRAINT [DF_MailRequests_LastStatus]  DEFAULT ('Request Send')
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[MailAlerts] ON

INSERT [dbo].[MailAlerts] ([ID], [Enabled], [Query], [Subject], [Message], [Recipients], [RecipientsCC], [RecipientsBCC], [LastTrue], [LastTrueTime], [LastCheckTime], [MinutesResend], [MessageQuery], [LastStatus])
VALUES (1, 0, N'SELECT 1 AS SendMail', N'Test Mail', N'Test Mail', N'user@domain.com', N'', N'', 0, CAST(N'2019-12-20 13:48:38.367' AS DateTime), CAST(N'2020-03-31 16:10:42.813' AS DateTime), NULL, NULL, N'OK')
INSERT [dbo].[MailAlerts] ([ID], [Enabled], [Query], [Subject], [Message], [Recipients], [RecipientsCC], [RecipientsBCC], [LastTrue], [LastTrueTime], [LastCheckTime], [MinutesResend], [MessageQuery], [LastStatus])
VALUES (2, 0, N'SELECT CONVERT(varchar(5), getdate(), 108) As Time,
	CASE
		WHEN CONVERT(varchar(4), getdate(), 108) = ''09:0'' THEN 1
		ELSE 0
	END	AS SendMail
', N'24 Hour Mail Service Check In', N'Mail service is running.', N'user@domain.com', N'', N'', 0, CAST(N'2020-03-31 09:00:40.087' AS DateTime), CAST(N'2020-03-31 16:10:42.813' AS DateTime), NULL, NULL, N'OK')
SET IDENTITY_INSERT [dbo].[MailAlerts] OFF
SET IDENTITY_INSERT [dbo].[MailConfig] ON

INSERT [dbo].[MailConfig] ([ID], [ServerURL], [ServerPort], [Username], [Password], [SenderName], [SenderAddress], [ServiceCommand]) VALUES (2, N'smtp.example.com', 465, N'alerts@example.com', N'YourPasswordHere', N'Alert-Server', N'alerts@example.com', N'')
SET IDENTITY_INSERT [dbo].[MailConfig] OFF