How to make a date dimension

So I need to create a date dimension, and I refuse to use RBAR-y loops or cursors to do it.

That means I'm limiting myself, intentionally, to using a tally table (also sometimes called a numbers table).

I found the script by DustinRyan, URL included inline below, that did most of what I needed. However, the Day Of The Week In Month (DoWInMonth) column was filled by a loop, and that was going to violate the requirements for making this dimension.

Also, the election day query was using a nested loop, and that was going to violate the requirements for the making of this dimension.

Found below is my update of his script to use a tally table to dynamically generate the required column. On my development virtual machine, running on a laptop spinning at 5400 RPM, generating and inserting all 300 years of this data as an insert takes less than 5 seconds. Not bad.

Not bad at all.

-- Find 300 years of dates
-- This version created by brad mckuhen
-- http://www.bradmckuhen.com
-- Original from a date dimension script by DustinRyan, found here:
-- http://www.bidn.com/blogs/DustinRyan/bidn-blog/2667/create-date-dimension-script
PRINT convert(VARCHAR, getdate(), 113) --USED FOR CHECKING RUN TIME.

-- Need a DB everybody has
USE tempdb;
GO

-- Prevent unneeded network traffic
SET NOCOUNT ON;
GO

-- Clear any previous runs
IF object_id(N'tempdb..dimDate') IS NOT NULL
 DROP TABLE dimDate;

CREATE TABLE [dbo].[DimDate] (
 --[DateSK] [int] IDENTITY(1,1) NOT NULL--Use this line if you just want an autoincrementing counter AND COMMENT BELOW LINE 
 [DateSK] [int] NOT NULL --TO MAKE THE DateSK THE YYYYMMDD FORMAT USE THIS LINE AND COMMENT ABOVE LINE. 
 , [FullDate] [datetime] NOT NULL
 , [Day] [tinyint] NOT NULL
 , [DaySuffix] [varchar](4) NOT NULL
 , [DayOfWeek] [varchar](9) NOT NULL
 , [DayOfWeekNumber] [int] NOT NULL
 , [DayOfWeekInMonth] [tinyint] NOT NULL
 , [DayOfYearNumber] [int] NOT NULL
 , [RelativeDays] INT NOT NULL
 , [WeekOfYearNumber] [tinyint] NOT NULL
 , [WeekOfMonthNumber] [tinyint] NOT NULL
 , [RelativeWeeks] INT NOT NULL
 , [CalendarMonthNumber] [tinyint] NOT NULL
 , [CalendarMonthName] [varchar](9) NOT NULL
 , [RelativeMonths] INT NOT NULL
 , [CalendarQuarterNumber] [tinyint] NOT NULL
 , [CalendarQuarterName] [varchar](6) NOT NULL
 , [RelativeQuarters] INT NOT NULL
 , [CalendarYearNumber] INT NOT NULL
 , [RelativeYears] INT NOT NULL
 , [StandardDate] [varchar](10) NULL
 , [WeekDayFlag] BIT NOT NULL
 , [HolidayFlag] BIT NOT NULL
 , [OpenFlag] BIT NOT NULL
 , [FirstDayOfCalendarMonthFlag] BIT NOT NULL
 , [LastDayOfCalendarMonthFlag] BIT NOT NULL
 , [HolidayText] [varchar](50) NULL CONSTRAINT [PK_DimDate] PRIMARY KEY CLUSTERED ([DateSK] ASC) WITH (
  PAD_INDEX = OFF
  , STATISTICS_NORECOMPUTE = OFF
  , IGNORE_DUP_KEY = OFF
  , ALLOW_ROW_LOCKS = ON
  , ALLOW_PAGE_LOCKS = ON
  , FILLFACTOR = 100
  )
 ON [PRIMARY]
 ) ON [PRIMARY]
GO

--you could also set this line to getdate() if you wanted 300 years from today
DECLARE @CurrentDate DATE = '1/1/1800';
DECLARE @StartDate DATETIME = cast('12/31/1799' AS DATE);

