ارسال و دریافت ایمیل با SQLServer راهنمای کامل POP3 و SMTP

ارسال و دریافت ایمیل با SQLServer: راهنمای کامل POP3 و SMTP

اگر نیاز به ارسال یا دریافت ایمیل با SQLServer دارید، احتمالاً با `xp_sendmail` یا `sp_send_dbmail` آشنا هستید. این روش‌ها برای سناریوهای خاصی مناسب هستند، اما محدودیت‌هایی دارند. برای مثال، `xp_sendmail` نیاز به پیکربندی سرویس SQL Mail در SQL Server دارد که در نسخه‌های جدیدتر منسوخ شده و `sp_send_dbmail` نیز تنها امکان ارسال ایمیل را فراهم می‌کند و قابلیت دریافت ایمیل ندارد. در دنیای امروز، نیاز به تعامل کامل با سرویس‌های ایمیل از داخل پایگاه داده می‌تواند حیاتی باشد.

چالش‌های موجود

محدودیت‌های داخلی SQL Server برای مدیریت ایمیل شامل موارد زیر است:

XP_SENDMAIL:

. نیاز به پیکربندی سرویس SQL Mail دارد که در نسخه‌های جدید SQL Server منسوخ شده است.
. در برخی از شبکه‌ها با پروتکل‌های احراز هویت قدیمی مشکل دارد.
. به دلیل مسائل امنیتی، اغلب در محیط‌های عملیاتی فعال نمی‌شود.

SP_SEND_DBMAIL:

. بخشی از قابلیت Database Mail است که یک سرویس ایمیل مبتنی بر SMTP در داخل SQL Server است.
. فقط قابلیت ارسال ایمیل دارد و از دریافت ایمیل پشتیبانی نمی‌کند.
. برای برخی نیازهای پیشرفته‌تر مانند ارسال پیوست‌های بزرگ یا مدیریت پیچیده‌تر محتوای ایمیل، ممکن است کافی نباشد.

یک راه‌حل جامع

برای غلبه بر این محدودیت‌ها، می‌توانیم از پروسیجرهای گسترش‌یافته (Extended Stored Procedures) شخص ثالث استفاده کنیم. این پروسیجرها (مانند `xp_smtp_sendmail` برای ارسال و `xp_pop3_getmail` برای دریافت) به صورت DLL نوشته شده‌اند و قابلیت‌های قدرتمندی را برای تعامل با سرورهای ایمیل POP3 و SMTP فراهم می‌کنند. این رویکرد انعطاف‌پذیری بیشتری در مدیریت ایمیل از داخل SQL Server ارائه می‌دهد، از جمله:
ارسال ایمیل از طریق SMTP: قابلیت ارسال ایمیل به هر سرور SMTP، با پشتیبانی از احراز هویت و پیوست.
دریافت ایمیل از طریق POP3: قابلیت اتصال به سرور POP3، دریافت ایمیل‌ها، تجزیه محتوا و ذخیره آن در جداول پایگاه داده.
مدیریت پیوست‌ها: امکان ارسال و دریافت فایل‌های پیوست.
گزارش‌گیری دقیق: ثبت و پیگیری وضعیت ارسال و دریافت ایمیل‌ها.

پیکربندی سرویس ایمیل در SQL Server

برای پیاده‌سازی این راه‌حل، ابتدا باید یک پایگاه داده و جداول لازم را برای ذخیره تنظیمات ایمیل، بدنه ایمیل، پیوست‌ها و لاگ‌ها ایجاد کنیم.

ایجاد پایگاه داده و جداول

مراحل اولیه شامل ایجاد یک پایگاه داده جدید (مثلاً MailDB) و سپس ساخت جداول زیر است:

MailAttachments: برای ذخیره پیوست‌های ایمیل.


CREATE TABLE MailAttachments
(
    AttachmentID INT IDENTITY(1,1) PRIMARY KEY,
    MailID INT NULL,
    AttachmentFileName VARCHAR(255) NULL,
    AttachmentFilePath VARCHAR(MAX) NULL
)

