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

Inverse square root

https://pastebin.com/4Afm45AH

https://en.wikipedia.org/wiki/Fast_inverse_square_root

www.beyond3d.com/content/articles/8/ www.beyond3d.com/content/articles/8/www.beyond3d.com/content/articles/15/ www.beyond3d.com/content/articles/15/www.lomont.org/Math/Papers/2003/InvSqrt.pdf www.lomont.org/Math/Papers/2003/InvSqrt.pdfbetterexplained.com/articles/understanding-quakes-fast-inverse-square-root/betterexplained.com/articles/understanding-quakes-fast-inverse-square-root/

Even I still adore Data Particles I “invented” almost 10 years ago, without proper tools (I struggle to create) it's quite hard to manage the data. I encounter it from time to time when I need to fix some order in our intranet app.

So for QB I decided to move from Data Particles to JSON structure I created for Qedy. It suits the purpose rather well and I can improve it to be mutually beneficial.

1. float Q_rsqrt(float number)
2. {
3. long i;
4. float x2, y;
5. const float threehalfs = 1.5F; x2 = number * 0.5F; y = number; i = *(long *) &y; // evil floating point bit level hacking i = 0x5f3759df - ( i >> 1 ); // what the fuck? y = *(float *) &i; y = y * (threehalfs - (x2 * y * y)); // 1st iteration // y = y * ( threehalfs - ( x2 * y * y ) ); // 2nd iteration
6. return y;
7. }

I created a simple parser, that runs for every ID, which is time consuming, but there's no rush.

Square root

One common method for approximating square roots with good performance is the Newton-Raphson method. Here's a simplified example:

1. function sqrt(num) { guess = num / 2.0; // Initial guess // Iterate 5 times to refine the approximation for (i = 0; i < 5; i++) guess = 0.5 * (guess + num / guess)
3. return guess;
4. }

I recreated this function in Excel (=0,5*(B2+$B$1/B2)) to see how many passes start to produce the same output. Number up to 20 are usually fine with 3, but higher the number is, more iterations it requires to get to it. Input around 100k need 9 passes, around 1M 13 passes.