چگونه از طریق SQL Server ایمیل ارسال کنیم؟ [قسمت اول]

چگونه از طریق SQL Server ایمیل ارسال کنیم؟ [قسمت اول]

نوشته شده توسط: مهران رحمتی
۰۱ شهریور ۱۳۹۴
زمان مطالعه: 16 دقیقه
۵
(۱)

مقدمه

در قسمت اول این مقاله پیکربندی و ارسال گزارش و پیامها از طریق سرویس ایمیل در SqlServer را بررسی خواهیم کرد.این روش برای ارسال ایمیل از موتور بانک اطلاعاتی Sql Server به سرویس دهنده های SMTP می باشد. برنامه های کاربردی تحت بانک اطلاعاتی SQLServer میتوانند با ارسال اطلاعات توسط Database Email با کابران ارتباط برقرار نمایند. این موضوع ویژگی هایی همانند مقیاس پذیری (scalability)، امنیت (security)، و اطیمنان پذیری (Reliability) را در خود ارایه کرده است.

Database Email از سرویس SMTP برای ارسال ایمیل استفاده میکند SQLSERVER 2000 هم از سرویس پست الکترونیک استفاده میکرد البته بجای SMTP از MAPI بهره میبرد.

Database Email در SQL نیاز به سرویس سازگاز با MAPI (Microsoft Exchange Server ) و سرویس گیرنده (Microsoft Outlook) دارد. ما میتوانیم از این سرویس برای ارسال متن ، نتایج یه پرس و جو (quiry) وفایل های ضمیمه (file attachment) استفاده کنیم. از این سرویس میتوان برای اطلاع رسانی به مدیران استفاده کرد که بتوانند وقایع و رویدادهایی که ارتباط بهSQLSERVER دارند را بخوانند.

به عنوان مثال می توان اشکالاتی که در انجام فرآیندهایی replication و یا mirroring که بطور خودکار انجام میشود و یا اینکه تاخیر زمانی در اجرای آنها وجود دارد را به مدیران (administrators ) اطلاع رسانی کرد.

نکاتی که باید بخاطر داشته باشیم

۱- پست الکترونیک بانک اطلاعاتی نیاز به سازگاری با MAPI همانند OutlookExpress و یارابط (interface) برنامه نویسی ندارند.
۲- عملکرد بهتر . تاثیر ارسال پست الکترونیک به سرویس دهنده SMTP به SQL Server کاهش پیداکرده است زیرا این وظیفه توسط یک فرآیند (process ) خارجی و مجزا که در واقع فایلی به نام DatabaseMail.exe است انجام می پذیرد.
۳- این سرویس در محیط مبنای کلاستر (cluster based environment.) به خوبی کار میکند.
۴ – ۶۴bit راپشتیبانی میکند.
۵ – اطلاعات مربوط به پیکربندی پست الکترونیک در بانکاطلاعاتی MSDB ذخیره می گردد.
۶- فقط اعضای متعلق به نقشهای (Rols)SysAdmin و DatabaseMailUserRole از بانک اطلاعاتی MSDB میتوانند بطور پیش فرض ایمیل ارسال کنند.
۷ – اجازه ارسال پیامها را در غالب متفاوت همانند HTML و متن که را میدهد.

اجزای اصلی پست الکترونیک پایگاه داده عبارتند از

۱- Sp_Send_dbmail : این یک روال ذخیره شده (stored procedure ) میباشد که SQLSERVER ازآن برای ارسال پست الکترونیک پایگاه داده استفاده میکند. این روال ذخیره شده درپایگاه داده MSDB وجود دارد.
۲MSDB DataBase : شامل همه روال ذخیره شده ،جداول سیستمی ، نقش های (Rols) وابسته بهDatabase Email پایگاه داده می باشد.
۳Service Broker : برای ایجاد ارتباط بین موتور SQL SERVER وموتور پست الکترونیکی پایگاه داده ما نیاز به این سرویس داریم ، در واقع Service Broker پیامها را به موتور Database Email تحویل میدهد.
۴- DatabaseMail.exe :این فایل در پوشه Binn درنمونه ( Instance) نصب شده در مسیر نصب شده SQLServer می باشد . این فایل درواقع همان موتور DatabaseEmail پایگاه داده میباشد.

این فرآیند چگونه کار میکند؟

