How to send email alerts via Task Scheduler in Server 2012 R2

Posted on Updated on

In Server 2008 R2 task scheduler, the send email feature is available as an option.  And I have used this feature a lot mostly for my phone system servers because I get real time alerts whenever there is a potential issues on switches and servers.

When 2012R2 came out, the send mail feature was not longer available.

I am making this post to help others out there with the same issues and hopefully you will find this post useful to you.

Here are the procedures:

1.  Create a powershell script to send emails, the entries for Attachment, SMTPPort, and UseSSL are optional unless required.


$From = “”

$To = “”

$Cc = “”

$Attachment = “C:\temp\Some random file.txt”

$Subject = “Subject here”

$Body = “Body of the email here”

$SMTPServer = “”

$SMTPPort = “52”

Send-MailMessage -From $From -to $To -Cc $Cc -Subject $Subject -Body $Body -SmtpServer $SMTPServer#-port $SMTPPort -UseSsl #-Credential (Get-Credential) # -Attachments $Attachment


2.  Next is to create your scheduled task, and under Actions, add the powershell script to send the email.




This is very simple yet very useful to anyone that wants to setup a scheduled task that also sends email alerts.  Although you can have SCOM send those alerts, it is still useful if you can do it directly from the server.

Please do not forget to leave me a comment if you see any corrections, or if you have any suggestions.  Or better, if you like this post, please feel free to let me know.  It won’t cost you anything.


SQL SSRS: creating multiple parameters and allowing search on single or multiple entries

Posted on Updated on

I built an SSRS report for one of my colleagues and it took me a little bit to figure how to properly setup the Parameters.  I am hoping this would help somebody out there that may run into same issue.

Here is the scenario:

I created 4 parameters named ‘Asset Barcode, Asset Serial, Purchase Order and Former Tag’.  And if all these 4 parameters are set to blank, then all data should be displayed.  And if any of the parameters are blank, then it will not be included in the query.

For the past couple of days, I have been testing and searching online on how to get this working, but it seems that a lot of people are suggesting to create a stored procedure which is very cumbersome.  Without the correct script, the blank parameters are still being included in the query and thereby not giving an accurate result.

Here is how my script looks like that worked:

NOTE: DO NOT copy and paste the code below, the commas and quotes are not formatted correctly in WordPress.

,Assets.[General/Asset Name]
,Assets.[Assets/Inventory Date]
,Assets.[Assets/Data Source]
,Assets.[Assets/Serial Number]
,Assets.[Assets/Former Tag No]
,Assets.[General/Purchase Order]
,Assets.[Employee Details/Employee Notes]


(@Asset_Barcode = or Assets.[General/Barcode] LIKE ‘%’ + @Asset_Barcode + ‘%’) and
(@Asset_Serial = or Assets.[Assets/Serial Number] LIKE ‘%’ + @Asset_Serial + ‘%’) and
(@Purchase_Order = or Assets.[General/Purchase Order] LIKE ‘%’ + @Purchase_Order + ‘%’) and
(@Former_Tag = or Assets.[Assets/Former Tag No] LIKE ‘%’ + @Former_Tag + ‘%’)

Merging two tables in a non-conventional way

Posted on Updated on

I found this an easy way to merge two tables together without using UNION, UNION ALL.  Here is a summary that one of our admins are having that led me into creating this script:

We are using a hardware asset manager software, and the vendor did all the data migration from the old version to the new version which also included a migration of data to a new database with different table schemas.  The migration went fine, but our admin noticed some values that were not migrated correctly.  So on the new database, there are some missing data.

Instead of using the UNION or UNION ALL to merge the old and new database.  I decided to create my own script.  Which also made more sense for us, since we only wanted to update the data that are on the new database and not add junk to it.  The old DB has 10,000 records and the new DB has only 7,000.  The old DB included the retired assets that we no longer want.

So here is how I did it in a script: (I will leave the table and database name as is)

You may have to fix the single and double quotes after pasting it to SMC as wordpress formatted the characters differently

use vam_master

DECLARE @T TABLE (SQL varchar(1000))


SELECT ‘UPDATE dbo.assets SET AssetSerialNo=”’ + [assets/serial number] + ”’ WHERE Barcode=”’ + CAST([general/barcode] as varchar(10)) + ””

FROM dbo.assets

where [general/barcode] is not NULL


SELECT ‘UPDATE dbo.assets SET PurchaseOrderNo=”’ + [general/purchase order] + ”’ WHERE Barcode=”’ + CAST([general/barcode] as varchar(10)) + ””

