Thursday, January 17, 2019

T-SQL Database Alert/Mail System

I am working on a T-SQL "system" to send emails.  This will help so that in all the things that I use to send update emails, (ETL, sql jobs, etc.), I can just write a record in a table somewhere and the email will be handled for me.  Some benefits:

  • I can have the templates and recipients pre-defined so I dont have to worry about what to say. 
  • I dont have to worry about updating my SSIS packages or sql jobs when the text or recipients of the notification email needs to be changed. 
  • A history of the emails sent, to whom, and when (purely for taking up space, I am sure noone would doubt me if I said an email was for sure sent even though they didnt get it).
  • A queue that will allow the emails to be sent even if services are down (after they are recovered).

Read more: T-SQL Database Alert/Mail System

Write comment (0 Comments)

SSIS connecting to Oracle

Ohh boy - this is a biggie for me right now (actually it has been for a couple of weeks, but I have successfully put it off until today). :)

I have a situation where there are already SSIS packages, and sql statements that pull from an Oracle database into our SQL Server database.  The person who set them up left the company (a positive for him).  Therefore, I was scrambling when I started getting all these error messages that were basically laughing at me!

These are the steps I have had to do:

Read more: SSIS connecting to Oracle

Write comment (0 Comments)

Versions of SQL Server

Ever needed to know what version you are running - and what service packs were installed?  I found the following when I was searching for this exact question for myself.  It was very helpful.  I printed it out, so if it is ever is a url that is not found, I can put the data here instead.

Write comment (0 Comments)

SSIS - Same recordset into multiple tables

Are you wanting to put a set of data into multiple tables - without needing a primary key from the first table?  I ran into a situation where I had records that I wanted to insert into a table, but I wanted to be able to email people saying "hey look you need to do this, this and this because there are new records to look at!".  So, I wanted to use the same exact set of data and put it in 2 tables (1 as the actual source, the other to use as a mail queue).

Voila - Multicast!  It allows you to use the same recordset in multiple destinations.  Wonderful!

Hopefully this helps someone else who runs into this issue.


Write comment (0 Comments)

DBARoadmap- from Midnight DBAs

I was fortunate to receive a copy of the DBA Roadmap in the SQL Saturday event in Baton Rouge, LA, on Sat, Aug 4, 2012 ( It was perfect timing - I have enough skills to know I like DBA work a lot, but need a lot more learning/info before I would ever consider myself a DBA. I was thrilled and was really looking forward to going through it fully.

I have listened to the first couple of sessions of the roadmap now and like it a lot. Previous to the SQL Sat event where I met them, I had listened to a couple of their webcasts and videos, and love the banter between them. They make you feel engaged in the discussion even though you arent there - this does the same thing!

The introduction track is available to listen from the website, and well worth the time to listen to. It is right on target with what they present within the sessions.

Read more: DBARoadmap- from Midnight DBAs

Write comment (0 Comments)

Blackberry Curve - Level 1 Emails

I was looking to make the emails I get when a sql job or something fails (that is required to run) make an annoying noise to wake me up in the middle of the night (which is not an easy thing to do when you are dead to the world).  Here's what I found on setting some emails as Level 1 messages (from for the Blackberry Curve.  This actually works well - I use the subject line specifically,

Level 1 Messages - This helps to identify when any email message is received as high priority, both in and out of the holster.

Read more: Blackberry Curve - Level 1 Emails

Write comment (0 Comments)


Powered by mod LCA