SQL Server Scheduled Email Queries

Every once in a while I come across a problem that both surprises me that it exists and that the solution is often trivial. This is one of those. Basically developer at a client wanted to setup a scheduled task that would execute a query and email the results. Everything appeared to work, but the emails never went out. There were no errors that were obvious other than the emails never arrived. Some digging via profiler showed that the SQL Agent user was having permission issues. But basically giving it every permission possible didn’t solve it.

So let me walk you through it. First, let’s create a real simple stored procedure. This assumes you have AdventureWorks2014 installed (yeah, it’s old, but it’s what I had handy).

Use adventureworks2014
GO
Create or Alter Procedure Send_Test_Email
as
exec msdb.dbo.sp_send_dbmail @recipients=’test@example.com’, @body=’This is the body of the email’, @subject=’Test Email w query embedded’,
@query=’select * from adventureworks2014.sales.SalesOrderHeader where subtotal > 150000′;

Now, here’s an important detail I did discover during testing. If my query didn’t actually query a table, but instead was say @query=’select getdate()’ things worked fine.

That said, if you simply execute the query above in SSMS, it should work just fine. (I’d recommend you put your own email in it for testing. This way you’ll know if the email is actually being sent.)

Before you do that, also create the following stored procedure:

Use adventureworks2014
GO
Create or Alter Procedure Send_Test_Email_Query_attached
as
exec msdb.dbo.sp_send_dbmail @recipients=’test@example.com’, @body=’This is the body of the email’, @subject=’Test Email w query embedded’,
@query=’select * from adventureworks2014.sales.SalesOrderHeader where subtotal > 150000′,
@attach_query_result_as_file = 1,
@query_result_no_padding = 1,
@query_attachment_filename = ‘Sales Report.csv’,
@query_result_separator = ‘;’,
@exclude_query_output = 1,
@append_query_error = 0,
@query_result_header = 0

This will execute the same query as the original stored procedure, but place the contents in a CSV file as an attachment.

Again, if you execute the above directly from SSMS, you should receive the email without an issue. This is basically what the client was attempting to do.

Now create the following job:

USE [msdb]
GO

/ Object: Job [Send AdventureWorks Email] Script Date: 7/27/2021 9:12:49 AM / BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 / Object: JobCategory [[Uncategorized (Local)]] Script Date: 7/27/2021 9:12:49 AM /
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’Send AdventureWorks Email’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/ Object: Step [Send email] Script Date: 7/27/2021 9:12:50 AM /
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Send email’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’exec Send_test_email_Query_attached’,
@database_name=N’adventureworks2014′,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

If you execute this job as is, you should see the following:

Success! Or is it?

But you will never get the email!

Modify the above job and replace the line:

@command=N’exec Send_test_email_Query_attached’,

with

@command=N’exec Send_test_email’,

Now if you run the job, it will fail!

This time it clearly failed!

But as is usual with sp_send_dbmail, the error message isn’t overly helpful:

Executed as user: NT AUTHORITY\SYSTEM. Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050). The step failed.

But it does help give a clue. The problem seems to be some sort of permissions issue. So I’ll admit, at this point I tried all sorts of solutions, including setting up a proxy user, giving unfettered rights to my SQL Agent user and other things (thinking once I got it working I could then lock things back down). Instead, I found a much easier solution buried in a thread on the Microsoft site.

You’ll note when I wrote the original stored procedure I fully qualified the table name. This is often generally useful, but here I did it because I was cheating and knew I’d need it for this demo.

The solution is actually VERY simple and I’ll show it both graphically and via a script.

First: change the database to MSDB and then fully qualify your call to the stored procedure as below:

Execute it from the MSDB database

However, there’s one more critical step: under the ADVANCED tab in the step change the Run As User to dbo:

Seems simple, but it’s critical

Now if you script out the scheduled task it should look like:

USE [msdb]
GO

/ Object: Job [Send AdventureWorks Email] Script Date: 7/27/2021 9:26:37 AM /
EXEC msdb.dbo.sp_delete_job @job_id=N’08a55e18-eecf-4d4c-8197-8135a0d7520b’, @delete_unused_schedule=1
GO