FROM dbo.assets

where [general/barcode] is not NULL


SELECT ‘UPDATE dbo.assets SET Model=”’ + [assets/equipment/model] + ”’ WHERE Barcode=”’+ CAST([general/barcode] as varchar(10)) + ””

FROM dbo.assets

where [general/barcode] is not NULL


SELECT ‘UPDATE dbo.assets SET mfg=”’ + [assets/mfg] + ”’ WHERE Barcode=”’+ CAST([general/barcode] as varchar(10)) + ””

FROM dbo.assets

where [general/barcode] is not NULL

select * from @T

Running this script will output results like ‘UPDATE dbo.assets SET AssetSerialNo=’blah’ WHERE Barcode=’blah’.  So you can then run this against the new DB and it will update the table with missing values for what you have selected and just ignore those entries that do not exist on the new DB.

I hope this helps, if you are on the same situation as me.

Shoretel Voicemail notification setup

Posted on

There are several ways to enable voicemail notifications via email in Shoretel.

Here are the things you will have to set on the user profiles:

1.  In Shoreware Director, Administration, Users, Individual Users; on each of the user profiles, specify the user’s email address in the General Tab.

2.  Under the General tab, click the link for ‘Escalation Profiles and Other Mailbox Options’ and specify the user’s email address and ‘Deliver Message as Email’ option.

Here are the settings for the server:

1.  Under Voicemail, Options; specify the ‘From Address for Email Notifications’

2.  If you are using a Voicemail switch, go to Sites and open your Headquarters Site.  And then specify the SMTP relay which is your Exchange server address or your smart host.

3.  If you are using your Shoretel server for voicemails, login to your server and open IIS6.0.  Expand your server and right click on SMTP Virtual Server and select Properties.  Under the Delivery tab, click Advanced and enter your Exchanger server or Smart Host address under Smart Host.  You will have to restart IIS after making the changes.  The voicemails will be saved in the queue folder under C:\inetpub\mailroot as they are being sent.

Exchange 2010 and MaxInbound ConnectionPerSource

Posted on Updated on

The exchange receive connectors do have settings for Maximum Inbound connection which is set to 20 by default.  If a server allowed to send emails reach this limit, you would see a log on the Exchange server.

Event 1021, MSExchangeTransport

Receive connector ‘name of connector’ rejected an incoming connection from IP address ‘server ip’.  The maximum number of connections per source (20) for this connector has been reached by this source IP address.

To verify the status of the receive connector, run the following cmdlet:

Get-ReceiveConnector ‘name of connector’ | select name, maxinbound*

To adjust the limit of the receive connector, run the following cmdlet:

Set-ReceiveConnector ‘name of connector’ -maxinboundconnectionpersource <value>

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id"

Posted on

This post helped me with my issue on one of my jobs. Thanks

Mohsin's DBA Blog

Issue: When trying to delete a Scheduler job which was created as part of a maintenance plan, we get the following error

TITLE: Microsoft SQL Server Management Studio ------------------------------ Drop failed for Job 'manual_db_backups.Subplan_1'. (Microsoft.SqlServer.Smo) For help, click: ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'. The statement has been terminated. (Microsoft SQL Server, Error: 547) For help, click:

We were getting the error even after removing the maintenance plan from SSIS.

To Resolve I did the following:


-- View the Maintenance plan subplans 
select * from sysmaintplan_subplans

-- View the Maintenance plan logs 
select * from sysmaintplan_log

To Delete the subplan:


--Delete the Log history for the maintenance plan…

View original post 34 more words

Workaround for syspolicy_purge_history step 3 error on SQL 2012

Posted on Updated on

I have started setting up a couple of servers with SQL 2012 SP2.  And I noticed that the syspolicy_purge_history job is failing on Step 3 with the following error message:

A job step received an error at line 1 in a Powershell script.  The corresponding line is ‘set-executionpolicy RemoteSigned -scope process -Force’.  Correct the script and reschedule the job.


So far I have not seen any valid resolution online, so as a workaround, I have setup the Execution policy to UNDEFINED on the following registry entry:

Execution Policy, REG_SZ,Undefined

And as a warning, changing the execution policy to UNDEFINED may open security holes on your server.  If you found a fix for this issues, please feel free to leave a comment so I can update this post.

Update:  I found a Microsoft KB article that talks about adding a GPO to correct the issue.

Thank you