INSERT INTO DimDate (
 [DateSK]
 , --TO MAKE THE DateSK THE YYYYMMDD FORMAT UNCOMMENT THIS LINE... Comment for autoincrementing. 
 [FullDate]
 , [Day]
 , [DaySuffix]
 , [DayOfWeek]
 , [DayOfWeekNumber]
 , [DayOfWeekInMonth]
 , [DayOfYearNumber]
 , [RelativeDays]
 , [WeekOfYearNumber]
 , [WeekOfMonthNumber]
 , [RelativeWeeks]
 , [CalendarMonthNumber]
 , [CalendarMonthName]
 , [RelativeMonths]
 , [CalendarQuarterNumber]
 , [CalendarQuarterName]
 , [RelativeQuarters]
 , [CalendarYearNumber]
 , [RelativeYears]
 , [StandardDate]
 , [WeekDayFlag]
 , [HolidayFlag]
 , [OpenFlag]
 , [FirstDayOfCalendarMonthFlag]
 , [LastDayOfCalendarMonthFlag]
 )
SELECT CONVERT(VARCHAR, calcdate, 112) AS [DateSK]
 , calcDate AS [FullDate]
 , DATEPART(DAY, calcDate) AS [Day]
 , CASE 
  WHEN DATEPART(DAY, calcDate) IN (
    11
    , 12
    , 13
    )
   THEN CAST(DATEPART(DAY, calcDate) AS VARCHAR) + 'th'
  WHEN RIGHT(DATEPART(DAY, calcDate), 1) = 1
   THEN CAST(DATEPART(DAY, calcDate) AS VARCHAR) + 'st'
  WHEN RIGHT(DATEPART(DAY, calcDate), 1) = 2
   THEN CAST(DATEPART(DAY, calcDate) AS VARCHAR) + 'nd'
  WHEN RIGHT(DATEPART(DAY, calcDate), 1) = 3
   THEN CAST(DATEPART(DAY, calcDate) AS VARCHAR) + 'rd'
  ELSE CAST(DATEPART(DAY, calcDate) AS VARCHAR) + 'th'
  END AS [DaySuffix]
 , CASE DATEPART(DW, CalcDate)
  WHEN 1
   THEN 'Sunday'
  WHEN 2
   THEN 'Monday'
  WHEN 3
   THEN 'Tuesday'
  WHEN 4
   THEN 'Wednesday'
  WHEN 5
   THEN 'Thursday'
  WHEN 6
   THEN 'Friday'
  WHEN 7
   THEN 'Saturday'
  END AS [DayOfWeek]
 , DATEPART(DW, CalcDate) AS [DayOfWeekNumber]
 --Occurance of this day in this month. If Third Monday then 3 and DOW would be Monday.
 , rank() OVER (
  PARTITION BY yearno
  , monthno
  , dow ORDER BY calcdate
  ) AS DOWInMonth
 , DATEPART(dy, CalcDate) AS [DayOfYearNumber] --Day of the year. 0 - 365/366
 , DATEDIFF(dd, @CurrentDate, CalcDate) AS [RelativeDays]
 , DATEPART(ww, CalcDate) AS [WeekOfYearNumber] --0-52/53
 , DATEPART(ww, CalcDate) + 1 - DATEPART(ww, CAST(DATEPART(mm, CalcDate) AS VARCHAR) + '/1/' + CAST(DATEPART(yy, CalcDate) AS VARCHAR)) [WeekOfMonthNumber]
 , DATEDIFF(ww, @CurrentDate, CalcDate) AS [RelativeWeeks]
 , DATEPART(MONTH, CalcDate) AS [CalendarMonthNumber] --To be converted with leading zero later.
 , DATENAME(MONTH, CalcDate) AS [CalendarMonthName]
 , DATEDIFF(MONTH, @CurrentDate, CalcDate) AS [RelativeMonths]
 , DATEPART(qq, CalcDate) AS [CalendarQuarterNumber] --Calendar quarter
 , CASE DATEPART(qq, CalcDate)
  WHEN 1
   THEN 'First'
  WHEN 2
   THEN 'Second'
  WHEN 3
   THEN 'Third'
  WHEN 4
   THEN 'Fourth'
  END AS [CalendarQuarterName]
 , DATEDIFF(qq, @CurrentDate, CalcDate) AS [RelativeQuarters]
 , DATEPART(YEAR, CalcDate) AS [CalendarYearNumber]
 , DATEDIFF(YEAR, @CurrentDate, CalcDate) AS [RelativeYears]
 , RIGHT('0' + convert(VARCHAR(2), MONTH(CalcDate)), 2) + '/' + Right('0' + convert(VARCHAR(2), DAY(CalcDate)), 2) + '/' + convert(VARCHAR(4), YEAR(CalcDate))
 , CASE DATEPART(DW, CalcDate)
  WHEN 1
   THEN 0
  WHEN 2
   THEN 1
  WHEN 3
   THEN 1
  WHEN 4
   THEN 1
  WHEN 5
   THEN 1
  WHEN 6
   THEN 1
  WHEN 7
   THEN 0
  END AS [WeekDayFlag]
 , 0 AS HolidayFlag
 , CASE DATEPART(DW, CalcDate)
  WHEN 1
   THEN 0
  WHEN 2
   THEN 1
  WHEN 3
   THEN 1
  WHEN 4
   THEN 1
  WHEN 5
   THEN 1
  WHEN 6
   THEN 1
  WHEN 7
   THEN 1
  END AS OpenFlag
 , CASE DATEPART(dd, CalcDate)
  WHEN 1
   THEN 1
  ELSE 0
  END AS [FirstDayOfCalendarMonthFlag]
 , CASE 
  WHEN DateAdd(day, - 1, DateAdd(month, DateDiff(month, 0, CalcDate) + 1, 0)) = CalcDate
   THEN 1
  ELSE 0
  END AS [LastDayOfCalendarMonthFlag]
