Creating a DBA Oncall list to email to group.

Question

Creating a DBA Oncall list to email to group.

randy.moodispaugh

SSC Veteran

Points: 281
More actions
July 19, 2016 at 1:39 pm

#328547

I'm looking for suggestions on creating a table with a list of DBA's and then a Job that will weekly send out a list of people who are on call for that week.
I have tried googling multiple phrasings with utter failure in searching. I swear I have seen something like what I'm looking for but it eludes me for now.
Example:
For the week of August 18th through August 24
DBA OnCall
Name Phone Number Email
Bob Smith 555.555.1234 me@company.com

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

randy.moodispaugh SSC Veteran Points: 281 More actions · Answer 1

I want to automate this information to be emailed to a group, to allow for DBA's to swap Schedules. Just looking for some ideas for creating this in TSQL/SP and running as a job.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

randy.moodispaugh (7/19/2016)
I want to automate this information to be emailed to a group, to allow for DBA's to swap Schedules. Just looking for some ideas for creating this in TSQL/SP and running as a job.

Have you looked at the documentation for sp_Send_DBMail, especially the @Query argument? Just like Ragu spaghetti sauce, "It's in there". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

randy.moodispaugh SSC Veteran Points: 281 More actions · Answer 3

Yes, I'm good with the DB Mail. I'm just playing through the different kinds of logic I would need to create an automated rotation of the DBA On Call list. Thanks for the input and if you have any ideas on the logic let me know.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

randy.moodispaugh (7/20/2016)
Yes, I'm good with the DB Mail. I'm just playing through the different kinds of logic I would need to create an automated rotation of the DBA On Call list. Thanks for the input and if you have any ideas on the logic let me know.

would help if you gave us a few more details here......eg how many dba's are there, how many on call at a time, are they always on call for a week, when does your week start...yadda yadda

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Eric M Russell SSC Guru Points: 125561 More actions · Answer 5

Which aspect of this do you need advice on?

Create a table to contain one row for each DBA along with their contact details and what days of week / hours they are on call.

Create a job with a schedule that runs once per day, week, etc. depending on required frequency of notifications.

Add a job step that queries the table to build up email message and then calls stored procedure to send out email.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Y.B. SSChampion Points: 11649 More actions · Answer 6

Eric M Russell (7/20/2016)
Which aspect of this do you need advice on?
Create a table to contain one row for each DBA along with their contact details and what days of week / hours they are on call.
Create a job with a schedule that runs once per day, week, etc. depending on required frequency of notifications.
Add a job step that queries the table to build up email message and then calls stored procedure to send out email.

I would also like to suggest that you build a front end application to handle any changes to the on-call rotation. Even if it's a simple one. I've built many slick solutions in SQL but when they only 'live' in SQL they become your full time responsibility. But hey, on-call rotations would never change right? :w00t:

SELECT quote FROM brain WHERE original = 1
0 rows returned

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

Eric has the all steps defined.

If you need help with concatenating the email addresses into a single string, check out Wayne Sheffield's article at http://www.sqlservercentral.com/articles/comma+separated+list/71700/. Just use the semicolon instead of the comma for building a single email address list.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Eric M Russell SSC Guru Points: 125561 More actions · Answer 8

There is an existing operator, schedule, notification framework for use by SQL Agent and SQL Alerts, and you may be able to leverage these existing tables. What you're wanting to do can (or should) be stacked on top of this. For example, operators (DBAs) are contained in MSDB.DBO.SYSOPERATORS.

https://msdn.microsoft.com/en-us/library/ms188406.aspx

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho