Handy Script: Testing Database Mail Configurations

Handy Script: Testing Database Mail Configurations

In my environment, we enable database mail on every instance. While we have very few clients or applications that use it, we use it to have our servers call for help when something goes wrong. That means it’s really important that database mail be configured properly. One of the things we have on our peer review checklist is to test database mail. One day, while working with a member of my team, I saw how cumbersome it was to click through the GUI to send a test email. This little script was quickly born:

declare @body nvarchar(1000)
declare @subject nvarchar(1000)

select @body = 'This is a mail test from '+@@servername
select @subject = 'Mail test from '+@@servername

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'dbmail',
@recipients='mhillwig@mycompany.org',
@body=@body,
@subject=@subject;

If you see something that shows your message was queued, it’s a good indication that SQL Server was able to send the mail message.

mailsend_test

 

The next step is to validate that the message was received. I can’t tell you how many times I’ve sent messages that just never made it through.

mailsend_test2

Handy Script: Confirming Objects Modified

Handy Script: Confirming Objects Modified

One of the things my team does frequently for clients is moving objects between environments. We have to migrate a procedure from DEV to Test, or we may move it from Test to Production. The migration is easy enough. But when responding back to the client, I hate the idea of saying “all set” or “done.” Instead, I’d rather show something that proves we did what we said we did. More importantly, I needed something that would work against multiple databases on multiple servers. That’s where this script came from.


set nocount on
go

DECLARE @dbname VARCHAR(30)
DECLARE @num_objects INT
DECLARE @object_type VARCHAR(3)

SELECT @dbname = 'MyDB' -- Use the database where the objects were moved
SELECT @num_objects = 1 -- Use the number of objects moved.
SELECT @object_type = 'P' -- Use P for procedures, F for functions, U for tables, V for views, etc.

DECLARE @SQL VARCHAR (1000)
select @@servername