FROM (
 -- To fuel the RANK() I use to get the dynamic 
 -- DoWinMonth value, I calculate the necessary pieces here
 SELECT [Surrogate Key] AS IDNo
  , @StartDate + [Surrogate Key] AS CalcDate
  , datepart(DW, @StartDate + [Surrogate Key]) AS DoW
  , year(@StartDate + [Surrogate Key]) AS YearNo
  , month(@StartDate + [Surrogate Key]) AS MonthNo
 FROM (
  -- This is the tally table on which the whole thing is built
  SELECT TOP (
    SELECT cast(365.25 * 300 AS BIGINT)
    ) ROW_NUMBER() OVER (
    ORDER BY getdate()
    ) AS [Surrogate Key]
  FROM sys.syscolumns s1
   , sys.syscolumns s2
  ) j
 ) k
-- Get the results in a predictable order
ORDER BY [DateSK];

--Add HOLIDAYS --------------------------------------------------------------------------------------------------------------
-- New Years Day --------------------------------------------------------------------------------------------- 
UPDATE dbo.DimDate
SET HolidayText = 'New Year''s Day'
 , HolidayFlag = 1
 , OpenFlag = 0
WHERE [CalendarMonthNumber] = 1
 AND [DAY] = 1

--Set OpenFlag = 0 if New Year's Day is on weekend 
UPDATE dbo.DimDate
SET OpenFlag = 0
WHERE DateSK IN (
  SELECT CASE 
    WHEN DayOfWeek = 'Sunday'
     THEN DATESK + 1
    END
  FROM DimDate
  WHERE CalendarMonthNumber = 1
   AND [DAY] = 1
  )

--Martin Luther King Day --------------------------------------------------------------------------------------- 
--Third Monday in January starting in 1983 
UPDATE DimDate
SET HolidayText = 'Martin Luther King Jr. Day'
 , HolidayFlag = 1
 , OpenFlag = 0
WHERE [CalendarMonthNumber] = 1 --January 
 AND [Dayofweek] = 'Monday'
 AND CalendarYearNumber >= 1983 --When holiday was official 
 AND [DayOfWeekInMonth] = 3 --Third X day of current month. 
GO

--President's Day --------------------------------------------------------------------------------------- 
--Third Monday in February. 
UPDATE DimDate
SET HolidayText = 'President''s Day'
 , HolidayFlag = 1
 , OpenFlag = 0
WHERE [CalendarMonthNumber] = 2 --February 
 AND [Dayofweek] = 'Monday'
 AND [DayOfWeekInMonth] = 3 --Third occurance of a monday in this month. 
GO

--Memorial Day ---------------------------------------------------------------------------------------- 
--Last Monday in May 
UPDATE dbo.DimDate
SET HolidayText = 'Memorial Day'
 , HolidayFlag = 1
 , OpenFlag = 0
FROM DimDate
WHERE DateSK IN (
  SELECT MAX([DateSK])
  FROM dbo.DimDate
  WHERE [CalendarMonthName] = 'May'
   AND [DayOfWeek] = 'Monday'
  GROUP BY CalendarYearNumber
   , [CalendarMonthNumber]
  )

--4th of July --------------------------------------------------------------------------------------------- 
UPDATE dbo.DimDate
SET HolidayText = 'Independance Day'
 , HolidayFlag = 1
 , OpenFlag = 0
WHERE [CalendarMonthNumber] = 7
 AND [DAY] = 4

--Set OpenFlag = 0 if July 4th is on weekend 
UPDATE dbo.DimDate
SET OpenFlag = 0
WHERE DateSK IN (
  SELECT CASE 
    WHEN DayOfWeek = 'Sunday'
     THEN DATESK + 1
    END
  FROM DimDate
  WHERE CalendarMonthNumber = 7
   AND [DAY] = 4
  )

--Labor Day ------------------------------------------------------------------------------------------- 
--First Monday in September 
UPDATE dbo.DimDate
SET HolidayText = 'Labor Day'
 , HolidayFlag = 1
 , OpenFlag = 0
FROM DimDate
WHERE DateSK IN (
  SELECT MIN([DateSK])
  FROM dbo.DimDate
  WHERE [CalendarMonthName] = 'September'
   AND [DayOfWeek] = 'Monday'
  GROUP BY CalendarYearNumber
   , [CalendarMonthNumber]
  )

--Columbus Day------------------------------------------------------------------------------------------ 
--2nd Monday in October 
UPDATE dbo.DimDate
SET HolidayText = 'Columbus Day'
 , HolidayFlag = 1
 , OpenFlag = 0
FROM DimDate
WHERE DateSK IN (
  SELECT MIN(DateSK)
  FROM dbo.DimDate
  WHERE [CalendarMonthName] = 'October'
   AND [DayOfWeek] = 'Monday'
   AND [DayOfWeekInMonth] = 2
  GROUP BY CalendarYearNumber
   , [CalendarMonthNumber]
  )

--Veteran's Day -------------------------------------------------------------------------------------------------------------- 
UPDATE DimDate
SET HolidayText = 'Veteran''s Day'
 , HolidayFlag = 1
 , OpenFlag = 0
WHERE DateSK IN (
  SELECT CASE 
    WHEN DayOfWeek = 'Saturday'
     THEN DateSK - 1
    WHEN DayOfWeek = 'Sunday'
     THEN DateSK + 1
    ELSE DateSK
    END AS VeteransDateSK
  FROM DimDate
  WHERE [CalendarMonthNumber] = 11
   AND [DAY] = 11
  )
GO

--THANKSGIVING -------------------------------------------------------------------------------------------------------------- 
--Fourth THURSDAY in November. 
UPDATE DimDate
SET HolidayText = 'Thanksgiving Day'
 , HolidayFlag = 1
 , OpenFlag = 0
WHERE [CalendarMonthNumber] = 11
 AND [DAYOFWEEK] = 'Thursday'
 AND [DayOfWeekInMonth] = 4
GO

--CHRISTMAS ------------------------------------------------------------------------------------------- 
UPDATE dbo.DimDate
SET HolidayText = 'Christmas Day'
 , HolidayFlag = 1
 , OpenFlag = 0
WHERE [CalendarMonthNumber] = 12
 AND [DAY] = 25

--Set OpenFlag = 0 if Christmas on weekend 
UPDATE dbo.DimDate
SET OpenFlag = 0
WHERE DateSK IN (
  SELECT CASE 
    WHEN DayOfWeek = 'Sunday'
     THEN DATESK + 1
    WHEN Dayofweek = 'Saturday'
     THEN DateSK - 1
    END
  FROM DimDate
  WHERE CalendarMonthNumber = 12
   AND DAY = 25
  )

