· 7 years ago · Nov 05, 2018, 04:04 AM
1USE [master]
2GO
3/****** Object: Database [LiveDashboard] Script Date: 05/11/2018 07:59:48 ******/
4CREATE DATABASE [LiveDashboard]
5 CONTAINMENT = NONE
6 ON PRIMARY
7( NAME = N'LiveDashboard', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\LiveDashboard.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
8 LOG ON
9( NAME = N'LiveDashboard_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\LiveDashboard_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
10GO
11ALTER DATABASE [LiveDashboard] SET COMPATIBILITY_LEVEL = 140
12GO
13IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
14begin
15EXEC [LiveDashboard].[dbo].[sp_fulltext_database] @action = 'enable'
16end
17GO
18ALTER DATABASE [LiveDashboard] SET ANSI_NULL_DEFAULT OFF
19GO
20ALTER DATABASE [LiveDashboard] SET ANSI_NULLS OFF
21GO
22ALTER DATABASE [LiveDashboard] SET ANSI_PADDING OFF
23GO
24ALTER DATABASE [LiveDashboard] SET ANSI_WARNINGS OFF
25GO
26ALTER DATABASE [LiveDashboard] SET ARITHABORT OFF
27GO
28ALTER DATABASE [LiveDashboard] SET AUTO_CLOSE OFF
29GO
30ALTER DATABASE [LiveDashboard] SET AUTO_SHRINK OFF
31GO
32ALTER DATABASE [LiveDashboard] SET AUTO_UPDATE_STATISTICS ON
33GO
34ALTER DATABASE [LiveDashboard] SET CURSOR_CLOSE_ON_COMMIT OFF
35GO
36ALTER DATABASE [LiveDashboard] SET CURSOR_DEFAULT GLOBAL
37GO
38ALTER DATABASE [LiveDashboard] SET CONCAT_NULL_YIELDS_NULL OFF
39GO
40ALTER DATABASE [LiveDashboard] SET NUMERIC_ROUNDABORT OFF
41GO
42ALTER DATABASE [LiveDashboard] SET QUOTED_IDENTIFIER OFF
43GO
44ALTER DATABASE [LiveDashboard] SET RECURSIVE_TRIGGERS OFF
45GO
46ALTER DATABASE [LiveDashboard] SET ENABLE_BROKER
47GO
48ALTER DATABASE [LiveDashboard] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
49GO
50ALTER DATABASE [LiveDashboard] SET DATE_CORRELATION_OPTIMIZATION OFF
51GO
52ALTER DATABASE [LiveDashboard] SET TRUSTWORTHY OFF
53GO
54ALTER DATABASE [LiveDashboard] SET ALLOW_SNAPSHOT_ISOLATION OFF
55GO
56ALTER DATABASE [LiveDashboard] SET PARAMETERIZATION SIMPLE
57GO
58ALTER DATABASE [LiveDashboard] SET READ_COMMITTED_SNAPSHOT OFF
59GO
60ALTER DATABASE [LiveDashboard] SET HONOR_BROKER_PRIORITY OFF
61GO
62ALTER DATABASE [LiveDashboard] SET RECOVERY FULL
63GO
64ALTER DATABASE [LiveDashboard] SET MULTI_USER
65GO
66ALTER DATABASE [LiveDashboard] SET PAGE_VERIFY CHECKSUM
67GO
68ALTER DATABASE [LiveDashboard] SET DB_CHAINING OFF
69GO
70ALTER DATABASE [LiveDashboard] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
71GO
72ALTER DATABASE [LiveDashboard] SET TARGET_RECOVERY_TIME = 60 SECONDS
73GO
74ALTER DATABASE [LiveDashboard] SET DELAYED_DURABILITY = DISABLED
75GO
76EXEC sys.sp_db_vardecimal_storage_format N'LiveDashboard', N'ON'
77GO
78ALTER DATABASE [LiveDashboard] SET QUERY_STORE = OFF
79GO
80USE [LiveDashboard]
81GO
82ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON;
83GO
84ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
85GO
86ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
87GO
88ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
89GO
90ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
91GO
92ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
93GO
94ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
95GO
96ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
97GO
98ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
99GO
100USE [LiveDashboard]
101GO
102/****** Object: Table [dbo].[Logbook] Script Date: 05/11/2018 07:59:48 ******/
103SET ANSI_NULLS ON
104GO
105SET QUOTED_IDENTIFIER ON
106GO
107CREATE TABLE [dbo].[Logbook](
108 [Id] [int] IDENTITY(1,1) NOT NULL,
109 [Status] [varchar](max) NULL,
110 [SentMail] [varchar](max) NULL,
111 [SupplierReply] [varchar](max) NULL,
112 [Intent] [varchar](max) NULL,
113 [ConfirmationMail] [varchar](max) NULL,
114PRIMARY KEY CLUSTERED
115(
116 [Id] ASC
117)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
118) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
119GO
120/****** Object: StoredProcedure [dbo].[dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040_QueueActivationSender] Script Date: 05/11/2018 07:59:48 ******/
121SET ANSI_NULLS ON
122GO
123SET QUOTED_IDENTIFIER ON
124GO
125CREATE PROCEDURE [dbo].[dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040_QueueActivationSender] AS
126BEGIN
127 SET NOCOUNT ON;
128 DECLARE @h AS UNIQUEIDENTIFIER;
129 DECLARE @mt NVARCHAR(200);
130
131 RECEIVE TOP(1) @h = conversation_handle, @mt = message_type_name FROM [dbo].[dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040_Sender];
132
133 IF @mt = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
134 BEGIN
135 END CONVERSATION @h;
136 END
137
138 IF @mt = N'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer' OR @mt = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
139 BEGIN
140
141
142 END CONVERSATION @h;
143
144 DECLARE @conversation_handle UNIQUEIDENTIFIER;
145 DECLARE @schema_id INT;
146 SELECT @schema_id = schema_id FROM sys.schemas WITH (NOLOCK) WHERE name = N'dbo';
147
148
149 IF EXISTS (SELECT * FROM sys.triggers WITH (NOLOCK) WHERE object_id = OBJECT_ID(N'[dbo].[tr_dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040_Sender]')) DROP TRIGGER [dbo].[tr_dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040_Sender];
150
151
152 IF EXISTS (SELECT * FROM sys.service_queues WITH (NOLOCK) WHERE schema_id = @schema_id AND name = N'dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040_Sender') EXEC (N'ALTER QUEUE [dbo].[dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040_Sender] WITH ACTIVATION (STATUS = OFF)');
153
154
155 SELECT conversation_handle INTO #Conversations FROM sys.conversation_endpoints WITH (NOLOCK) WHERE far_service LIKE N'dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040_%' ORDER BY is_initiator ASC;
156 DECLARE conversation_cursor CURSOR FAST_FORWARD FOR SELECT conversation_handle FROM #Conversations;
157 OPEN conversation_cursor;
158 FETCH NEXT FROM conversation_cursor INTO @conversation_handle;
159 WHILE @@FETCH_STATUS = 0
160 BEGIN
161 END CONVERSATION @conversation_handle WITH CLEANUP;
162 FETCH NEXT FROM conversation_cursor INTO @conversation_handle;
163 END
164 CLOSE conversation_cursor;
165 DEALLOCATE conversation_cursor;
166 DROP TABLE #Conversations;
167
168
169 IF EXISTS (SELECT * FROM sys.services WITH (NOLOCK) WHERE name = N'dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040_Receiver') DROP SERVICE [dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040_Receiver];
170
171 IF EXISTS (SELECT * FROM sys.services WITH (NOLOCK) WHERE name = N'dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040_Sender') DROP SERVICE [dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040_Sender];
172
173
174 IF EXISTS (SELECT * FROM sys.service_queues WITH (NOLOCK) WHERE schema_id = @schema_id AND name = N'dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040_Receiver') DROP QUEUE [dbo].[dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040_Receiver];
175
176 IF EXISTS (SELECT * FROM sys.service_queues WITH (NOLOCK) WHERE schema_id = @schema_id AND name = N'dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040_Sender') DROP QUEUE [dbo].[dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040_Sender];
177
178
179 IF EXISTS (SELECT * FROM sys.service_contracts WITH (NOLOCK) WHERE name = N'dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040') DROP CONTRACT [dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040];
180
181 IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040/StartMessage/Insert') DROP MESSAGE TYPE [dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040/StartMessage/Insert];
182IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040/StartMessage/Update') DROP MESSAGE TYPE [dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040/StartMessage/Update];
183IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040/StartMessage/Delete') DROP MESSAGE TYPE [dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040/StartMessage/Delete];
184IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040/id') DROP MESSAGE TYPE [dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040/id];
185IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040/MessageId') DROP MESSAGE TYPE [dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040/MessageId];
186IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040/StepId') DROP MESSAGE TYPE [dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040/StepId];
187IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040/Contents') DROP MESSAGE TYPE [dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040/Contents];
188IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040/EndMessage') DROP MESSAGE TYPE [dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040/EndMessage];
189
190
191 IF EXISTS (SELECT * FROM sys.objects WITH (NOLOCK) WHERE schema_id = @schema_id AND name = N'dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040_QueueActivationSender') DROP PROCEDURE [dbo].[dbo_Logbook_0fcd496f-2880-46ba-b7f3-3be64e4bc040_QueueActivationSender];
192
193
194 END
195END
196GO
197/****** Object: StoredProcedure [dbo].[dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54_QueueActivationSender] Script Date: 05/11/2018 07:59:48 ******/
198SET ANSI_NULLS ON
199GO
200SET QUOTED_IDENTIFIER ON
201GO
202CREATE PROCEDURE [dbo].[dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54_QueueActivationSender] AS
203BEGIN
204 SET NOCOUNT ON;
205 DECLARE @h AS UNIQUEIDENTIFIER;
206 DECLARE @mt NVARCHAR(200);
207
208 RECEIVE TOP(1) @h = conversation_handle, @mt = message_type_name FROM [dbo].[dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54_Sender];
209
210 IF @mt = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
211 BEGIN
212 END CONVERSATION @h;
213 END
214
215 IF @mt = N'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer' OR @mt = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
216 BEGIN
217
218
219 END CONVERSATION @h;
220
221 DECLARE @conversation_handle UNIQUEIDENTIFIER;
222 DECLARE @schema_id INT;
223 SELECT @schema_id = schema_id FROM sys.schemas WITH (NOLOCK) WHERE name = N'dbo';
224
225
226 IF EXISTS (SELECT * FROM sys.triggers WITH (NOLOCK) WHERE object_id = OBJECT_ID(N'[dbo].[tr_dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54_Sender]')) DROP TRIGGER [dbo].[tr_dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54_Sender];
227
228
229 IF EXISTS (SELECT * FROM sys.service_queues WITH (NOLOCK) WHERE schema_id = @schema_id AND name = N'dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54_Sender') EXEC (N'ALTER QUEUE [dbo].[dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54_Sender] WITH ACTIVATION (STATUS = OFF)');
230
231
232 SELECT conversation_handle INTO #Conversations FROM sys.conversation_endpoints WITH (NOLOCK) WHERE far_service LIKE N'dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54_%' ORDER BY is_initiator ASC;
233 DECLARE conversation_cursor CURSOR FAST_FORWARD FOR SELECT conversation_handle FROM #Conversations;
234 OPEN conversation_cursor;
235 FETCH NEXT FROM conversation_cursor INTO @conversation_handle;
236 WHILE @@FETCH_STATUS = 0
237 BEGIN
238 END CONVERSATION @conversation_handle WITH CLEANUP;
239 FETCH NEXT FROM conversation_cursor INTO @conversation_handle;
240 END
241 CLOSE conversation_cursor;
242 DEALLOCATE conversation_cursor;
243 DROP TABLE #Conversations;
244
245
246 IF EXISTS (SELECT * FROM sys.services WITH (NOLOCK) WHERE name = N'dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54_Receiver') DROP SERVICE [dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54_Receiver];
247
248 IF EXISTS (SELECT * FROM sys.services WITH (NOLOCK) WHERE name = N'dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54_Sender') DROP SERVICE [dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54_Sender];
249
250
251 IF EXISTS (SELECT * FROM sys.service_queues WITH (NOLOCK) WHERE schema_id = @schema_id AND name = N'dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54_Receiver') DROP QUEUE [dbo].[dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54_Receiver];
252
253 IF EXISTS (SELECT * FROM sys.service_queues WITH (NOLOCK) WHERE schema_id = @schema_id AND name = N'dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54_Sender') DROP QUEUE [dbo].[dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54_Sender];
254
255
256 IF EXISTS (SELECT * FROM sys.service_contracts WITH (NOLOCK) WHERE name = N'dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54') DROP CONTRACT [dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54];
257
258 IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54/StartMessage/Insert') DROP MESSAGE TYPE [dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54/StartMessage/Insert];
259IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54/StartMessage/Update') DROP MESSAGE TYPE [dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54/StartMessage/Update];
260IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54/StartMessage/Delete') DROP MESSAGE TYPE [dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54/StartMessage/Delete];
261IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54/id') DROP MESSAGE TYPE [dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54/id];
262IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54/MessageId') DROP MESSAGE TYPE [dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54/MessageId];
263IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54/StepId') DROP MESSAGE TYPE [dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54/StepId];
264IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54/Contents') DROP MESSAGE TYPE [dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54/Contents];
265IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54/EndMessage') DROP MESSAGE TYPE [dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54/EndMessage];
266
267
268 IF EXISTS (SELECT * FROM sys.objects WITH (NOLOCK) WHERE schema_id = @schema_id AND name = N'dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54_QueueActivationSender') DROP PROCEDURE [dbo].[dbo_Logbook_58647f7a-751e-47c4-8104-787a4908ea54_QueueActivationSender];
269
270
271 END
272END
273GO
274USE [master]
275GO
276ALTER DATABASE [LiveDashboard] SET READ_WRITE
277GO