/ Object: Job [Send AdventureWorks Email] Script Date: 7/27/2021 9:26:37 AM / BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 / Object: JobCategory [[Uncategorized (Local)]] Script Date: 7/27/2021 9:26:37 AM /
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’Send AdventureWorks Email’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/ Object: Step [Send email] Script Date: 7/27/2021 9:26:37 AM /
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Send email’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’exec adventureworks2014.dbo.Send_Test_Email_Query_attached’,
@database_name=N’msdb’,
@database_user_name=N’dbo’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

If you then execute THIS scheduled task you’ll get both a success message:

And you should actually receive the email with the query results attached.

So, basically an issue that surprised me that it even existed, actually ended up with a fairly simple solution.

#YesAllMen

For over two years I’ve been wanting to put together a speaking topic using the above as a title or as a subtitle. The reason is because the alternative, #NotAllMen is NOT a good thing to use.

Yesterday I came across an article that said it better than I could. I would recommend you read it first: Buzzfeed Article.

It brings up several great points, including ones I had not given much thought to. Look, I know when a friend of yours is upset that a guy just made a pass at her in a professional setting, one of your first instincts is to think “I’d never do that” and are tempted to say “#notallmen” you’re not being helpful. In fact, you’ve just made it about yourself. Just don’t. That’s not what is needed.

Look, I’m pretty confident that all my male readers are pretty decent guys. I work with many of you professionally, some of you in volunteer positions, some are simply friends, and some readers, I suspect I don’t know, but you’re probably ok too. None of you are outright sexist or racist. If you were, I wouldn’t be associating with you. But all of us are still a product of our environments. We make the off-handed comment without thinking about it. Or someone around us makes a comment and we don’t react. This is also why I suggest when it comes to calling yourself an ally, just don’t.

Several years ago I helped organize and then participated in a Women in Tech panel for our local SQL Server User Group. I was the only man on the panel and expected to be asked what was the best thing I thought we, as men could do. The answer was of course somewhat ironic: “Sit down and shut up.” I of course expanded upon this. No, we can’t nor should we ever completely shut up. That wasn’t my point of course. My point was to make sure not to center the discussion about us. When a friend complains about a sexist incident, replying #NotAllMen is doing just that. Centering the discussion on us. Sorry, that’s a time to shut up and listen.

But when others make sexist comments, that’s a time when it may be appropriate to say something. And if someone calls you out, take it in stride. We make mistakes. And if someone takes you aside and says something (I’ve now heard that called a “call-in”) thank them. It means they think well enough of you to help you be a better person.

At the end of the day guys, we’re all still part of the problem, even when we do our best. That doesn’t make us evil. It simply means we have space to grow into. Let’s do that. Let’s grow.

… Other Duties as Assigned

I’ve mentioned once before that at one of my clients I describe my job as “DBA and other duties as assigned.”

This phrase has really been on my mind this week, especially during a phone call with another client yesterday. This second client is a local consulting company that has hired me a few times to back them with my skills in SQL Server and MS Access. This time around the work they’re looking for is definitely SQL Server related. It was refreshing.

But it reminded me of my last two weeks with two of my other clients. One is having an issue with their app (that they always call “the database”) that is most likely a design issue that I need to dig into. This is a perfect example of what I call “software archeology” where I at times have to shift through “pot shards” to determine what the original developer was thinking. At times it can be fun and interesting, at other times, frustrating. I’ll be shifting through more pot shards in the near future to get to the bottom of this problem.

For my largest client, I spent most of my hours with them last week trying to true up a file with some financial data in it. In this case it’s part of an ETL process where I receive data, compile it and send it to a vendor. The process uses a combination of PowerShell and Pentaho. So while they interact with the database, the work I was doing wasn’t in T-SQL or directly on the database server.

The numbers weren’t adding up. There was an undercurrent of “Greg, your numbers are wrong” or “You’re filtering on the wrong criteria.” I kept pointing out that “I simply add up the numbers you give me.” Eventually the problem was narrowed down to the fact that in the source system, which is the system of record, they had deleted rows. Arguably, one should never be deleting rows in such a system, but rather issuing a 2nd row (a credit if you want to reverse a debit, or a debit to reverse a credit) and this was typically what was done. But in this case the maintainers of the source of record decided to wholesale delete these rows. I explained that from day one, since deletions are never supposed to happen (and given the way the system works, extremely hard to detect) all I do is either insert new rows, or update existing rows. In any event, with one minor schema change, some updates to the rows in question and an updated PowerShell script, I was able to make the numbers come out to match with theirs. So, is that really DBA work? Not in the traditional sense. But it’s definitely other duties as assigned.

Now that’s not to say I didn’t do what some might consider actual DBA work. On Saturday morning I patched one of their servers. And at one point during the week, I deployed a script to production. So, out of 18 hours of work for the customer last week, I think I can say maybe 1-2 total was “dba work” or about 5%.

Now, I want to be clear. This is not a rant or a complaint. I’ll admit I tend to prefer to work directly with SQL Server, but I was reminded of a quick discussion I had with a fellow DBA over the weekend about how they probably needed to start to learn PowerShell for their job.

I’ve been arguing for years that the role of a DBA has changed, and will continue to change dramatically over the next few years. Once where we might spend days head down slinging T-SQL code, setting up backups and restores, tuning indices, etc. now much of that is automated or at least far easier to do. Which is a good thing. In years past, a DBA might be responsible for a dozen machines or so at the most. If it was more than that, we’d feel sorry for them. That’s no longer uniformly true. I know a DBA who is responsible for over 100 machines. They’re the soul DBA. But, through PowerShell and other modern tools, it’s generally not an overwhelming job.

However, like the online presentation from the Atlanta Azure Data User Group I attended last night on SQL Database Edge, there is a growing list of things DBAs need to learn. Steve Jones recently posted about whether DBAs need to learn Linux? The short take away is not necessarily, but it’s probably a good idea, but we definitely need to learn about containers.

I have heard for years, “Microsoft will automate everything and the DBA’s job will go away.” Not only is that not true in my experience, the exact opposite is. I think being a successful DBA is in some ways harder than it was a decade ago. There’s so much more to be aware of and to learn.

Off the top of my head, without any real priority I came up with the list below of technologies that a modern DBA might find useful to know. This is not to say I know them all, or that one has to be an expert in all of them. And I will note, this is far from an inclusive list. I also left out third-party tools which are so common place. But I think it illustrates just how broad the required skillset of a good DBA is these days.

  • T-SQL
  • PowerShell
  • Query Store
  • Linux – at least at the most basic level
  • Containers
  • SSIS
  • SSAS
  • SSRS
  • Storage – (at least how different types can impact performance and the advantages and disadvantages of each)
  • Azure
  • SQL Database Edge
  • git or some form of version control

In conclusion, I’ll say, I’m not going to make any predictions about where the Microsoft data platform will be a decade from now, but I can tell you that DBAs will still be needed but their skillset will be as different from today as today is from a decade ago.

And post conclusion, I’ll add I’ll continue to rely on #sqlfamily and all my fellow DBAs to help me out. And continue to help them.

Let me Email that!

This week’s post is a short one, but hopefully a practical and useful one. I had a recent need to send an email to about 3 dozen folks. Prior to this particular email, I had been updating them with general information and was simply using the BCC function of my email client to send out the email. But in this particular case I needed to email them login information specific to them. I didn’t relish the idea of crafting 3 dozen separate emails and making sure I got the correct information into each. So, I turned to the tool closest at hand: PowerShell and in about 3 minutes of writing, and 10 more minutes of debugging and tweaking, had something that worked great.

param([parameter(mandatory)] $course_id, [parameter(mandatory)] $course_description)

$course_info = invoke-sqlcmd -server "sql_server" -query "select ct.Course_Type_Name, c.Location, c.City,c.State from Courses c
inner join course_types ct on ct.Course_Type_ID=c.Course_Type_ID
where course_id=$course_id" 


$class_list=invoke-sqlcmd -server 'sql_server' -query "select p.persno, first_name,LAST_NAME, email1 from courses_taken ct
inner join people p on p.PERSNO=ct.PERSNO
and ct.Course_ID=$course_id"

foreach ($person in $class_list)
{
    $body = "Thank you for participating in the recent $($course_info.Course_type_name) $course_description course at $($course_info.location) in $($course_info.City), $($course_info.state). Your class information has been entered into the Generic Class database. 
If you wish to review your unofficial transcript, you may login in at https://www.example.com/Account/Login.aspx. 
If you have not created an account before you can create an account at https://www.example.com/Account/Register.aspx. 
Please be sure to use the following email:$($person.email1) (you can update it after creating your account) and your Generic Class ID: $($person.persno). Please note this is NOT your OTT ID. 
If you have issues or questions, please contact Joe Smith at mailto:jsmith@example.com. Thank you and we look forward to seeing you at future training!"

    Send-MailMessage -From "jsmith@example.com" -SmtpServer Mail_Server -Subject "Generic Class: Your Generic Class Login and unofficial transcript" -Body $body -to $person.email1
}