-- Valentine's Day --------------------------------------------------------------------------------------------- 
UPDATE dbo.DimDate
SET HolidayText = 'Valentine''s Day'
WHERE CalendarMonthNumber = 2
 AND [DAY] = 14

-- Saint Patrick's Day ----------------------------------------------------------------------------------------- 
UPDATE dbo.DimDate
SET HolidayText = 'Saint Patrick''s Day'
WHERE [CalendarMonthNumber] = 3
 AND [DAY] = 17
GO

--Mother's Day --------------------------------------------------------------------------------------- 
--Second Sunday of May 
UPDATE DimDate
SET HolidayText = 'Mother''s Day' --select * from DimDate 
WHERE [CalendarMonthNumber] = 5 --May 
 AND [Dayofweek] = 'Sunday'
 AND [DayOfWeekInMonth] = 2 --Second occurance of a monday in this month. 
GO

--Father's Day --------------------------------------------------------------------------------------- 
--Third Sunday of June 
UPDATE DimDate
SET HolidayText = 'Father''s Day' --select * from DimDate 
WHERE [CalendarMonthNumber] = 6 --June 
 AND [Dayofweek] = 'Sunday'
 AND [DayOfWeekInMonth] = 3 --Third occurance of a monday in this month. 
GO

--Halloween 10/31 ---------------------------------------------------------------------------------- 
UPDATE dbo.DimDate
SET HolidayText = 'Halloween'
WHERE [CalendarMonthNumber] = 10
 AND [DAY] = 31

--Election Day-------------------------------------------------------------------------------------- 
--The first Tuesday after the first Monday in November. 
UPDATE tues
SET HolidayFlag = 1
 , HolidayText = 'Election Day'
FROM dimdate AS tues
INNER JOIN dimdate AS mon
 ON tues.fulldate = mon.fulldate + 1
WHERE mon.CalendarMonthNumber = 11
 AND mon.[DayOfWeek] = 'Monday'
 AND mon.DayOfWeekInMonth = 1
GO

-------------------------------------------------------------------------------------------------------- 
PRINT CONVERT(VARCHAR, GETDATE(), 113) --USED FOR CHECKING RUN TIME.

--DimDate indexes--------------------------------------------------------------------------------------------- 
CREATE UNIQUE NONCLUSTERED INDEX [IDX_DimDate_Date] ON [dbo].[DimDate] ([FullDate] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_Day] ON [dbo].[DimDate] ([Day] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_DayOfWeek] ON [dbo].[DimDate] ([DayOfWeek] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_DOWInMonth] ON [dbo].[DimDate] ([DayOfWeekInMonth] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_DayOfYear] ON [dbo].[DimDate] ([DayOfYearNumber] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_WeekOfYear] ON [dbo].[DimDate] ([WeekOfYearNumber] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_WeekOfMonth] ON [dbo].[DimDate] ([WeekOfMonthNumber] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_Month] ON [dbo].[DimDate] ([CalendarMonthNumber] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_MonthName] ON [dbo].[DimDate] ([CalendarMonthName] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_Quarter] ON [dbo].[DimDate] ([CalendarQuarterNumber] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_QuarterName] ON [dbo].[DimDate] ([CalendarQuarterName] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DimDate_Year] ON [dbo].[DimDate] ([CalendarYearNumber] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_dim_Time_HolidayText] ON [dbo].[DimDate] ([HolidayText] ASC)
 WITH (
   PAD_INDEX = OFF
   , STATISTICS_NORECOMPUTE = OFF
   , SORT_IN_TEMPDB = OFF
   , IGNORE_DUP_KEY = OFF
   , DROP_EXISTING = OFF
   , ONLINE = OFF
   , ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON
   , FILLFACTOR = 90
   ) ON [PRIMARY]

PRINT convert(VARCHAR, getdate(), 113) --USED FOR CHECKING RUN TIME.
/*
-- Holiday Validation Query
select count(1), HolidayText 
from dimdate 
where HolidayFlag = 1
group by HolidayText
having count(1) >= 1

*/

How to resend a message using dbmail

I'm a good DBA, developer, and all around wonderful human being. That means I love sets, hate cursors, and document my databases in my spare time.

However.