MailBody: برای ذخیره محتوای بدنه ایمیل.


CREATE TABLE MailBody
(
    MailBodyID INT IDENTITY(1,1) PRIMARY KEY,
    MailID INT NULL,
    MailText VARCHAR(MAX) NULL
)

MailLog: برای ثبت وقایع مربوط به ارسال و دریافت ایمیل.


CREATE TABLE MailLog
(
    MailLogID INT IDENTITY(1,1) PRIMARY KEY,
    MailID INT NULL,
    LogDateTime DATETIME DEFAULT GETDATE(),
    LogDescription VARCHAR(MAX) NULL
)

MailQueue: صف ایمیل‌های در انتظار ارسال.


CREATE TABLE MailQueue
(
    MailID INT IDENTITY(1,1) PRIMARY KEY,
    MailFrom VARCHAR(255) NULL,
    MailTo VARCHAR(MAX) NULL,
    MailCC VARCHAR(MAX) NULL,
    MailBCC VARCHAR(MAX) NULL,
    MailSubject VARCHAR(255) NULL,
    MailPriority INT NULL DEFAULT 3, -- 1=High, 3=Normal, 5=Low
    MailDateTime DATETIME DEFAULT GETDATE(),
    SendStatus BIT NULL DEFAULT 0, -- 0=Pending, 1=Sent
    Retries INT NULL DEFAULT 0
)

MailSettings: برای ذخیره تنظیمات سرورهای POP3 و SMTP.


CREATE TABLE MailSettings
(
    SettingID INT IDENTITY(1,1) PRIMARY KEY,
    SMTP_SERVER VARCHAR(255) NULL,
    SMTP_PORT INT NULL DEFAULT 25,
    POP3_SERVER VARCHAR(255) NULL,
    POP3_PORT INT NULL DEFAULT 110,
    MAILBOX VARCHAR(255) NULL,
    PASSWORD VARCHAR(255) NULL,
    SMTP_AUTH BIT NULL DEFAULT 0, -- 0=No Auth, 1=Auth
    SMTP_USE_SSL BIT NULL DEFAULT 0,
    POP3_USE_SSL BIT NULL DEFAULT 0
)

Pop3Mail: برای ذخیره ایمیل‌های دریافتی از POP3.


CREATE TABLE Pop3Mail
(
    Pop3MailID INT IDENTITY(1,1) PRIMARY KEY,
    FromAddress VARCHAR(255) NULL,
    ToAddress VARCHAR(MAX) NULL,
    CcAddress VARCHAR(MAX) NULL,
    Subject VARCHAR(MAX) NULL,
    MailBody VARCHAR(MAX) NULL,
    ReceivedDateTime DATETIME DEFAULT GETDATE(),
    IsRead BIT DEFAULT 0,
    UID VARCHAR(255) NULL
)

Pop3UIDList: برای جلوگیری از دریافت مجدد ایمیل‌های تکراری.


CREATE TABLE Pop3UIDList
(
    UIDListID INT IDENTITY(1,1) PRIMARY KEY,
    UID VARCHAR(255) NULL,
    ReceivedDateTime DATETIME DEFAULT GETDATE()
)

پیکربندی راه‌حل

پس از ایجاد جداول، باید تنظیمات سرورهای POP3 و SMTP خود را در جدول `MailSettings` وارد کنید. این شامل آدرس سرور، پورت‌ها، نام کاربری و رمز عبور ایمیل می‌شود.


INSERT INTO MailSettings (SMTP_SERVER, SMTP_PORT, POP3_SERVER, POP3_PORT, MAILBOX, PASSWORD, SMTP_AUTH, SMTP_USE_SSL, POP3_USE_SSL)
VALUES ('smtp.mail.com', 587, 'pop3.mail.com', 995, 'your_email@mail.com', 'your_password', 1, 1, 1)

نکته: مقادیر `smtp.mail.com` و `pop3.mail.com` را با آدرس سرورهای SMTP و POP3 واقعی خود جایگزین کنید. همچنین `your_email@mail.com` و `your_password` را با اطلاعات حساب ایمیل خود به روز کنید. تنظیمات SSL و احراز هویت SMTP را بر اساس نیاز سرویس‌دهنده ایمیل خود فعال یا غیرفعال کنید.