Every course in this particular database has an ID. And a course can have a specific course information. The first query gathers that information.

The second query then gets the personal information for each student who took that particular course and puts it into a class_list object.

I then simply iterate over the object and send an email with the personalized details to each student.

It literally took me more time to tweak the text and formatting of the body of the message than it did to write the rest of the script.

I suppose I could have figured out my email client’s mail merge feature, or done something in Word which I know also has such a feature but that would have taken far longer. I also could have spent more time making things more generic (perhaps passing in the body and having it automatically formatted, or sending as a nice HTML message with appropriate formatting. But sometimes, quick and dirty is the way to go. And now I have a script I can customize for future use.

Not bad for about 15 minutes worth of work.

Coping

I’m going to be a bit more open than I usually am in my blog posts. I think it’s time for a bit of transparency.

Let me start by saying that overall, despite the impact of Covid, the last 15 or so months have not been terrible for me. Far from it. In fact I’ve been very fortunate. So this post isn’t a rant or a series of complaints. It’s really a short reflection.

Last year for example, I biked more than I had in years, and in fact did my first century ride since college. I presented at PASS Summit for the first time, albeit virtually. I got to spend more time with my kids. Fortunately, no one close to me had a serious case of COVID nor died from it (though I had friends and former coworkers who did lose people close to them).

I even managed to organize and pull off a cave rescue training class late last summer. And of course just pulled off another weeklong course this month, and will help with another one in late August.

So overall, it’s been a pretty decent 15 months.

But lately I’ve noticed things aren’t necessarily where I want them to be. My motivation levels have been off. I’ve got at least 3-4 ideas for more articles for Redgate. But, I find myself finding reasons to put off writing them. I’ve got a few other projects that I haven’t made progress on. I need to finish tiling a backsplash in my bathroom, patch a hole in the wall in the downstairs bathroom from when I put in a fan, and a few more.

But honestly, the idea of launching into such projects just makes me go “bleah”.

I think too some of the frustration in my inability to attract new clients like I was hoping to this year has put me into the “bleah” mode, and of course in a vicious cycle caused me to put less effort into attracting new clients.

But, ultimately, I’m writing this post not because I’m looking for sympathy or for comfort, but ironically for the exact opposite reason. I find that often people hide the state of their emotional well-being and put on a happy fa├žade, especially on social media, and as a result everyone goes around thinking that everyone else is doing better than they are themselves doing. So, I’m saying, “hey, I’m doing great, but you know what, there are days when life is ‘bleah’ and it’s ok. And if you’re having such days, or even worse, you’re not alone.”

Postscript: I want to add, if you haven’t, check out Steve Jones blog, he’s been daily posting a bunch of coping suggestions. I don’t read them every day, and I suspect Steve would agree with me that take what works for you and ignore the ones that don’t is the way to go. In part his posts helped inspire this one.

“We’re up to plan F”

I managed to skip two weeks of writing, which is unusual for me, but I was busy with other business, primarily last week leading an NCRC weeklong class of cave rescue for Level 1 students. I had previously lead such a class over three weekends last year, and have helped teach the Level 2 class multiple times. Originally this past week was supposed to be our National weeklong class, but back in February we had agreed to postpone it due to the unknown status of the ongoing Covid pandemic. However, due to a huge demand and the success of vaccinations, we decided to do a “Regional” Class just limited to Level 1 students. This would help handle the pent up demand, create students for the Level 2 class that would be at National, and to do sort of a test run of our facilities before the much larger National.

There’s an old saying that no plan survives the first contact with the enemy. In cave rescue this is particularly true. It also appears to be true in cave rescue training classes!

The first hitch was the drive up the the camp we were using. The road had been stripped down to the base dirt level and they were doing construction. Not a huge issue, just a dusty one. But for cavers, dust is just mud without the water. But this would come into play later in the week.

Once at the camp, as I was settling in and confirming the facilities, the first thing I noticed was that the scissors lift we had used to rig ropes in the gym last time was gone. A few texts and I learned it had only been on loan to the camp the past two years and was no longer available. This presented our first real challenge. How to get ropes up over the beams 20-30′ in the air.

But shortly after I realized I had a far greater issue. The custom made rigging plates we use to tie off the end of the ropes to the posts were still sitting in my garage at home. I had completely forgotten them. This was resolved by a well timed call to an instructor heading towards the camp, who via a longer detour then he expected, was able to get them. Fortunately, had that call waited another 5 minutes, his detour would have probably doubled. So the timing was decent.

I figured the week was off to a good start at that point! Honestly though, we solved the problems and moved on. I went to bed fairly relaxed.

All went well until Monday. This was the day we were supposed to do activities on the cliffs. Several weeks ago, my son and I, along with two others had gone to the cliffs, which were on the same property as the camp, but accessible only by leaving the camp and accessing from a public road, in order to clear away debris and do other work to make them usable. I was excited to show them off. Unfortunately, due to the weather forecast of impending thunderstorms all day we made the decision to revise our schedule and move cliff day to the next day. There went Plan A. Plan B became “go the next day.”

On Tuesday I and a couple of other instructors got in my car to head to the cliffs in advance of the students so we could scope things out and plan the activities. We literally got to the bottom of the road from the main entrance to the camp where we were going to turn on to the road under construction, only to find a the road closed there with a gaping ditch dug across it. So much for Plan B. We went back to the camp, told students to hang on and then I headed out again, hoping to basically take a loop around and approach the access road to the cliffs from the opposite direction. After about a 3 mile detour we came to the other end of the road and found it closed there. Despite trying to sweet talk the flag person, we couldn’t get past (we could have lied and said we lived on the road, but after 8-10 other cars would have arrived in a caravan saying the same thing we thought that might be suspicious). There went Plan C. We called an instructor back at the camp and headed back.

We got there and turns out an instructor had already come up with Plan D, which was to see if we could access the cliffs by crossing a field the camp owned and going through the woods. It might involve some hiking, but it might be doable. While there are dirt-bike paths, there’s nothing there that worked for us. So that plan fell apart. We were up to Plan E now. Plan E was proposed to further swap some training, but we realized that would impact our schedule too much. Now on to Plan F. For Plan F, we decided to head to a local cave which we thought would have some suitable cliffs outside.

That worked. It would out quite well actually. We lost maybe an hour to 90 minutes with all the plans, but we ultimately came upon a plan that worked. We were able to teach the skills we wanted and accomplish our educational objectives.

Often we wake up with a plan in our heads for what we will do that day. Most days those plans work out. But, then there are the days where we have to adapt. Things go sideways. Something breaks, or something doesn’t go as planned. In the NCRC we have an unofficial motto, Semper Gumby – “Always be Flexible”. Sometimes you have to completely change plans (cancelling due to the threat of thunderstorms), others you may have to try to adapt (finding other possible routes to the cliffs) and finally you may need to reconsider how to meet your objectives in a new way (finding different cliffs).

My advice, don’t lock yourself into only one solution. It’s a recipe for failure.

“Man down!”

Last week I wrote about how in many crisis situations you should actually stop and take 5 minutes to assess the situation, take a deep breath, and maybe even make a cup of tea. The point was, in many cases, we’re not talking life or death, and by taking a bit longer to respond we can have a better response.

I pointed out that you don’t always have that luxury. That happened to my mom’s partner within days of me writing last week’s post. While at work at a local supermarket chain, he heard someone shout “Man down”. Next thing he knew, a young man was laying on the floor having seizures. He jumped into action and provided the appropriate, immediate first aid. This included telling someone to call 911. Apparently no one else, including his manager responded at first. But, he had learned how to respond in his basic training in the Army decades ago. That training stuck.

My mom called me to talk about this and wondered why no one else had responded (she knows of my interest in emergency response and the like). I pointed out it’s a variety of factors, but often comes down to people don’t know how to respond, or they’ve assumed someone else has already responded. This discussion prompted a quick Facebook post by me that that I’m expanding upon here.

Let me ask you this, if someone collapsed in front of you at the mall, would you know what to do? What would you do? Would you do it?

The reality is, unfortunately many would not respond. So here’s my advice.

Get some training

You do not need to become an EMT to respond. In fact most training can be done in just a few hours.

Take a First Aid and a CPR course. Make sure the CPR course includes a segment on how to use an AED (Automatic External Defibrillator). I’ve taken several such courses over the years and try to remain certified.

Take a Stop the Bleed class. This is a bit different from your standard First Aid class. I haven’t taken it yet, but plan to when I can find one near me (I may even look into getting one setup when I have a bit more free time).

“911, what’s your emergency?”

Call 911. Anyone can do this. I would recommend even teaching even your young children to do this if they find you or someone else unconscious. Even if they can’t communicate much details, 911 operators are trained to gather what information they can, have ways (usually electronically) of determining the address and dispatching help. (Please note, if your child or someone else calls 911 by accident, please do NOT hang up. Simply let them know it was a mistake. It happens, they understand. But if they aren’t made aware, they WILL dispatch resources).

TELL someone specific to call 911. If you’re about to render aid, do NOT assume someone has already called 911 or will. In a crowd, groupthink happens and everyone starts to freeze and/or assume someone else has it handled. My advice, don’t just say “someone call 911”. Point to a specific person and tell them to call 911. Odds are, they will do it. In many cases in an emergency, folks are simply looking for someone to take charge and to give them direction. Now, someone else may have already called 911, or it may end up being multiple people will be calling 911. THAT IS OK. That’s far better than no one calling if it’s an emergency. In the event of a heart attack minutes count. This means that the sooner 911 is called, the better.

Respond

This may sound obvious, but be prepared to act. Again, it’s a common trope that in large crowds, people tend NOT to act, because in part they expect someone else already has it covered. Be that person who does act.

Years ago in the northern Virginia area, I witnessed a car get t-boned on the far side of an intersection from me. There were 3 lanes of traffic in either direction. NO ONE stopped to check on the drivers. I had to wait for the light to change before I could cross the intersection and check on them. Fortunately, the driver of the car I checked on was fine, other than some very minor injuries from their air bag deploying. And by this time, another witness had finally stopped to check on the 2nd car. They too were fine. But several dozen people had witnessed the accident and only the two of us had responded. If they drivers had been seriously injured and no one had responded, things could have been much worse for them.

Carry gloves, maybe more

Carry nitrile gloves with you. Sounds perhaps a bit silly or trite, but they don’t take up room and you can toss them in your backpack, glove compartment (yes, really you can put gloves in there), your purse etc. If you do come across someone who is injured, especially if blood or other bodily fluids are present, don them. I even carry a tiny disposable rebreather mask for CRP in my work backpack. Takes up no room but it’s there if I need it.

When you enter public buildings, look to see if they have a sign about AED availability. Note it and if possible where it is. In addition to telling someone to call 911, be prepared to tell someone “Get the AED, I think there’s on next to the desk in reception.”

Get your employer involved

Get your work to sponsor training. And honestly, while many companies might offer video tutorials with a quick online quiz at the end, I think they’re a bare minimum. I think hands on training is FAR more effective. There’s a number of reasons for this as I understand it, including the fact that you’re often engaging multiple pathways to the brain (tactile as well as visual and auditory) and a certain level of stress can actually improve memorization.

Seeing a video about how to use an AED is very different from holding a training unit in your hands and feeling its weight and hearing it give you instructions directly. Applying a bandage is far more realistic when your mock patient is laying there groaning in pain. Even getting into the action of telling someone “Call 911” is far more impactful when you do it in a hands-on manner and not simply checking a box in an on-line quiz.

Find out what resources are available in the office. Is there a first aid kit? What’s in it? For larger offices, I would argue they should have an AED and perhaps a Stop the Bleed kit. When’s the last time the AED batteries were tested? Who is responsible for that?

This works

In the case of the “man down” that prompted this post, they are reportedly doing fine and suffered no injuries.

I know of a local case, at a school where a student collapsed. A coach and the school nurse responded. And while the nurse especially had more training, what saved the students life was having an AED on site and available. Even if the school nurse or coach had not been there, in theory any bystander could have responded in a similar fashion.

As I said above, you don’t have to be a highly trained EMT or the like to make an impact and save someone from further injury or even save a life. You simply need to have some basic training and be willing to respond.

Take 5 Minutes

This weekend I had the pleasure of moderating Brandon Leach‘s session at Data Saturday Southwest. The topic was “A DBA’s Guide to the Proper Handling of Corruption”. There were some great takeaways and if you get a chance, I recommend you catch it the next time he presents it.

But there was one thing that stood out that he mentioned that I wanted to write about: taking 5 minutes in an emergency. The idea is that sometimes the best thing you can do in an emergency is take 5 minutes. Doing this can save a lot of time and effort down the road.

Now, obviously, there are times when you can’t take 5 minutes. If you’re in an airplane and you lose both engines on takeoff while departing La Guardia, you don’t have 5 minutes. If your office is on fire, I would not suggest taking 5 minutes before deciding to leave the building. But other than the immediate life-threatening emergencies, I’m a huge fan of taking 5 minutes. Or as I’ve put it, “make yourself a cup of tea.” (note I don’t drink tea!) Or have a cookie!

Years ago, when the web was young (and I was younger) I wrote sort of a first-aid quiz web-page. Nothing fancy or formal, just a bunch of questions with hyperlinks to the bottom. It was self-graded. I don’t recall the exact wording of one of the questions but it was something along the lines of “You’re hiking and someone stumbles and breaks their leg, how long should you wait before you run off to get help.” The answer was basically “after you make some tea.”

This came about after hearing a talk from Dr. Frank Hubbell, the founder of SOLO talk about an incident in the White Mountains of New Hampshire where the leader of a Boy Scout troop passed out during breakfast. Immediately two scouts started to run down the trail to get help. While doing so, one slipped and fell off a bridge and broke his leg. Turns out the leader simply had passed out from low blood sugar and once he woke up and had some breakfast was fine. The pour scout with the broken leg though wasn’t quite so fine. If they had waited 5 minutes, the outcome would have been different.

The above is an example of what some call “Go Fever”. Our adrenaline starts pumping and we feel like we have to do something. Sitting still can feel very unnatural. This can happen even when we know rationally it’s NOT an emergency. Years ago during a mock cave rescue training exercise, a student was so pumped up that he started to back up and ran his car into another student’s motorcycle. There was zero reason to rush, and yet he had let go fever hit him.

Taking the extra 5 minutes has a number of benefits. It gives you the opportunity to catch your breath and organize the thoughts in your head. It gives you time to collect more data. It also sometimes gives the situation itself time to resolve.

But, and Brandon touched upon this a bit, and I’ve talked about it in my own talk “Who’s Flying the Plane”, often for this, you need strong support from management. Management obviously wants problems fixed, as quickly as possible. This often means management puts pressure on us IT folks to jump into action. This can lead to bad outcomes. I once had a manager who told my team (without me realizing it at the time) to reboot a SQL Server because it was acting very slowly. This was while I was in the middle of remotely trying to diagnosis it. Not only did this not solve the problem, it made things worse because a rebooting server is exactly 100% not responsive, but even when it comes up, it has to load a lot of pages into cache and will have a slow response after reboot. And in this case, as I was pretty sure would happen, the reboot didn’t solve the problem (we were hitting a flaw in our code that was resulting in huge table scans). While non-fatal, taking an extra 5 minutes would have eliminated that outage and gotten us that much closer to solving the problem.

Brandon also gave a great example of a corrupted index and how easy it can be to solve. If your boss is pressuring you for a solution NOW and you don’t have the opportunity to take those 5 minutes, you might make a poor decision that leads to a larger issue.

My take away for today is three fold:

  1. Be prepared to take 5 minutes in an emergency
  2. Take 5 minutes today, to talk to your manager about taking 5 minutes in an emergency. Let them know NOW that you plan on taking those 5 minutes to calm down, regroup, maybe discuss with others what’s going on and THEN you will respond. This isn’t you being a slacker or ignoring the impact on the business, but you being proactive to ensure you don’t make a hasty decision that has a larger impact. It’s far easier to have this conversation today, than in the middle of a crisis.
  3. If you’re a manager, tell your reports, that you expect them to take 5 minutes in an emergency.

Changing Technologies – T-SQL Tuesday

Select <columns> from Some_Table where Condition=’Some Value’

T-SQL Tuesday Topic

The above statement is pretty much the basis of what started my current career. Of course it actually goes back further than that. I have a Computer Science Degree from RPI. So I’ve done programming, learned hardware and more. I even took an Intro to Databases course while at RPI. I still recall the professor talking about IBM and something called Structured Query Language. The book had a line that went something like “while not the most popular database technology, its use may grow in the future.” Boy did it.

When I first started working with SQL Server, it was 4.21 and for a startup. I had a lot to learn. Back then, a lot was by experience. Sometimes I made mistakes. But I learned.

When I started at that startup, if one could write basic queries and backup and restore a database, one was a half-way decent DBA. Knowing how to tune indices was a definite bonus, as was knowing things like how to set up log-shipping and replication.

Back then, besides experience, I learned new stuff two ways: SQL Server Magazine and the SQL Connections conference. Work paid for both. It was worth it. But honestly, there wasn’t too much to learn. But there also weren’t as nearly as many resources as there were today.

Fast forward 30+ years and here I’ve written a book, worked for several startups, regularly write about databases and database related topics, and often presented at User Groups, SQL Saturdays and at the now defunct PASS Summit. Today as a consultant I regularly touch the SQL Server Query Engine, SSAS, SSRS, SSIS, use PowerShell, write the occasional C# and VB.Net, sometimes do work on a Linux machine or VM and more. A lot has changed.

Obviously the technology has changed. So how have I responded? By doing what I said above. This may sound like a tautology or even circular reasoning but it’s true. When I would go to a SQL Saturday, I’d often attend 3-5 different topics. I’d learn something. But then I started presenting. And that forced me to learn. As much as I may like to think I know about a topic, when I go to present about it, I like to ensure I know even more. This forces me to read white papers, other articles and perhaps attend other sessions.

When I’ve written an article, I’ve often had to do a lot of research for it.

So strangely, I would say a bit part of keeping my skills up to date is not just learning from others, but from teaching. Teaching forces me to keep my skills up.

In summation, I’ve responded by learning from others, but also forcing myself to teach myself before I taught others. It’s a feedback loop. The more technology changes, the more I reach out and learn and the more learn, the more I do outreach.

The impetus for this week’s blog was Andy Leonard’s call for a T-SQL Tuesday topic.

Hiring

Not sure why it came to mind last night, but I was thinking of the best hire I never made. This expanded into me thinking about folks I have hired over the ages. As a Director of IT and later a VP of IT, I’ve had to make a lot of hires over the years, some better than others. Even when I can’t remember their names (an unfortunate weakness of mine) I can almost always remember their faces and how they worked out. And fortunately, most of them worked out quite well, even the ones who surprisingly might think they didn’t.

Looking back, I would say there was probably only one person I absolutely should not have hired and she was the only person I ended up having to let go because of performance issues. There were a few how were less than stellar, and a few I had to let go because of budget cuts, but even those weren’t necessarily bad hires.

But then there’s the one that “got away” and honestly, when I reflected upon it, I was glad, for both of us. Back in the early days of the first dotcom bubble I was working for a company that was quickly expanding. I can’t recall how many interviews a day I was doing, but it was a lot. We were looking to ramp up quickly and I couldn’t afford to be too picky. That said, some of my best hires came during that period.

In this case she was an ideal candidate, both on resume and in person. She had a great college background, ticked all the checkmarks in terms of classes taken and experience. She did great during the interview, both technically and in terms of how I thought she’d be for the team I was looking to build. In fact, looking back, I think she would have been the first member of said team and as such would have been a good role model for others.

There was only one issue, and we both recognized it in time. We were a startup. We didn’t ask that stereotypical (and I think bad) question of “where do you see yourself in 5 years?” because, heck, we didn’t know where we’d be in 5 years. We didn’t have a clear career path of growth for employees. I mean it was obvious we’d grow and there would be steps up, but there was no clear org chart.

On the other hand, companies like GE, especially back then, had a very clear progression path. If you wanted management, you knew the path to take and it was pretty clear that both parties would work to make it happen.

And, it became apparent, she wanted to know where she would be in 5 years. And there was absolutely nothing wrong with that. We made her the offer, but I half-hoped she’d turn it down and was relieved in some ways that she did. Yes, she would have been a great hire for us. However, honestly, for her own career, it probably would have been a mistake.

But, I have to wonder what things would have been like had she joined the team. She would have been great. She’s the one that got away. And I’m OK with that.