SQL Server üzerinde otomatik sorgular hazırlayıp sorgu sonuçlarını raporla ilgili kişilere mail olarak atmak veya ftp aracılığıyla belirlenmiş bir alana aktarmak özellikle kurumsal uygulamalarda ihtiyaç duyulan bir durumdur. Bu yazıda SQL Server üzerinde mail ve ftp işlemi nasıl yapılacağını örneklendireceğiz.
FTP ile dosya alma veya gönderme
Ne yazık ki T-SQL aracılığıyla doğrudan ftp işlemini yönetilememektedir. Bu işlemi yapabilmek için T-SQL’in Ms-Dos Komut istemine komut gönderen harici master..xp_cmdshell yordamı kullanılır. Bu yordam iki parametre alır, ilk parametre işletim sistemi tarafından yürütülecek komutu, ikinci parametre ise yürütme sonucunda istemciye herhangi bir dönüş mesajının döndürülüp döndürülmeyeceğini belirtir.
xp_cmdshell {‘command_string’} [, no_output]
FTP işlemi nihayetine System32 klasörünün altında bulunan FTP.EXE aracılığıyla yapıldığı için bu xp_cmdshell yordamına ftp.exe komutunu parametre olarak göndermemiz yeterli olacaktır. Peki FTP için gerekli güvenlik ve dosya bilgileri nasıl gönderilecek. Bunun en güzel çözümü ftp komutlarının harici bir dosyaya yazılması ve bu dosyasının FTP.EXE programına “-s” parametresiyle gönderilmesidir. C:\FtpIslem.txt dosyası oluşturup aşağıdaki gibi sunucu bilgilerini ve sunucu üzerinde çalıştırılacak (put, push) komutları yazıyoruz.
open ftp.ahmetkaymaz.com -> Sunucu Adı
admin -> Kullanıcı Adı
123 -> Şifre
cd /Blog/Upload/Image/ -> Logon olduktan sonra konumlanacak klasör
put C:\Resim1.jpg -> İstemcideki dosyayı sunucuya gönder
quit ->Çıkış komutu
Şimdi Query Analyzer tarafına geçip aşağıdaki gibi T-SQL komutunu çalıştıralım.

EXEC master..xp_cmdshell "ftp -s:C:\FtpIslem.txt"

İhtiyaç duyulduğu durumda bu yordamın sonucu bir tabloya da aktarılabilir. Bunun için aşağıdaki gibi procedure çalıştırılır ve sonuçlar geçici veya kalıcı bir tabloya aktarılır.

CREATE TABLE Sonuc (SatirId int identity(1,1), Aciklama varchar(100))
INSERT Sonuc
EXEC master..xp_cmdshell "FTP -s:C:\FtpIslem.txt"

Eğer FTP işlemini DTS içerisinde yapmak istersek DTS, download işlemi için hazır bir nesne sunar.

FTP Task nesnesini kullanarak güvenlik bilgilerini ve karşıdan hangi dosyanın indirileceği seçilir.

DTS içerisinde FTP tabanlı upload işlemi için yine xp_cmdshell yordamı kullanılır. DTS tasarım ekranındaki “Execute Process Task” aracı kullanılarak FTP.EXE’nin çalıştırılması sağlanır.

SQL Server 2005 ile birlikte DTS’lerin yerini almış olan SQL Server Integration Services (SSIS) aracılığıyla FTP işlemini gerçekleştirmek için daha da kolaylaştırıldı.

“FTP Task” aracı hem dosya gönderme hem de alma işlemini sağlar. Öncelikle bu işlem için bir “FTP Connection” nesnesi tanımlanır. Bu nesnenin özelliklerine ftp güvenlik bilgileri girilir. Ardından aynı şekilde “FTP Task Editor” aracında ne tür bir işlemin yapılacağı girilir.

Uzaktaki dosyaların yereldeki hangi klasöre yükleneceği ve yereldeki hangi dosyaların sunucuya gönderileceği “File Connection Manager Editor” aracılığıyla tanımlanır.

