Wstęp
Powiadomienia e-mail w systemach informatycznych pełnią kluczową rolę wpływającą na bezpieczeństwo oraz wygodę użytkownika. Automatyzacja przepływu informacji pozwala na skrócenie czasu reakcji w przypadku awarii do minimum. Dodatkowo monitoring danych wysyłanych ze środowisk może ułatwić optymalizację systemów.
Dobrą praktyką stosowaną we wdrożeniach oprogramowania jest zatem przygotowanie odpowiedniego systemu powiadomień. W przypadku Comarch ERP XL jest używana baza danych od Microsoftu, zatem konfiguracja wysyłki e-mailów wykorzystywanych przez aplikację znajduje się w Microsoft Managment Studio. W artykule przedstawię jak poprawnie ustawić wysyłkę powiadomień na serwerze Microsoft SQL Server.
Do skonfigurowania powiadomień mailowych na serwerze SQL będziesz potrzebował:
- Pełna wersja MS SQL Server (wersja SQL Express nie posiada funkcji wysyłki poczty)
- Konto administracyjne do SQL Server
- Dane dostępowe serwera SMTP i konto do wysyłki poczty email.
Zastosowanie
Wysyłka poczty e-mail z poziomu SQL Server ma wiele zastosowań, w tym:
- Powiadamianie użytkowników programu ERP XL o wystąpieniu pewnych zdarzeń w programie, np. kończący się termin ważności towaru, przekazanie realizacji zamówienia do produkcji, kończący się stan towaru na magazynie.
- Powiadamianie adminitratora programu ERP XL o potencjalnych zagrożeniach w działaniu systemu, np. nieudane wykonanie kopii zapasowych, niedziałanie aplikacji do aktualizacji kursów walut, nieudana synchronizacja ze sklepem internetowym.
- Cykliczne raporty do kierowników i menadżerów, np. sprzedaż poniżej marży, przeterminowane faktury, liczba wystawionych dokumentów przez operatorów.
- Powiadamianie klientów firmy o wystąpieniu pewnych zdarzeń w programie, np. kończący się termin płatności, przekazanie zamówienia do realizacji, potwierdzenie wysyłki towaru.
Ponadto z powiadomień mailowych korzystają niektóre komercyjne aplikacje Prospeo:
- e-Faktura - powiadomienie jeśli program nie uruchamia się automatycznie od pewnego określonego czasu
- e-Windykacja - powiadomienie jeśli program nie wysyła przypomnień o należnościach od pewnego określonego czasu
- Aktualizator kursów walut - powiadomienie jeśli program nie pobrał ostatniego kursu waluty
Dane skrzynki e-mail
Przed podjęciem prac nad konfiguracją wysyłki należy przygotować konto z którego system będzie mógł skorzystać. Potrzebujemy następujących danych:
- Nazwa konta wraz z hasłem – dane logowania do konta
- Nazwa e-mail/wyświetlana nazwa – w zależności czy chcemy korzystać z nazwy email konta aktualnie wykorzystywanego, czy chcemy używać nazwy innej skrzynki (po wcześniejszym ustawieniu uprawnień wysyłki jako dana skrzynka)
- Nazwa serwera – nazwa serwera SMTP z którego będzie wysyłana wiadomość (można to znaleźć w dokumentacji lub na stronie dostawcy)
- Port – port z którego będziemy korzystać przy wysyłaniu wiadomości (w zależności czy używamy TLS – port 587 lub nie – port 25)
Do wysyłki możemy wykorzystać praktycznie każdą skrzynkę pocztową niezależnie od dostawcy, lecz w niektórych przypadkach mogą być niezbędne do podjęcia dodatkowe kroki. Przykładowo przy skrzynkach gmaila należy w ustawieniach odblokować opcję dostępu ‘mniej bezpiecznych aplikacji’, inaczej google może blokować przepływ wiadomości danego konta z niektórych aplikacji oraz systemów.
Konfiguracja poczty na serwerze SQL
Mając wszelkie niezbędne dane oraz uprawnienia, możemy przystąpić do konfiguracji skrzynki w Managment Studio. Po zalogowaniu się do serwera przechodzimy do drzewa Managment – Database Mail.
- Set up Database Mail – Opcja pozwalająca na stworzenie nowego profilu dla konta skrzynki pocztowej
- Manage Database Mail accounts and profiles – Pozwala na edycję istniejących profili, dodanie lub edycję skrzynek pocztowych
- Manage profile security – Umożliwia zarządzanie uprawnieniami do profili
- View or change system parameters – Możemy zmienić tutaj ustawienia systemowe wysyłki
Dla stworzenia nowego profilu wybieramy opcję pierwszą. W tym momencie przejdziemy do okna dodania profilu:
Kliknięcie przycisku Add pozwala na przypięcie konta skrzynki pocztowej do profilu. Z tego miejsca możemy wybrać aktualnie istniejącą skrzynkę w systemie lub dodać nową.
W przypadku tworzenia nowej skrzynki mamy do uzupełnienia następujące pola:
Uzupełniamy wszystko wedle danych które zebraliśmy wcześniej. Dodatkowo w zależności czy e-mail jest obsługiwany wewnętrznie lub zewnętrznie, zaznaczamy odpowiednią autentykację.
Po przypisaniu konta do profilu kolejnymi krokami są uprawnienia do profilu oraz informacje techniczne systemu wysyłki. Ustawienia standardowe są jak najbardziej ok w klasycznych wdrożeniach.
Po przejściu wszystkich kroków mamy gotową konfigurację pozwalającą na wysyłkę wiadomości email. Teraz możemy w prosty sposób przetestować system klikając prawym przyciskiem na Database Mail i wybierając opcję „Send Test E-Mail”. Wybieramy odpowiedni profil z listy, dopisujemy nadawcę oraz ewentualnie edytujemy treść wiadomości testowej.
Jeżeli wszystkie dane zostały poprawnie uzupełnione adres wpisany w polu „To” powinien dostać wiadomość.
W razie problemów możemy zajrzeć do logów, czyli skorzystać z opcji „View Database Mail Log” klikając prawym przyciskiem na Database Mail.
Jak ustawić powiadomienie e-mail w Agencie SQL
Po prawidłowym skonfigurowaniu profilu ze skrzynką SMTP możemy ją wykorzystać w Agencie SQL. Umożliwia nam to przypisanie odpowiednich powiadomień dla dla wybranych operatorów w zakresie jednego lub wielu zadań (job) SQL.
Pierwszym krokiem jest aktywacja powiadomień oraz wybranie z listy odpowiedniego profilu:
Powoduje to przypisanie wybranego profilu jako domyślnego wykorzystywanego we wszystkich powiadomieniach agenta.
Kolejnym krokiem jest stworzenie operatora, wystarczy nadać mu nazwę oraz przypisać adres.
Dzięki poprzednim krokom jesteśmy teraz w stanie umożliwić wysyłkę odpowiednich powiadomień dla wybranego operatora w konkretnym zadaniu (jobie). Jesteśmy w stanie przykładowo ustawić powiadomienia w momencie niepowodzenia wykonania zadania.
W zakresie powiadomień agenta możemy jeszcze ustawić alerty dotyczące konkretnych baz SQL. Tworząc alert jesteśmy w stanie ustawić wysyłkę maila w razie wystąpienia konkretnego wskazanego przypadku:
Przypadki możemy wybrać z listy, lub również możemy samemu wskazać numer błędu.
Jak wysłać e-mail z poziomu składni SQL
Po odpowiednim skonfigurowaniu profilu dla Database Mail możemy go wykorzystać równieć z poziomu kodu SQL. Wykorzystamy do tego standardową procedurę "sp_send_dbmail". Podstawowa składnia wygląda w następujący sposób:
declare @bodyTekst varchar(8000) select @bodyTekst='Łaczna liczba wystawionych faktur wynosi ' + convert(varchar(10), count(*)) from ERPXL_BAZA.cdn.tranag where trn_gidtyp=2033 EXECUTE [msdb].[dbo].[sp_send_dbmail] @profile_name = 'Alert ERP XL', @recipients = 'michal.zurkowski@prospeo.com.pl', @body = @bodyTekst, @subject = 'Komunikat z programu ERP XL'
Potrzebujemy stworzyć jedną zmienną, odpowiadającą za test wysłanej wiadomości. Do tej zmiennej możemy przypisać dowolny wykonywalny kod SQL, dla przykładu ten tutaj pokaże nam ilość faktur z wybranej bazy danych. W kolejnych standardowych zmiennych dla procedury należy uzupełnić wymagane informacje do wysyłki, wskazujemy nazwę profilu, odbiorcę, treść wiadomości oraz tytuł wiadomości.
Standardowo wiadomość jest formatowana w tak zwanym plain text. Aby użyć formatowania HTML należy dodać odpowiednią standardową zmienną określającą formatowanie.
declare @bodyTekst varchar(8000)
set @bodyTekst=
'<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Raport CDNXL</title>
<meta http-equiv="Tabelka" content="text/html;charset=UTF-8"/>
<STYLE TYPE="text/css"> TD { font-size: 12; } </STYLE>
<STYLE TYPE="text/css"> TH { font-size: 13; } </STYLE>
</head><body>
<TABLE cellspacing="2" cellpadding="2" width="800" border="1">
<CAPTION>Tabelka</CAPTION>
<TR><TH>Kolumna 1<TH>Kolumna 2<TH>Kolumna 3<TH>Kolumna 4<TH>Kolumna 5
<TR><TD>Mail<TD>SQL<TD>ŚwiatXL<TD>Artykuł<TD>Powiadomienia
</TABLE><BR>
</body>
</html>'
EXECUTE [msdb].[dbo].[sp_send_dbmail]
@profile_name = 'Alert ERP XL',
@recipients = 'michal.zurkowski@prospeo.com.pl',
@body = @bodyTekst,
@subject = 'Komunikat z programu ERP XL',
@body_format='HTML'
Dodajemy zmienną body_format, określamy ją jako HTML, dzięki czemu treść maila może zawierać kod HTML. Efektem takiej wiadomości jest prosta tabelka.
Kod ten używający procedury sp_send_dbmail możemy w prosty sposób wrzucić np. w job SQL, dzięki czemu będziemy mogli ustawić cykliczne powiadomienia o interesujących nas danych z baz ERP XL.
Podsumowanie
Powiadomienia mailowe są niezbędnym system przy pracy z SQL serwerem, umożliwiając wykorzystanie ich na wiele przydatnych sposobów. W artykule wspomniałem między innymi o komunikatach wysyłanych w związku z błędami, ale równie dobrze można raportować codzienne wykonywanie backupów lub statusy i wartości zwrotne danych zadań (jobów). Powiadomienia w prosty sposób ułatwiają codzienny monitoring serwera pozwalając zabezpieczyć się przed niepożądanymi sytuacjami.