In this life, we all find strange things that can only be done one way. The below script is, I think, one of those unfortunate examples of that, in that it uses a cursor to get work done. However, if you can find a better way, email it or post a comment, and let's have a discussion.

Here's the short version:

I use SQL Agent to run a bunch of jobs overnight. SQL Agent is configured to send me the job status at the end of the job, succeed or fail.

In my situation, I needed to get all the failed messages for some time period to audit them. In order to get SQL Server dbmail to re-send the messages, I couldn't find an easy way to do this, so I made my own way to force the messages that weren't sent to get resent.

This is one of those cases where cursors make sense, because essentially, this is dynamic sql, due to the requirement to call the SP with the parameter values that change with every iteration of the loop.

I don't like being forced to use a cursor, but it works, it did indeed resend the emails, and it let me audit what I needed.

USE msdb;
go

DECLARE c1 CURSOR FOR
  SELECT DISTINCT
  f.recipients
  ,
  f.subject,
  f.body,
  f.importance,
  f.sensitivity
  FROM   msdb.dbo.sysmail_faileditems AS f
         JOIN msdb.dbo.sysmail_sentitems AS s
           ON f.profile_id = s.profile_id
              AND Isnull(f.recipients, '') = Isnull(s.recipients, '')
              AND Isnull(f.copy_recipients, '') = Isnull(s.copy_recipients, '')
              AND Isnull(f.blind_copy_recipients, '') =
                  Isnull(s.blind_copy_recipients, '')
              AND f.importance = s.importance
              AND f.sensitivity = s.sensitivity
  WHERE  Datediff(dd, f.sent_date, Getdate()) < 31
         AND f.body <> s.body;

DECLARE @Failed_profile_name                SYSNAME,
        @Failed_recipients                  VARCHAR(max),
        @Failed_copy_recipients             VARCHAR(max),
        @Failed_blind_copy_recipients       VARCHAR(max),
        @Failed_from_address                VARCHAR(max),
        @Failed_reply_to                    VARCHAR(max),
        @Failed_subject                     NVARCHAR(255),
        @Failed_body                        NVARCHAR(max),
        @Failed_body_format                 VARCHAR(20),
        @Failed_importance                  VARCHAR(6),
        @Failed_sensitivity                 VARCHAR(12),
        @Failed_file_attachments            NVARCHAR(max),
        @Failed_query                       NVARCHAR(max),
        @Failed_execute_query_database      SYSNAME,
        @Failed_attach_query_result_as_file BIT,
        @Failed_query_attachment_filename   NVARCHAR(255),
        @Failed_query_result_header         BIT,
        @Failed_query_result_width          INT,
        @Failed_query_result_separator      CHAR(1),
        @Failed_exclude_query_output        BIT,
        @Failed_append_query_error          BIT,
        @Failed_query_no_truncate           BIT,
        @Failed_query_result_no_padding     BIT
;

OPEN c1;

FETCH next FROM c1 INTO @Failed_recipients, @Failed_subject, @Failed_body,
@Failed_importance, @Failed_sensitivity;

WHILE @@fetch_status = 0
  BEGIN
      FETCH next FROM c1 INTO @Failed_recipients, @Failed_subject, @Failed_body,
      @Failed_importance, @Failed_sensitivity;

      EXEC Sp_send_dbmail
        @profile_name = 'Default Profile',
        @recipients = @Failed_recipients,
        @from_address = 'era@HQ.DHS.GOV',
        @reply_to = 'era@HQ.DHS.GOV',
        @subject = @Failed_subject,
        @body = @Failed_body,
        @importance = @Failed_importance,
        @sensitivity = @Failed_sensitivity;
  END

CLOSE c1;

DEALLOCATE c1; 

error code 84bc0643

SQL Server 2012 install of SP1 fails with error code 84BC0643.

"Windows Update ran into a problem."

No kidding, Windows Update. 

This was followed by the Windows Update (KB2793634) - "Windows Installer starts repeatedly after you install SQL Server 2012 SP1"

This update DID succeed, and Windows Update now shows a clean slate - no updates found.

Free zip code information

Free zip code information I found online:

• http://federalgovernmentzipcodes.us/
• http://geonames.usgs.gov/domestic/download_data.htm