SQL Server üzerinde mail işlemi
SQL Server üzerinde mail göndermek veya okumak için kurulumla birlikte standart olarak gelen SQL Mail aracı veya third party bir araç kullanılabilir. SQL Mail aracı, mail almak ve göndermek için aşağıdaki yordamlar tarafından kullanılan bir T-SQL arabirimi sunar.
xp_startmail
xp_sendmail
xp_findnextmsg
xp_readmail
xp_deletemail
sp_processmail
xp_stopmail
Bu yordamlar harici yordamlar olup sqlmap.dll bileşeni içerisinde bulunur. SQL Mail, MAPI (Messaging Application Programming Interface) tabanlı bir uygulama olup bulunduğu sunucunun MAPI uyumlu bir istemci olarak yapılandırılmasını ister.
SQL Mail servisini başlatmak için sunucu üzerinde bu işlemler için kullanılanacak e-mail adresiyle ilişkili bir mail profilinin oluşturulması gerekir. Sunucu üzerindeki profil oluşturmak veya var olan profilleri düzeltmek için Denetim Masası’ndaki (Control Panel) Mail ikonu kullanılır. Bu ikon Microsoft Outlook ile birlikte oluşturulur. Ardından Enterprise Manager içinde sunucunun altındaki Support Services bölümünden SQL Mail çift tıklanarak varolan profillerden biri seçilir.

Aynı şekilde SQL Agent aracının Properties bölümünden agent’in ilişkili olacağı profil de seçilir. Böylece SQL Server üzerinde otomatik olarak işlenen Job’ların sonucuna dair ilgili profil üzerinde ilgili kişilere mail atılabilir. SQL Mail, SQL Agent servisi başladığında otomatik olarak başlatılır. Veya SQL Mail oturumunu manual başlatmak için xp_startmail yordamı kullanılır. Burada dikkat edilmesi gereken konu SQL Mail içerisinde mevcut profillerin doğru görüntülenmesi için SQL Server ve SQL Agent servisinin bir domain veya local account ile başlatılması gerekir. Mevcut bir profili test etmek için SQL Mail’in Properties menüsü kullanılabileceği gibi master.dbo.xp_test_mapi_profile yordamı da kullanılabilir. Test işlemleri başarılı olduktan sonra xp_sendmail yordamını kullanarak bir mailing denemesi yapabiliriz.

EXEC xp_sendmail @recipients='ahmet.kaymaz@ahmetkaymaz.com;blog@ahmetkaymaz.com',
@subject='ALISVERIS veritabanı yedeği başarıyla alındı.'

xp_sendmail yordamı birçok parametreye sahiptir. SQL Mail’i kullandığımız diğer alan ise SQL Server üzerinde tanımlı operatörlerdir. SQL Agent’in çalıştırdığı Job’ların sonucunu operatörleri bildirmek için SQL Mail alt yapısı kullanılır. SQL Server Agent bölümünün altındaki Operators alanında bir operatör tanımlayalım. Operatör için e-mail adresi, cep telefonu gibi bilgiler girilir. Aşağıdaki şekilde

Outlook Express mail profili oluşturmayı desteklemediği için SQL Mail için kullanılamaz. SQL Mail 2000 için en az Microsoft Outlook 98 veya 2000 sürümlerinin istemci olarak kullanılıyor olması lazım. Bununla birlikte SQL Mail, Lotus Notes veya Novell Groupwise gibi third-party sunucularla birlikte de kullanılabilir.
xp_sendmail yordamının SQL Server 2005’ten sonraki sürümlerde kaldırılması muhtemel. Bu yüzden SQL Server 2005 ile birlikte gelmiş olan Database Mail servisinin kullanılması tavsiye edilir.
Görüldüğü gibi SQL Mail bileşeni biraz uğraştırıcı ve yönetimi zor bir hizmet sunmaktadır. Ayrıca SQL Mail ile halıhazırda HTML formatında mail de atılamamaktadır. Başka bir alternatif ise SQL Mail kullanmadan yine Microsoft ürünü olan CDO for NT Server (CDONTS) veya CDO for Windows 2000 (CDOSYS) bileşenlerini kullanmaktır. Bu bileşenler sp_OA (SQL Server OLE Automation) stored procedure’leri aracılığıyla T-SQL içerisinde kullanılabilir. Bu bileşenler özellikler web tabanlı uygulamalarda mail atmak için tercih edilen basit SMTP bileşenleridir. Microsoft Internet Information Server (IIS) 4.0 ve üstü versiyonlar varsayılan olarak CDONTS bileşenini install eder. Aşağıdaki T-SQL yordamda CDONTS nesnesi yaratılmış ve Send() yordamına parametreler set edilmiştir.

CREATE PROCEDURE xp_CDOMailGonder(
	@Kimden varchar(100),
	@Kime varchar(255),
	@Baslik varchar(255),
	@Mesaj varchar(500)
) AS

DECLARE @Obj int, @OLESonuc int

--CDONTS.NewMail nesnesini oluşturalım
EXECUTE @OLESonuc = sp_OACreate 'CDONTS.NewMail', @Obj OUT