ارسال ایمیل از SQL Server (SMTP)

برای ارسال ایمیل، از پروسیجر گسترش‌یافته `xp_smtp_sendmail` استفاده می‌شود. این پروسیجر به پارامترهایی مانند گیرنده، فرستنده، موضوع و محتوای ایمیل نیاز دارد.


DECLARE @MailID INT
DECLARE @MailTo VARCHAR(MAX) = 'recipient@example.com'
DECLARE @MailFrom VARCHAR(255) = 'your_email@mail.com'
DECLARE @MailSubject VARCHAR(255) = N'ایمیل آزمایشی از SQL Server'
DECLARE @MailText VARCHAR(MAX) = N'این یک ایمیل آزمایشی است که از SQL Server ارسال شده است.'

INSERT INTO MailQueue (MailFrom, MailTo, MailSubject)
VALUES (@MailFrom, @MailTo, @MailSubject)

SET @MailID = SCOPE_IDENTITY()

INSERT INTO MailBody (MailID, MailText)
VALUES (@MailID, @MailText)

-- پروسیجر سفارشی برای ارسال ایمیل از صف
EXEC sp_send_mail @MailID = @MailID

برای سادگی، یک پروسیجر ذخیره شده `sp_send_mail` ایجاد می‌کنیم که از تنظیمات `MailSettings` استفاده کرده و `xp_smtp_sendmail` را فراخوانی می‌کند:


CREATE PROCEDURE sp_send_mail
    @MailID INT
AS
BEGIN
    DECLARE @SMTP_SERVER VARCHAR(255), @SMTP_PORT INT, @MAILBOX VARCHAR(255), @PASSWORD VARCHAR(255), @SMTP_AUTH BIT, @SMTP_USE_SSL BIT
    DECLARE @MailFrom VARCHAR(255), @MailTo VARCHAR(MAX), @MailCC VARCHAR(MAX), @MailBCC VARCHAR(MAX), @MailSubject VARCHAR(255), @MailText VARCHAR(MAX)
    DECLARE @BodyType VARCHAR(50) = 'text/html' -- یا 'text/plain'

    SELECT @SMTP_SERVER = SMTP_SERVER, @SMTP_PORT = SMTP_PORT, @MAILBOX = MAILBOX, @PASSWORD = PASSWORD, @SMTP_AUTH = SMTP_AUTH, @SMTP_USE_SSL = SMTP_USE_SSL
    FROM MailSettings
    WHERE SettingID = 1 -- فرض بر این است که یک ردیف تنظیمات وجود دارد.

    SELECT @MailFrom = mq.MailFrom, @MailTo = mq.MailTo, @MailCC = mq.MailCC, @MailBCC = mq.MailBCC, @MailSubject = mq.MailSubject, @MailText = mb.MailText
    FROM MailQueue mq
    JOIN MailBody mb ON mq.MailID = mb.MailID
    WHERE mq.MailID = @MailID

    IF @MailFrom IS NULL SET @MailFrom = @MAILBOX

    EXEC master.dbo.xp_smtp_sendmail
        @FROM = @MailFrom,
        @TO = @MailTo,
        @CC = @MailCC,
        @BCC = @MailBCC,
        @SUBJECT = @MailSubject,
        @MESSAGE = @MailText,
        @SERVER = @SMTP_SERVER,
        @PORT = @SMTP_PORT,
        @USERNAME = @MAILBOX,
        @PASSWORD = @PASSWORD,
        @USE_SSL = @SMTP_USE_SSL,
        @AUTH = @SMTP_AUTH,
        @BODYTYPE = @BodyType

    IF @@ERROR = 0
    BEGIN
        UPDATE MailQueue SET SendStatus = 1, Retries = Retries + 1 WHERE MailID = @MailID
        INSERT INTO MailLog (MailID, LogDescription) VALUES (@MailID, 'ایمیل با موفقیت ارسال شد.')
    END
    ELSE
    BEGIN
        UPDATE MailQueue SET Retries = Retries + 1 WHERE MailID = @MailID
        INSERT INTO MailLog (MailID, LogDescription) VALUES (@MailID, 'خطا در ارسال ایمیل: ' + CAST(@@ERROR AS VARCHAR(10)))
    END
