Sending emails from Powershell using SQL Server 2012

First off, let me say there is an easier way to send email messages from Powershell, where you don’t have to use SQL Server. If sending email is all you want to do, you can try this.

I, on the other hand had a more unique situation. I was working with a bunch of scripts running on different servers, and I wanted to be able to send emails and log information about their execution. Precisely, I needed something that could tell me:

  • What email messages were sent out, to who, and at what time.
  • How many of them got sent successfully, and how many didn’t
  • What error caused a message to fail

And it just so happens that the Database Mail feature in SQL Server can do all of these things, so I decided to test it out.

Configuring SQL Server Database Mail

To send emails with SQL Server, you need to create a profile, and then set up an account for that profile. You can do this programmatically using the stored procedures "msdb.dbo.sysmail_add_profile_sp" and "msdb.dbo.sysmail_add_account_sp" to create new profiles and accounts respectively. But I prefer to use the Configuration Wizard, its just easier that way. Plus, the wizard automatically enables the Database Mail feature on SQL Server for you. If you decide to use T-SQL to configure your Database Mail, you will have to enable it first using the following code:

But if you prefer GUI, you can follow the screenshots.

Now Database mailing should be enabled for you. You can test that everything is working by right clicking on “Database Mail” and selecting “Send Test E-mail…”.

If everything was set up correctly, your test email should deliver successfully. Next we will connect to the database and run the system procedure for sending email “msdb.dbo.sp_send_dbmail”, using the following code:

Copy the code to a Powershell file, let’s call it “db_mail.ps1”. Replace the values in ConnectionString and Profile with the real thing, and run the script. Example:

Note:If you haven’t executed Powershell scripts on your system before, you may need to set the execution policy that will allow you to do so.

So that’s it. The emails should have started dropping.

And if you need to check what’s going on with the emails you have been sending, The system views "msdb.dbo.sysmail_allitems" and "msdb.dbo.sysmail_event_log", got you covered.

You should be able to see all email messages that have been sent from your database server, along with some very useful information.

Using Facebook comments with comment moderation on your website

This is one that I honestly thought would be as simple as taking a code snippet and pasting it somewhere on your site a la Disqus, but as I have come to find out, there is a bit more trickery to it.

It goes without saying, that in order to use Facebook comments on your website, you have to have a Facebook account. But if you are one of the few people on earth without a Facebook account, head over to Facebook and create one pronto.

The Facebook comments plugin requires an App ID, so you are going to need to have a Facebook app. If you already have one, good. The comments plugin will be using it shortly. If you don’t have a Facebook app, we are going to create one in a minute.

Login to your Facebook account, navigate to the developer apps page and click the “Create New App” button.

A pop-up box will appear next, asking you to enter your App name. You can type in anything here, you are not really creating an App people are going to use. You only need the App for its App ID. For the purposes of this post, the App will be called “Website_comment”. Facebook checks that the App Name you are using isn’t currently in use, so your App name will have to be unique.

Next you will most likely be asked to enter Captcha information for security purposes, but after that your App should be created.

Your App “Display name” and “Contact email” is usually pre-loaded, you will have to add a value for “App Domains” in the Basic info section. This is going to be the domain name where you want to use the Facebook comments plugin. Don’t bother adding the “http://” or “www.” here, just enter the last 2 levels of the domain name. So “http://www.abc.com” will be “abc.com”.

In the “Select how your app integrates with Facebook” section, enter a value for Site URL in “Website with Facebook login”. This should be your domain name with all the bells and whistles, so abc.com will be “http://abc.com”. After this, hit Save.

Now your app is ready, and we have an App ID, so we can proceed further. Proceed to the Facebook comment plugin page. Scroll to where you set up the plugin, modify the default values and click on “Get Code”.

A pop-up will appear with code that you have to add to your website. The code uses the App ID for the App you just created.

The pop-up is pretty self explanatory. Copy the code in the first section into your website, just below the <body> tag, and place the code in the second section wherever you want the plugin to appear on your website. After making the modifications to your website, Facebook comments should now be available on your website.

Moderating comments