وقتی به هر دلیل یک خطای زمان اجرا اتفاق می افتد ،مثلاهنگام اجرای فرآیندی که بطور خودکار مانند پشتیبان گیری(Backup) یاتکثیر دادها (Replicate) صورت می پذیرد ، اطلاعات مربوط به خطای رخ داده به موتور پست الکترونیک بانکاطلاعاتی ارجاع داده میشود و سپس موتور پست الکترونیکی (database mail engine ) آنرا به سرویس دهنده SMTP که نام کاربری و کلمه عبور آن در پروفایل ایجاد شده تحویل میدهد و سرانجام سرویسدهنده MSTP انرابه دریافت کنندگان ارسال میکند.

Error –> DB Engine –> DBMail Engine –> SMTP Server –> Recipients

بخاطر دارید که گفتیم یکی از اجزای مهم برای ارسال ایمیل Service Broker است حالا چگونه میتوانیم آنرا فعال کنیم

USE [master]
GO
ALTER DATABASE [MSDB] SET ENABLE_BROKER WITH NO_WAIT
GO
به خاطر داشته باشید که جداول موجود در بانک اطلاعاتی MSDB با پایگاه داده پست الکترونیکی (ِDatabase Mail)ارتباط دارد ، لذا جداول مهم در این زمینه عبارت است از :

۱- Sysmail_Profile :شامل همه اطلاعات مربوط به پروفایلهای ایجاد شده است
۲- Sysmail_account: شامل اطلاعات کلیه سرویس دهنده های SMTP است
۳- Sysmail_server: شامل جزییات سرویس دهنده های SMTP است .
۴- Sysmail_allitems: وضعیت ارسال ایمیل را نشان میدهد . اگرفیلد send_status=1 باشد بمعنای ارسال موفقیت آمیز ایمیل است درغیراینصورت دلیل وجود خطا می باشد
۵- Sysmail_log: کنترل خطاهایی که بوسیله حالتهای پستالکترونیک (DatabaseMail)بوجود آمده اند.
۶- Sysmail_configuration : شامل جزییات پارامترهای سیستم است (که درواقع هنگام تعریف پروفایل در صفحات Wizard تنظیم (SET)میکنید)

مراحل پیکربندی

۱- فعال کردن ایمیل در سطح سرور
sp_configure 'Database Mail XPs',1
reconfigure

۲- فعال کردن Service broker درپایگاه داده MSDB

USE [master]
GO
ALTER DATABASE [MSDB] SET ENABLE_BROKER WITH NO_WAIT
GO

۳- پیکربندی پروفایل ایمیل (پروفایل مجموعه ای از حساب هایی است که توسط کاربران ایجاد شده است)

۴- اضافه کردن حساب SMTP

۵- ایجاد پروفایل خصوصی (Private)با عمومی (Public)

تذکر : پروفایل خصوصی (private)بوسیله اعضای زیر مورد استفاده قرار میگیرد

· sysadmin members and

· databasemailuserrole members of MSDB

۶- تنظیم پارامترها
۷- ارسال ایمیل

مثال: پیکربندی Database Mail

برای انجام این مثال به قسمت Management بروید و روی آیتم DataBase Mail کلیک راست کنید و از منوی ظاهر شده قسمت DataBaseMail Configure را انتخاب کنید
و از پنجره خوش آمد گویی روی دکمه Next کلیک کنید تا به صفحه DatabaseMail Configure Wizard وارد شوید . در قسمت گزینه اول SetupDataBase Mail by Performance the folloming Task را انتخاب کنید ودکمه Next را بزنید.