END

دریافت ایمیل از SQL Server (POP3)

برای دریافت ایمیل، از پروسیجر `xp_pop3_getmail` استفاده می‌کنیم. این پروسیجر به اطلاعات سرور POP3 و احراز هویت نیاز دارد.


-- پروسیجر سفارشی برای دریافت ایمیل
EXEC sp_get_mail

برای ساده‌سازی، یک پروسیجر ذخیره شده `sp_get_mail` ایجاد می‌کنیم که `xp_pop3_getmail` را فراخوانی کرده و ایمیل‌های دریافتی را در جداول مربوطه ذخیره می‌کند. این پروسیجر همچنین از `Pop3UIDList` برای جلوگیری از دریافت مجدد ایمیل‌ها استفاده می‌کند.


CREATE PROCEDURE sp_get_mail
AS
BEGIN
    DECLARE @POP3_SERVER VARCHAR(255), @POP3_PORT INT, @MAILBOX VARCHAR(255), @PASSWORD VARCHAR(255), @POP3_USE_SSL BIT
    DECLARE @ReturnCode INT, @UIDList XML

    SELECT @POP3_SERVER = POP3_SERVER, @POP3_PORT = POP3_PORT, @MAILBOX = MAILBOX, @PASSWORD = PASSWORD, @POP3_USE_SSL = POP3_USE_SSL
    FROM MailSettings
    WHERE SettingID = 1

    -- دریافت UIDهای موجود برای جلوگیری از تکرار
    SELECT @UIDList = (SELECT UID FROM Pop3UIDList FOR XML PATH('UID'), ROOT('UIDList'))

    EXEC @ReturnCode = master.dbo.xp_pop3_getmail
        @SERVER = @POP3_SERVER,
        @PORT = @POP3_PORT,
        @USERNAME = @MAILBOX,
        @PASSWORD = @PASSWORD,
        @USE_SSL = @POP3_USE_SSL,
        @STORE_TABLE_NAME = 'Pop3Mail', -- جدولی که ایمیل‌ها در آن ذخیره می‌شوند
        @STORE_UID_TABLE_NAME = 'Pop3UIDList', -- جدولی که UIDها در آن ذخیره می‌شوند
        @UID_LIST = @UIDList -- لیست UIDهای قبلی برای جلوگیری از دریافت مجدد

    IF @ReturnCode = 0
    BEGIN
        INSERT INTO MailLog (LogDescription) VALUES ('ایمیل‌ها با موفقیت دریافت و در Pop3Mail ذخیره شدند.')
    END
    ELSE
    BEGIN
        INSERT INTO MailLog (LogDescription) VALUES ('خطا در دریافت ایمیل: ' + CAST(@ReturnCode AS VARCHAR(10)))
    END
END

پردازش زمان‌بندی‌شده

برای اطمینان از ارسال و دریافت منظم ایمیل‌ها، می‌توانید SQL Server Agent Job را تنظیم کنید تا پروسیجرهای `sp_send_mail` و `sp_get_mail` را در فواصل زمانی مشخص (مثلاً هر 5 دقیقه) اجرا کند. این کار اتوماسیون کامل مدیریت ایمیل را فراهم می‌کند.