To keep away the trolls and spammers, comment moderation is important. Luckily Facebook comments has features that can handle this quite effectively. To activate this on your website add the following code snippets to the <head> section of your website:

Now head over to https://developers.facebook.com/tools/comments

Any comment that is posted using the Facebook comment plugin on your website can be viewed and managed here. We are going to be making a few modifications to the default comment settings. Click on the “Settings” link and a pop-up box should appear.

To turn on Moderation, change “Moderation mode” to “Let me approve every comment before it appears”. With this setting, comments will only become public domain after you have approved them. However, the person that posted the comment and friends of that person will always be able to see the comment regardless of your moderation.

You can also have more than one person moderating the comments on your website by adding them in the “Moderators” fields. They will also get notifications when a new comment is posted, the notification brings you to back to this Comment moderation tool where you can approve or hide the comment that was just posted.

You can activate moderation only when certain words are detected in a comment. To use this feature, leave the “Moderation mode” field option at “Make every post public by default” and change the default “No restrictions” setting in the “Blacklisted words” field to “Create custom list”, which will then present a text box to you so you can enter your restricted words. Alternatively, you can use the “Standard Facebook restrictions” option if you want to ride on the restricted words list already defined by Facebook. Leaving the default “No restrictions” option means all comments will be moderated regardless on content.

To allow your user to post comments using other networks besides Facebook, check the “Other Login providers” box. Now they will have the option of commenting using their Yahoo, Hotmail, AOL as well their Facebook accounts.

Comment moderation can also take place on the website itself, you don’t have to come to Facebook to approve every comment that needs moderation, it can be done on the website. Once the Facebook comment plugin detects that the person logged into Facebook currently is defined as a comment moderator, you will be able to moderate comments right there on the site.

How to point your Namecheap domain to your Tumblr blog

If you were like me, trying to find a way to get your tumblr blog to point to your Namecheap domain name, and not finding any resources that catered to your exact need. You’re in luck. I am going to walk you through the process that took me a while to grasp, and hopefully you should get your tumblr blog pointing to your domain in a matter of minutes, and not in 3 days as suggested by the official tumblr documentation.

OK first off, let me re-iterate something that was said in the Tumblr documentation. You do not need to change your name server to get this to work. In fact Namecheap said it explicitly: "Please note that you can only set up URL forwarding for a domain using Namecheap default nameservers." So, touching anything that has to do with your domain nameservers is a complete no-no.

Next, we are going to be configuring A-records and CNAME records for your domain, so to start you will need to get the tumblr values for this configuration entries. Currently Tumblr says:

A-record: 66.6.44.4

CNAME: domains.tumblr.com

These values have changed before, so its always a good idea to check what’s current. You can get that here

Now head over to Namecheap.com, log into your account and select the domain that you want to configure for Tumblr.

Under the Host management section for the domain you just selected, click on “URL Forwarding”.

Now modify the entries to look like the one pictured below. Make sure you are using the most current values for your A-record and CNAME entries. 

Note: When you enter “domains.tumblr.com” for your CNAME record, Namecheap always adds a dot(.) after it, if you didn’t add one yourself, to make it “domains.tumblr.com.”. This is not an error, CNAME values have to be fully qualified domain names, which always end with a trailing dot(.). So don’t waste any time trying to remove the dot(.), you can’t.

After saving your changes, its time to log into Tumblr and modify your blog settings to complete the process.

Click on the gear icon to get to your settings page

On the left side bar, click on the blog you want to use your domain for, and select the “Use a custom domain name” checkbox. You can now enter the name of the domain you just configured. Make sure you add “http://” to it. So if your domain name is abc.com, you should type in “http://abc.com”.

Now test your domain to make sure everything is copacetic, which it should be if you did everything like I explained. Now all that’s left is for you to save, and in a couple of minutes, at most an hour, your domain should start pointing to your Tumblr blog.

PS:

1 - The time it takes for URL Forwarding to work for your namecheap domain may vary, but according to a Namecheap documentation on the subject, it really should take a couple of minutes for the configuration to take effect.

2 - If you are trying to do something different and are wondering if the other options in RECORD TYPE have the answer, consult this Namecheap documentation. They have really good explanations of what the different record types do.

1 of 1