SELECT @sql = 'select top ' + cast(@num_objects as varchar) + ' left(name,30) as object, object_id, modify_date from ' + @dbname + '.sys.objects where type = ''' + @object_type + '''
order by modify_date desc'

EXEC (@sql)

The top three SELECT statement are the parameters. It generates a nice, little output that shows the server, database, and object.  It also shows the object ID and the date/time of modification. That’s enough to paste into a ticket to let the client know we’ve fulfilled their request.

objects_modified_confirmation

This is actually one of the first little scripts I wrote when I joined this company. It’s such a little thing that’s very simple, but it’s served us very well for nearly four years.

Event Recap: SQL Saturday Madison #sqlsatmadison

Event Recap: SQL Saturday Madison #sqlsatmadison

SQL Saturday is synonymous with fun. With only one exception, I can honestly say that every event I’ve gone to has been a lot of fun. As someone who has been an attendee, speaker, and organizer, I’m part of a unique group of people that has a completely different perspective on these events.

Last weekend was SQL Saturday in Madison, WI. As I had written earlier, I was really excited about this event because it’s one I’ve always wanted to attend, yet the logistics never worked in our favor. This year, I finally made it to the land of cheese. And I wasn’t disappointed.

The organizers of the Madison event really had their challenges this year. From being at a brand new venue to some serious technical issues with the SQL Saturday website, they had their work cut out for them. And they came through with flying colors.

Friday night was their speaker event, and it was like a family reunion. I got to see a ton of old friends as well as make some new ones. But the highlight of that evening was seeing my dear friend Jes Borland.

jes_selfie

Friday night was life changing for me. I was introduced to something I’ve never experienced before–fried cheese curds. This is Wisconsin, where cheese is king. And having fried cheese curds for the first time was literally life changing. I can’t explain it other than saying they’re little flavor missiles of cheese with a mozzarella texture and a cheddar flavor. Then they beer batter them and fry them.


cheese

 

I went to Wisconsin for the SQL and fell in love with the cheese. I’m perfectly okay with this. Visiting the SQL Family was obviously a great benefit.

The event itself seemed to be run really smoothly. I think this is a testament to the leadership of the committee who ran it and the small army of volunteers they had. I did walk into the event with a little gift for my friend Jes. Anyone who knows her will say that this is absolutely and completely appropriate. She does love her coffee.

jes_coffee

My presentation of Do More with Less: Inside SQL CMS and MSX went really well. I was surprised at how well the questions came. Every question was like a softball because it was answered on the next slide.

After my presentation, I got to have a nice chat with my friend Adam Belebczuk, who knows more about Always On an Availability Groups than I do. He gave me some great insight into a problem I’m trying to solve in my own environment. That’s why these events are so magical. They’re full of people who seen problems that you’re just now facing. The friendship and fellowship of the speaker room is simply incredible.

Madison was a great time, but I was completely exhausted by the end of the day. I didn’t even take part in the after party. If they’ll have me back, I’ll certainly offer to speak in Madison again. I’ll do it for the cheese–and the friendship.

Handy Script: Find Running SQL Agent Jobs

Handy Script: Find Running SQL Agent Jobs

One of the things SQL Server doesn’t make all that easy is to know what SQL Agent jobs are running. At least that’s what I used to think.

After turning to multiple search engine queries, I was equipped with all kinds of complex scripts that parsed MSDB tables in all kinds of ways, but they never told me what I really wanted to know. What is running NOW. Then one day, I stumbled across this. I just wish I knew who had originally posted it so I could thank them.

exec msdb.dbo.sp_help_job @execution_status=1

This little thing tells me precisely which SQL Agent jobs are running at the moment, and that fills a great need in a lot of cases.

running_sql_agent_jobs

 

SQL Saturday Madison #sqlsatmadison

This weekend, I will be speaking at SQL Saturday Madison. This is incredibly special to me. I’ve been wanting to speak at SQL Saturday Madison for about three years. And every year, the events and Madison and Boston both fall on the same day. That means that Jes Borland (blog | twitter) and I will be at the same event. We have never been at the same SQL Saturday before.  If you know Jes and I, putting us in the same room is like a force of nature. We are an absolute riot together.

This year, we collaborated and made sure we didn’t schedule our events on the  same day. This means that I’ll be at SQL Saturday Madison on April 11 and SQL Saturday Boston is on April 18.

If you look at their schedule, it’s going to be an incredible day of training. As for me, I’ll be presenting Do More With Less: SQL Central Management Server and SQL Agent Multi-Server Administration.

I’m looking forward to two consecutive weekends with #sqlfamily.

Why Weather Matters #sqlsat364

Why Weather Matters #sqlsat364

It’s hard to believe that SQL Saturday #364 Boston 2015 is next weekend. At this point, pretty much everything is coming together. Sure, we’re going to have some challenges over the next week, but for the most part, the hard part is done. All I have to do is whip out my American Express card a few times. Other than that, Paresh Motiwala and his small army of volunteers will do the remainder of the hard work.

The one thing that really worries me at this point is completely beyond my control, and that’s the weather. Seriously.

One of the challenges of hosting a SQL Saturday in Boston is getting the date just right. If we do it anywhere between mid-October and late March, we run the risk of getting a snowstorm. If we host it from mid-May to mid-September, we lose a lot of people who will be on the cape or somewhere in New Hampshire or Maine. Oh, and we need to avoid hitting the weekends of Easter, Palm Sunday, and Passover. I also try to avoid hosting our event the same weekend as other big SQL Saturday events, which helps us avoid competing for speakers and sponsors. That gives us a really tight window of opportunity.

By having our event in mid to late April, our biggest risk is good weather. Yes, good weather. Our event looks like it’s going to be one of the first really nice weekends in the Spring. That means our dropout rate will be incredibly high. Let’s face it, if you’re having your first nice weekend of the season, would you want to be in a conference facility or out in the sunshine preparing your garden?

It seems completely perverse, but I’m actually hoping for rain the day of our event.

« Previous Entries