-- مثال ایجاد SQL Server Agent Job برای ارسال ایمیل
EXEC msdb.dbo.sp_add_job
    @job_name = N'ارسال ایمیل‌های در انتظار',
    @enabled = 1,
    @description = N'این Job ایمیل‌های موجود در MailQueue را ارسال می‌کند.'

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'ارسال ایمیل‌های در انتظار',
    @step_name = N'اجرای sp_send_mail برای ایمیل‌های جدید',
    @step_id = 1,
    @cmdexec_success_code = 0,
    @on_success_action = 1,
    @on_fail_action = 2,
    @database_name = N'MailDB',
    @command = N'
    DECLARE @MailID INT
    WHILE EXISTS (SELECT 1 FROM MailQueue WHERE SendStatus = 0)
    BEGIN
        SELECT TOP 1 @MailID = MailID FROM MailQueue WHERE SendStatus = 0 ORDER BY MailDateTime ASC
        EXEC sp_send_mail @MailID = @MailID
    END'

EXEC msdb.dbo.sp_add_schedule
    @job_name = N'ارسال ایمیل‌های در انتظار',
    @name = N'هر 5 دقیقه',
    @freq_type = 4, -- روزانه
    @freq_interval = 1,
    @freq_subday_type = 4, -- دقیقه
    @freq_subday_interval = 5, -- هر 5 دقیقه
    @active_start_date = 20230101,
    @active_end_date = 99991231,
    @active_start_time = 0,
    @active_end_time = 235959

EXEC msdb.dbo.sp_attach_schedule
    @job_name = N'ارسال ایمیل‌های در انتظار',
    @schedule_name = N'هر 5 دقیقه'

EXEC msdb.dbo.sp_add_jobserver
    @job_name = N'ارسال ایمیل‌های در انتظار',
    @server_name = N'(LOCAL)'


-- مثال ایجاد SQL Server Agent Job برای دریافت ایمیل
EXEC msdb.dbo.sp_add_job
    @job_name = N'دریافت ایمیل‌های جدید',
    @enabled = 1,
    @description = N'این Job ایمیل‌های جدید را از سرور POP3 دریافت می‌کند.'

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'دریافت ایمیل‌های جدید',
    @step_name = N'اجرای sp_get_mail',
    @step_id = 1,
    @cmdexec_success_code = 0,
    @on_success_action = 1,
    @on_fail_action = 2,
    @database_name = N'MailDB',
    @command = N'EXEC sp_get_mail'

EXEC msdb.dbo.sp_add_schedule
    @job_name = N'دریافت ایمیل‌های جدید',
    @name = N'هر 5 دقیقه',
    @freq_type = 4,
    @freq_interval = 1,
    @freq_subday_type = 4,
    @freq_subday_interval = 5,
    @active_start_date = 20230101,
    @active_end_date = 99991231,
    @active_start_time = 0,
    @active_end_time = 235959

EXEC msdb.dbo.sp_attach_schedule
    @job_name = N'دریافت ایمیل‌های جدید',
    @schedule_name = N'هر 5 دقیقه'

EXEC msdb.dbo.sp_add_jobserver
    @job_name = N'دریافت ایمیل‌های جدید',
    @server_name = N'(LOCAL)'

گزارش‌گیری ایمیل

جدول `MailLog` به شما امکان می‌دهد تا وضعیت تمامی عملیات ارسال و دریافت ایمیل را رصد کنید. این اطلاعات برای عیب‌یابی و اطمینان از عملکرد صحیح سیستم ایمیل شما بسیار مفید است.


SELECT * FROM MailLog ORDER BY LogDateTime DESC

نتیجه‌گیری

با استفاده از پروسیجرهای گسترش‌یافته `xp_smtp_sendmail` و `xp_pop3_getmail` و یک ساختار پایگاه داده سفارشی، می‌توان یک سیستم قدرتمند برای ارسال و دریافت ایمیل به طور کامل از داخل SQL Server پیاده‌سازی کرد. این راه‌حل بر محدودیت‌های روش‌های داخلی غلبه کرده و انعطاف‌پذیری و کنترل بیشتری بر روی فرآیندهای ایمیل فراهم می‌آورد. این رویکرد برای اتوماسیون وظایف مبتنی بر ایمیل، مانند ارسال گزارش‌ها، اطلاعیه‌ها یا پردازش ایمیل‌های ورودی به عنوان بخشی از گردش کار پایگاه داده، ایده‌آل است.

ارسال و دریافت ایمیل با SQLServer راهنمای کامل POP3 و SMTP