Sending e-mail alerts with Microsoft SQL Server Express

Our client migrated from ancient SQL Server 2008 R2 to 2017 Express. Limit of 4 cores, 1 GB RAM and 10 GB storage is not problem, it's rather small app and Express edition is free even for commercial use.

There are some things missing, but fortunately all I needed have a workaround. The toughest time I had migrating all the date conversion method combos (using CONVERT, GETDATE or DATEADD) which were incompatible for some reason and silently broke many procedures.

With e-mail notifications it was mostly a lot of unknowns for me, because I'm not a database specialist nor server admin (albeit Windows Server).

SQLMail

It may not have a GUI, but it still works. You only need to configure it via SQL commands to msdb.dbo.sysmail*. Bojan Petrovic wrote an excellent tutorial on SQLShack.

1. EXEC msdb.dbo.sp_send_dbmail @profile_name = @from_profile_name, @recipients = @to_recipients_mails, @copy_recipients = @to_copy_recipients, @blind_copy_recipients = @to_blind_copy_recipients, @subject = @subject, @body_format= 'html', @body = @mailbody;

Scheduler

I created a BAT file and added it to Task Scheduler to run every day at 7:00. In there is a call to SQL Server on “dbserver.local” machine using sqlcmd command. It will call p_alert procedure in database “dbname” with argument 123:

1. sqlcmd -E -S dbserver.local -d dbname -Q "EXEC dbo.p_alert 123"

For @out variables you can pass null: "EXEC dbo.p_alert null".

Error handling

On this topic I found another great article by Bojan Petrovic on SQLShack.

I wanted to log errors into a table, so I created it:

1. CREATE TABLE _log_db_err (ErrorID INT IDENTITY(1, 1), UserName VARCHAR(100), ErrorNumber INT, ErrorState INT, ErrorSeverity INT, ErrorLin INT, ErrorProcedure VARCHAR(MAX), ErrorMessage VARCHAR(MAX), ErrorDateTime DATETIME) GO

And then used classic TRY/CATCH approach:

1. BEGIN TRY ... END TRY BEGIN CATCH INSERT INTO _log_db_err VALUES (SUSER_SNAME(), ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_MESSAGE(), GETDATE()); END CATCH END

• sql

UPSERT

This strange word is a composition of UPDATE and INSERT. It's a technique, when INSERT is not possible, mostly due to key conflicts, so UPDATE is performed instead.

ON DUPLICATE KEY UPDATE

The most common UPSERT is 

1. INSERT INTO table (column_names) VALUES (data) ON DUPLICATE KEY UPDATE column1 = expression, column2 = expression...;

asdf

INSERT IGNORE

asdf

1. INSERT IGNORE INTO table_name (column_names) VALUES ( value_list), ( value_list) .....;

asdf

REPLACE

asdf

1. REPLACE [INTO] table_name(column_list) VALUES(value_list);

asdf

• ⮤ Newer post
• sql
• todo

Working with foreign tables

I consider this blog as an inspiration for others, not just as a promotion of QetriX ;-) so let me write a post about one of my former ideas for 3rd generation of my CMS (year 2007).

I was on a quest for automated database layer, so I came up with automated table joining without usage of “advanced” database features (designed for MyISAM engine).

From my former job I adopted a system of three-letter prefix for column names according to table name. The motivation for this was to have unique column names throughout the whole database schema. If I have table “users” (I use plural for table names), the prefix would be “usr”, so primary key would be “usr_id”. For PK in table “companies” it would be “com_id” and so on. If the table had similar name and the prefix was already taken, I’d use the next letter in order, so for “computers” along with “companies” it would be “com” and “cop” respectively.

For foreign keys I used even more specific approach, I used both table and prefix names from foreign table in the column name (plus “fk” suffix), so if user had relation to company, the column name in table “users” would be: “usr_companies_com_fk”.

Thanks to this I was able to specify $db->getDataFrom("users") and it would return data from all joined tables as well. For this purpose I had cached database schema (like “information_schema.tables” in MySQL).

And that’s it :)

Fun fact: The system was so automated, that when you deleted a row in one table, all referenced rows in all foreign tables has been deleted as well (incl. domains, users, locations etc). facepalm

• ⮤ Newer post
• sql