--Nesnenin Send yordamını çağıralım
execute @OLESonuc = sp_OAMethod @Obj, 'Send', Null, @Kimden, @Kime, @Baslik, @Mesaj, 0

--Destroy CDO nesnesini yok edelim
EXECUTE @OLESonuc = sp_OADestroy @Obj

return @OLESonuc

Bu yordamı çağıralım.

xp_CDOMailGonder 'ahmet.kaymaz@ahmetkaymaz.com',
'mehmet@hotmail.com','Başlık','Açıklama'

SQL Mail yerine CDONTS bileşenini kullandığımızda Microsoft Outlook gibi mail istemciye ve Microsoft Exchange sunucusuna ihtiyacımız olmayacaktır. Fakat SQL Mail gibi herhangi bir profilin bağlı olduğu mail hesabına ait mailler okunamaz. SQL Mail’de mailleri okumak için xp_readmail yordamı kullanılır. Ayrıca CDONTS.NewMail yalnızca local smtp sunucuları destekler uzataki bir SMTP sunucusunu relay olarak kullanamayız. Son olarak Windows Server 2003 ve üstü işlem sistemlerinde CDONTS bileşeninin desteklenmeyeceğini göz önünde bulundurmalıyız.

CREATE PROCEDURE xp_CDOSYSMailGonder
	@Kimden  varchar(500) ,
	@Kime varchar(500) ,
	@Baslik varchar(500),
	@Mesaj varchar(4000) ,
	@SmtpSunucu varchar(25),
	@MesajTip varchar(10)
as
declare @Obj int
declare @OLESonuc int
declare @source varchar(255)
declare @description varchar(500)
declare @output varchar(1000)

exec @OLESonuc = sp_OACreate 'CDO.Message', @Obj out
exec @OLESonuc = sp_OASetProperty @Obj,
'configuration.fields("https://schemas.microsoft.com/cdo/configuration/sendusing").value','2'

exec @OLESonuc = sp_OASetProperty @Obj,
  'configuration.fields("https://schemas.microsoft.com/cdo/configuration/smtpserver").value',
  @SmtpSunucu 

exec @OLESonuc = sp_OAMethod @Obj, 'configuration.fields.update', null
exec @OLESonuc = sp_OASetProperty @Obj, 'to', @Kime
exec @OLESonuc = sp_OASetProperty @Obj, 'from', @Kimden
exec @OLESonuc = sp_OASetProperty @Obj, 'subject', @Baslik

-- Html e-mail için 'htmlbody' düz metin için 'textbody' özelliği kullanılır

exec @OLESonuc = sp_OASetProperty @Obj, @MesajTip, @Mesaj
exec @OLESonuc = sp_OAMethod @Obj, 'send', null

exec @OLESonuc = sp_oadestroy @Obj

Bu stored procedure’i çağıralım.

exec xp_CDOSYSMailGonder
	@Kimden='ahmet.kaymaz@ahmetkaymaz.com',
	@Kime ='sqlblog@ahmetkaymaz.com',
	@Baslik ='Yeni Mail',
	@Mesaj ='
Son iki yöntemin özelliği Microsoft'a ait SMTP bileşenlerini kullanıyor olmamızdır. Bilindiği gibi dışarıdan herhangi bir bileşeni T-SQL içerisinde kullanmak için sp_OAXXX yordamları kullanılır. Bu yordamları kullanarak nesne oluşturulur ve nesnelerin yordam ve özellikleri dışarıdan çağrılabilir. Dolayısıyla yine bu yordamları kullanarak sadece CDONTS veya CDOSYS bileşenlerini kullanmak zorunda değiliz herhangi başka bir SMTP componentini de kullanabiliriz. Bu noktada kişisel olarak uygulamalarda bu iki nesneden kaçınarak <a href="https://www.sqldev.net/xp/xpsmtp.htm">https://www.sqldev.net/xp/xpsmtp.htm</a> sitesinden indirilebilecek olan XPSMTP.DLL - SQL Server SMTP Mail XP bileşenini tercih ediyorum. Sqldev sitesinden indirilecek olan <b>XPSMTP80.DLL</b><br />
dosyası sunucuya register edilir. Register işleminden sonra ilgili database altında <b>xp_smtp_sendmail</b> yordamı oluşturulur. Sitede anlatılan parametreleri kullanarak kolayca mailing işlemi yapılabilir.
SQL Server üzerinde mail atmak için kullanılabilecek diğer bileşen de bir çok yazılımcının özellikle web uygulamalarında tercih ettiği <a href="https://dimac.net/">JMAIL</a> componentidir. Bu componentin sınıf yapısı https://dimac.net/ adresinden öğrenbilebilir. JMAIL'in kullanımı da örneklendirelim.
[sql]CREATE PROCEDURE xp_JMailMailGonder
	@Kimden varchar(255),
	@Kime varchar(255),
	@Attachment varchar(255) = '', -- Attach edilecek dosyanın konumu
	@Baslik varchar(255),
	@Mesaj varchar(1000),
	@MesajDosya varchar(1000) = '', -- Mailin içeriğinin bulunduğu dosya
	@SmtpSunucu varchar(100)
