· 8 years ago · Aug 29, 2018, 02:30 AM
1/*
2Deployment script for Date proof
3
4This code was generated by a tool.
5Changes to this file may cause incorrect behavior and will be lost if
6the code is regenerated.
7*/
8
9GO
10SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
11
12SET NUMERIC_ROUNDABORT OFF;
13
14
15GO
16:setvar DatabaseName "DateProof"
17:setvar DefaultFilePrefix "DateProof"
18:setvar DefaultDataPath ""
19:setvar DefaultLogPath ""
20
21GO
22:on error exit
23GO
24/*
25Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
26To re-enable the script after enabling SQLCMD mode, execute the following:
27SET NOEXEC OFF;
28*/
29:setvar __IsSqlCmdEnabled "True"
30GO
31IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
32 BEGIN
33 PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
34 SET NOEXEC ON;
35 END
36
37
38GO
39USE [master];
40
41
42GO
43
44IF (DB_ID(N'$(DatabaseName)') IS NOT NULL)
45BEGIN
46 ALTER DATABASE [$(DatabaseName)]
47 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
48 DROP DATABASE [$(DatabaseName)];
49END
50
51GO
52PRINT N'Creating $(DatabaseName)...'
53GO
54CREATE DATABASE [$(DatabaseName)] COLLATE SQL_Latin1_General_CP1_CI_AS
55GO
56USE [$(DatabaseName)];
57
58
59GO
60IF EXISTS (SELECT 1
61 FROM [master].[dbo].[sysdatabases]
62 WHERE [name] = N'$(DatabaseName)')
63 BEGIN
64 ALTER DATABASE [$(DatabaseName)]
65 SET ANSI_NULLS ON,
66 ANSI_PADDING ON,
67 ANSI_WARNINGS ON,
68 ARITHABORT OFF,
69 CONCAT_NULL_YIELDS_NULL OFF,
70 NUMERIC_ROUNDABORT OFF,
71 QUOTED_IDENTIFIER ON,
72 ANSI_NULL_DEFAULT ON,
73 CURSOR_DEFAULT GLOBAL,
74 RECOVERY SIMPLE,
75 CURSOR_CLOSE_ON_COMMIT OFF,
76 AUTO_CREATE_STATISTICS ON,
77 AUTO_SHRINK OFF,
78 AUTO_UPDATE_STATISTICS ON,
79 RECURSIVE_TRIGGERS OFF
80 WITH ROLLBACK IMMEDIATE;
81 ALTER DATABASE [$(DatabaseName)]
82 SET AUTO_CLOSE OFF
83 WITH ROLLBACK IMMEDIATE;
84 END
85
86
87GO
88IF EXISTS (SELECT 1
89 FROM [master].[dbo].[sysdatabases]
90 WHERE [name] = N'$(DatabaseName)')
91 BEGIN
92 ALTER DATABASE [$(DatabaseName)]
93 SET ALLOW_SNAPSHOT_ISOLATION OFF;
94 END
95
96
97GO
98IF EXISTS (SELECT 1
99 FROM [master].[dbo].[sysdatabases]
100 WHERE [name] = N'$(DatabaseName)')
101 BEGIN
102 ALTER DATABASE [$(DatabaseName)]
103 SET READ_COMMITTED_SNAPSHOT OFF
104 WITH ROLLBACK IMMEDIATE;
105 END
106
107
108GO
109IF EXISTS (SELECT 1
110 FROM [master].[dbo].[sysdatabases]
111 WHERE [name] = N'$(DatabaseName)')
112 BEGIN
113 ALTER DATABASE [$(DatabaseName)]
114 SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
115 PAGE_VERIFY CHECKSUM,
116 DATE_CORRELATION_OPTIMIZATION OFF,
117 ENABLE_BROKER,
118 PARAMETERIZATION SIMPLE,
119 SUPPLEMENTAL_LOGGING OFF
120 WITH ROLLBACK IMMEDIATE;
121 END
122
123
124GO
125IF IS_SRVROLEMEMBER(N'sysadmin') = 1
126 BEGIN
127 IF EXISTS (SELECT 1
128 FROM [master].[dbo].[sysdatabases]
129 WHERE [name] = N'$(DatabaseName)')
130 BEGIN
131 EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
132 SET TRUSTWORTHY OFF,
133 DB_CHAINING OFF
134 WITH ROLLBACK IMMEDIATE';
135 END
136 END
137ELSE
138 BEGIN
139 PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
140 END
141
142
143GO
144IF IS_SRVROLEMEMBER(N'sysadmin') = 1
145 BEGIN
146 IF EXISTS (SELECT 1
147 FROM [master].[dbo].[sysdatabases]
148 WHERE [name] = N'$(DatabaseName)')
149 BEGIN
150 EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
151 SET HONOR_BROKER_PRIORITY OFF
152 WITH ROLLBACK IMMEDIATE';
153 END
154 END
155ELSE
156 BEGIN
157 PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
158 END
159
160
161GO
162ALTER DATABASE [$(DatabaseName)]
163 SET TARGET_RECOVERY_TIME = 60 SECONDS
164 WITH ROLLBACK IMMEDIATE;
165
166
167GO
168IF EXISTS (SELECT 1
169 FROM [master].[dbo].[sysdatabases]
170 WHERE [name] = N'$(DatabaseName)')
171 BEGIN
172 ALTER DATABASE [$(DatabaseName)]
173 SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF),
174 CONTAINMENT = NONE
175 WITH ROLLBACK IMMEDIATE;
176 END
177
178
179GO
180IF EXISTS (SELECT 1
181 FROM [master].[dbo].[sysdatabases]
182 WHERE [name] = N'$(DatabaseName)')
183 BEGIN
184 ALTER DATABASE [$(DatabaseName)]
185 SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF),
186 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = OFF,
187 DELAYED_DURABILITY = DISABLED
188 WITH ROLLBACK IMMEDIATE;
189 END
190
191
192GO
193IF EXISTS (SELECT 1
194 FROM [master].[dbo].[sysdatabases]
195 WHERE [name] = N'$(DatabaseName)')
196 BEGIN
197 ALTER DATABASE [$(DatabaseName)]
198 SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL, DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_PLANS_PER_QUERY = 200, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), MAX_STORAGE_SIZE_MB = 100)
199 WITH ROLLBACK IMMEDIATE;
200 END
201
202
203GO
204IF EXISTS (SELECT 1
205 FROM [master].[dbo].[sysdatabases]
206 WHERE [name] = N'$(DatabaseName)')
207 BEGIN
208 ALTER DATABASE [$(DatabaseName)]
209 SET QUERY_STORE = OFF
210 WITH ROLLBACK IMMEDIATE;
211 END
212
213
214GO
215IF EXISTS (SELECT 1
216 FROM [master].[dbo].[sysdatabases]
217 WHERE [name] = N'$(DatabaseName)')
218 BEGIN
219 ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
220 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
221 ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
222 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
223 ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
224 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
225 ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
226 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
227 END
228
229
230GO
231IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
232 EXECUTE sp_fulltext_database 'enable';
233
234
235GO
236PRINT N'Creating [dbo].[dates]...';
237
238
239GO
240CREATE TABLE [dbo].[dates] (
241 [dt] DATETIME NOT NULL,
242 [dtOffset] DATETIMEOFFSET (7) NOT NULL,
243 [Description] NVARCHAR (50) NULL
244);
245
246
247GO
248PRINT N'Creating [dbo].[TestDates]...';
249
250
251GO
252CREATE PROCEDURE [dbo].[TestDates]
253
254AS
255 DECLARE @dt DATETIME = GETDATE()--cuurent timezone could be eastern who knows
256 DECLARE @tz DATETIMEOFFSET(7) = SYSDATETIMEOFFSET() --same as GETDATE but also stores the timezone info
257
258 SELECT @dt AS 'GETDATE',@tz AS 'Offset'
259 TRUNCATE TABLE dates
260 insert INTO dbo.dates
261 (
262 dt,
263 dtOffset,
264 Description
265 )
266 VALUES(
267 @dt,
268 @tz,
269 N'Current date'
270 )
271
272 SELECT @dt=CONVERT(DATETIME, '2018-3-11 7:00:00')
273 insert INTO dbo.dates
274 (
275 dt,
276 dtOffset,
277 Description
278 )
279 VALUES(
280 @dt,
281 CONVERT (DATETIMEOFFSET, @dt AT TIME ZONE 'Eastern Standard Time'),
282 N'Pre DST')
283
284 SELECT @dt=CONVERT(DATETIME, '2018-3-10 7:00:00')
285 insert INTO dbo.dates
286 (
287 dt,
288 dtOffset,
289 Description
290 )
291 VALUES(
292 @dt,
293 CONVERT (DATETIMEOFFSET, @dt AT TIME ZONE 'Eastern Standard Time'),
294 N'Post DST')
295
296
297
298 SELECT description, dt AS 'DATETIME', dtOffset AS 'DATETIMEOFFSET',
299 dt AT TIME ZONE 'US Eastern Standard Time' AS 'US Eastern DT', dt AT TIME ZONE 'Eastern Standard Time' AS 'Eastern DT', dt AT TIME ZONE 'Hawaiian Standard Time' AS 'Hawaiian Standard Time DT' ,
300 dtOffset AT TIME ZONE 'US Eastern Standard Time' AS 'US Eastern Offset', dtOffset AT TIME ZONE 'Eastern Standard Time' AS 'Eastern Offset', dtOffset AT TIME ZONE 'Hawaiian Standard Time' AS 'Hawaiian Standard Time Offset'
301 FROM dates
302
303
304
305RETURN 0
306GO
307/*
308Post-Deployment Script Template
309--------------------------------------------------------------------------------------
310 This file contains SQL statements that will be appended to the build script.
311 Use SQLCMD syntax to include a file in the post-deployment script.
312 Example: :r .\myfile.sql
313 Use SQLCMD syntax to reference a variable in the post-deployment script.
314 Example: :setvar TableName MyTable
315 SELECT * FROM [$(TableName)]
316--------------------------------------------------------------------------------------
317*/
318DECLARE @return_value Int
319
320EXEC @return_value = [dbo].[TestDates]
321
322SELECT @return_value as 'Return Value'
323
324GO
325
326GO
327DECLARE @VarDecimalSupported AS BIT;
328
329SELECT @VarDecimalSupported = 0;
330
331IF ((ServerProperty(N'EngineEdition') = 3)
332 AND (((@@microsoftversion / power(2, 24) = 9)
333 AND (@@microsoftversion & 0xffff >= 3024))
334 OR ((@@microsoftversion / power(2, 24) = 10)
335 AND (@@microsoftversion & 0xffff >= 1600))))
336 SELECT @VarDecimalSupported = 1;
337
338IF (@VarDecimalSupported > 0)
339 BEGIN
340 EXECUTE sp_db_vardecimal_storage_format N'$(DatabaseName)', 'ON';
341 END
342
343
344GO
345ALTER DATABASE [$(DatabaseName)]
346 SET MULTI_USER
347 WITH ROLLBACK IMMEDIATE;
348
349
350GO
351PRINT N'Update complete.';
352
353
354GO