در این قسمت Profile Name = “sql Profile”قرار دهید و شرحی برای آن وارد نمایید و سپس روی دکمه Add کلیک نمایید تا صفحه زیر نمایان گردد در قسمت Email address آدرس ایمیل خود را وارد کنید . که البته غالبا از ID شرکتی برای این موضوع استفاده میکنند، در قسمت Server name :اسم سرویس دهنده SMTPخود را وارد کنید مثلا برای گوگل (smtp.gmail.com)( اطلاعات این قسمت از اطلاعات سرویس دهنده آن دریافت کنید.)در قسمت port number شماره پورت مورد نظر را وارد کنید بعنوان مثال ۵۸۷ . گزینه SSLرا فعال کنید واگر از ایمیل شخصی خودتان استفاده میکنید گزینه Basic Authentication رافعال کنید و ایمیل و کلمه عبور آنرا خود را وارد کنید، سپس دکمه Ok را بزنید و به صفحه بعد بروید
در زیر گزینه Manage Profile Security مطمئن شوید که گزینه Public تیک خورده باشد و گزینه مقابل آن DefultProfile=Yes را فعال کرده باشید سپس بعد از زدن دکمه Next به صفحه پارامترهای مربوط به پروفایل ایجاد شده خواهید رفت . لازم است در مورد این صفحه عرض کنم که میتواند در این پنچره تنظیمات مربوط به فایلهایی که میخواهید SQL موقع Attach کردن آنها را در لیست سیاه خود قرار دهد را تعیین کنید ، مثلا درگزینهProhibited Attachment FileExtensions بطور پیش فرض فایلهایی با پسوند Exe,dll,vbs,js در لیست سیاه قرار گرفته اند ، حال میتوانید از گزینه مقابل ان استفاده کرده و فایلهایی با پسوند دلخواه را در لیست سیاه به SqlServer معرفی کنید.
سپس دکمه Next و Finish رابزنید تا کار پیکربندی ایمیل سرور فعال شود.

چگونه میتوانیم تنظیمات انجام شده در MailServer را مشاهده کنیم؟

Use msdb
Go
--فهرست پروفایلهای ایجاد شده
SELECT * from sysmail_profile
--فهرست اکانت های ایجاد شده
SELECT * from sysmail_account
-- کنترل کردن اکانت مورد نظر
SELECT * from sysmail_profileaccount where profile_id= ?
--نمایش جزییات ایمیل سرور
SELECT * from sysmail_server

حال برای تست ارسال یک ایمیل گزینه SendTest E-Mailرا از DabaseMail انتخاب کنید

حال دکمه Send Test E-Mail رابزنید و ایمیل ارسال شده را چک کنید. برای اطمینان از اینکه ایمیل ارسال شده یا نه از دستور زیر استفاده کنید و به فیلد Send_Status را ملاحظه کنید

SELECT * FROM sysmail_allitems

اگر Send_Status =Send بود بعمنای ارسال موفقیت آمیز ایمیل است و گزینه failed به معنای عدم موفقیت.

چه رتبه ای می‌دهید؟

میانگین ۵ / ۵. از مجموع ۱

اولین نفر باش