as

DECLARE @Obj int
DECLARE @Sonuc int
DECLARE @Sonuc1 int
DECLARE @Sonuc2 varchar(255)

EXEC @Sonuc = sp_OACreate 'jmail.Message', @Obj out

EXEC @Sonuc = sp_OAMethod @Obj, 'AddHeader', NULL, 'App-Sender',@Kimden

EXEC @Sonuc = sp_OASetProperty @Obj, 'From', @Kimden

EXEC @Sonuc = sp_OAMethod @Obj, 'AddRecipient', NULL, @Kime,''
--Son parametre gönderilen kişinin adını belirtir

IF @Attachment 
Bu yordam her EXEC satırından @Sonuc değişkeninin "IF @Sonuc <> 0 RETURN 1¨ şeklinde bir kontrol eklenerek o satırın hata döndürmediği kontrol edilebilir. Sonuçta eğer procedure 1 döndürürse hata oluştuğu 0 döndürürse işlemin başarı olduğu anlamına gelir. Yordamı aşağıdaki gibi kullanabiliriz.
<u><b>SQL Server 2005 Database Mail</b></u>
Yazının girişinde SQL Mail özelliğinin SQL Server 2005'ten sonraki sürümlerde kaldırılacağını bunun yerine Database Mail aracının geliştirildiğini söylemiştik. Database Mail, SQL Mail gibi herhangi bir Outlook veya Exchange Server'e ihtiyaç duymadan sunucu üzerinden mail atmamıza sağlar. Aynı zamanda birden fazla SMTP sunucuyu da parametre olarak kabul eder. Management Studio içerisinde bağlı olunan sunucuda Management bölümünün altında Database Mail aracını sağ tıklayıp yapılandırmaya başlayabiliriz.
<img src="https://www.ahmetkaymaz.com/wp-content/uploads/SQL_Server_Mail_4.jpg"/>
"Database Mail Configuration Wizard" aracının ilk ekranında profil yönetimi sunulur. Bu ekranda yeni bir profil oluşturulabilir veya var olan profiller düzenlenebilir. Profil altında bir veya daha fazla mail hesabı tanımlaması yapılır.
<img src="https://www.ahmetkaymaz.com/wp-content/uploads/SQL_Server_Mail_5.jpg"/>
Yapılan ayarların doğru çalışıp çalışmadığını da Database Mail aracını sağ tıklayarak test edebiliriz. SQL Agent servisini oluşturulmuş olan profillerle ilişkilendirebiliriz. Böylece Job'ların başarılı veya başarısız olması durumunda profildeki kişi veya kişiler haberdar edilir. SQL Agent'in bilgilendirme ayarları için servisi sağlayıp Properties penceresine girelim.
<img src="https://www.ahmetkaymaz.com/wp-content/uploads/SQL_Server_Mail_6.jpg"/>
T-SQL içerisinde Database Mail ile ilgili kullanılabilecek başlıca yordamlar şunlardır;<br />
sp_send_dbmail<br />
sysmail_add_account_sp<br />
sysmail_add_profile_sp<br />
sysmail_delete_account_sp<br />
sysmail_delete_profile_sp<br />
sysmail_start_sp<br />
sysmail_stop_sp
Bu yordamlar msdb veritabanının altında bulunmaktadır. Örneğin aşağıdaki script Ahmet Kaymaz profilini kullanarak listedeki kişilere mail atar.
[sql]EXEC msdb.dbo.sp_send_dbmail
	@recipients=N'ahmet@ahmetkaymaz.com',
	@subject = 'Mail başlığı',
	@body= 'Mailin içeriği',
	@profile_name = 'Ahmet Kaymaz'

Gönderilmiş olan mailler msdb.dbo.sysmail_allitems tablosunda tutular.
SQL Server 2005’te SQL Mail ve Database Mail özellikleri varsayılan olarak kapalı durumdadır. Bunları “SQL Server Surface Area Configuration” aracını kullanarak enable edebiliriz.