title sign
معرفی نویسنده
title sign
دیدگاه کاربران

    • با سلام و احترام
      ضمن تشکر بابت توضیحات جامع،
      در صورت امکان در مورد اتصال به Exchange Server 2013 نیز یک مثال بزنید
      با تشکر

      • سلام این مورد هیچ تفاوتی با تنظیمات ارائه شده ندارد تنها تفاوت
        ۱- سرور SQL Server باید Mail Server شما را مشاهده کند
        ۲- پورت مربوطه را درست تنظیم کنید (فک کنم ۲۵ یا ۲۵۲۵ باشد)

    • با سلام و احترام
      ضمن تشکر بابت توضیحات جامع،
      در صورت امکان در مورد اتصال به Exchange Server 2013 نیز یک مثال بزنید
      با تشکر

      • سلام این مورد هیچ تفاوتی با تنظیمات ارائه شده ندارد تنها تفاوت
        ۱- سرور SQL Server باید Mail Server شما را مشاهده کند
        ۲- پورت مربوطه را درست تنظیم کنید (فک کنم ۲۵ یا ۲۵۲۵ باشد)

    •    با سلام و عرض ادب

      جناب رحمتی ممنون از مقاله خوبتون . بنده تمام مراحل را انجام دادم . ایمیل هم ارسال می شود ولی وقتی یک operator تعریف می کنم متاسفانه ، ایمیل آدرس کار نمی کند و این دفعه نامه ای ایمیل نمی شود .

    •     مقاله خوبی بود. ولی من معمولا با SSIS ایمیل رو ارسال میکنم و به نظرم کنترل بیشتری میشه داشت و همینطور تسک و کامپوننتهای قبل و بعد از ارسال ایمیل هم قاعدتا راحتتر میشه مدیریت کرد.

    •  سلام
      مقاله بسیار عالی و کاربردی بود با تشکر

    • سلام 

      مقاله عالی بود.

      همانطور که آقای عبدالهی گفتند قبل از اجرای دستورات زیر
      sp_configure ‘Database Mail XPs’,1
      reconfigure
      باید دستور زیر اجرا شود.
      sp_configure ‘show advanced options’, 0;
      GO
      RECONFIGURE;
      GO
      sp_configure ‘Database Mail XPs’, 1;
      GO
      RECONFIGURE
      GO
      •  سلام 

        ار مرحمت شما سپاسگذارم
        چون قبلا تنظیمات  sp_configure ‘show advanced options’,1 در سیستم من ست شده بود این خطا برای من ایجاد نمی شد
        حالا  وقتی ابتدا دستور
        sp_configure ‘show advanced options’,0
        GO
        RECONFIGURE;
        GO
        را اجرا میکنم و بعد دستور
        sp_configure ‘Database Mail XPs’, 1;
        GO
        RECONFIGURE
        GO
        این خطا اتفاق می افتد . بنظر میرسد ابتدا باید (به عدد یک ست شود)
        sp_configure ‘Database Mail XPs’, ۱;
        GO
        RECONFIGURE
        GO
    • سلام
      در ابتدا تشکر می کنم از مقاله بسیار خوب و کاربردیتون جناب رحمتی
      برای من که مبتدی هستم در این زمینه یک مورد به نظرم رسید اگر مورد صلاحدیدتون بود لطفاً توضیحی دراین باره بدین ممنون میشم :
      در قسمت :
       مراحل پیکر بندی – فعال کردن ایمیل در سطح سرور
      sp_configure ‘Database Mail XPs’,1
      reconfigure
      در این قسمت من از این کد استفاده کردم که با این خطا روبرو شدم
      Msg 15123, Level 16, State 1, Procedure sp_configure, Line 66
      The configuration option ‘Database Mail XPs’ does not exist, or it may be an advanced option.
      خوشبختانه خاطرم مونده بود که در یکی از مجموعه های آموزشی آقای طاهری موردی مشابه این توضیح داده شده بود همین طور در این لینک مایکروسافت به این قضیه اشاره شده و کدهای کامل رو به این صورت در نظر گرفتن :
      sp_configure ‘show advanced options’, 0;
      GO
      RECONFIGURE;
      GO
      sp_configure ‘Database Mail XPs’, 1;
      GO
      RECONFIGURE
      GO
      در انتها مجدد تشکر می کنم بابت مقاله مفید و کاربردی که در این زمینه قرار دادین.

      •  سلام دوست خوبم

        بنده مشکلی در این مورد که فرمودید مشاهده نکردم اما پیشنهاد میکنم که املا دستوری عبارت فوق و یا نسخه SQL و سطخ دسترسی کاربری خود اطمینان حاصل فرمایید.
        با تشکر از شما
    • سلام
      در ابتدا تشکر می کنم از مقاله بسیار خوب و کاربردیتون جناب رحمتی
      برای من که مبتدی هستم در این زمینه یک مورد به نظرم رسید اگر مورد صلاحدیدتون بود لطفاً توضیحی دراین باره بدین ممنون میشم :
      در قسمت :
       مراحل پیکر بندی – فعال کردن ایمیل در سطح سرور
      sp_configure ‘Database Mail XPs’,1
      reconfigure
      در این قسمت من از این کد استفاده کردم که با این خطا روبرو شدم
      Msg 15123, Level 16, State 1, Procedure sp_configure, Line 66
      The configuration option ‘Database Mail XPs’ does not exist, or it may be an advanced option.
      خوشبختانه خاطرم مونده بود که در یکی از مجموعه های آموزشی آقای طاهری موردی مشابه این توضیح داده شده بود همین طور در این لینک مایکروسافت به این قضیه اشاره شده و کدهای کامل رو به این صورت در نظر گرفتن :
      sp_configure ‘show advanced options’, 0;
      GO
      RECONFIGURE;
      GO
      sp_configure ‘Database Mail XPs’, 1;
      GO
      RECONFIGURE
      GO
      در انتها مجدد تشکر می کنم بابت مقاله مفید و کاربردی که در این زمینه قرار دادین.

    • سلام
      مقاله بسیار عالی و کاربردی بود
      بخصوص تشریح نحوه فعا سازی  و فرآیند کار سرویس و چگونگی پیکربندی سرویس من خودم بسار مشتاق بودم در مورد   
       بدونم که تا حدودی با تحوه کار کردش و نحوه فعالسازیش آشنا شدم Service Broker

    •  عالی  بود ممنونم ایشلا مقاله های بیشتری رو از شما بخوانیم

  • 1
  • 2