SQL Server ASP.NET C# Kitabı

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("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'

exec @OLESonuc = sp_OASetProperty @Obj,
  'configuration.fields("http://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 ='HTML formatında mail.',
	@SmtpSunucu ='192.1.1.120',
	@MesajTip ='HTMLBody'

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 http://www.sqldev.net/xp/xpsmtp.htm sitesinden indirilebilecek olan XPSMTP.DLL – SQL Server SMTP Mail XP bileÅŸenini tercih ediyorum. Sqldev sitesinden indirilecek olan XPSMTP80.DLL
dosyası sunucuya register edilir. Register işleminden sonra ilgili database altında xp_smtp_sendmail 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 JMAIL componentidir. Bu componentin sınıf yapısı http://dimac.net/ adresinden öğrenbilebilir. JMAIL’in kullanımı da örneklendirelim.

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 <> ''
	BEGIN
		EXEC @Sonuc = sp_OAMethod @Obj, 'AddAttachment', @Sonuc1 out, @Attachment, TRUE
	END

EXEC @Sonuc = sp_OASetProperty @Obj, 'Subject', @Baslik

IF @MesajDosya <> ''
	BEGIN
		EXEC @Sonuc = sp_OAMethod @Obj, 'AppENDBodyFromFile', NULL, @MesajDosya
	END
ELSE
	BEGIN
		EXEC @Sonuc = sp_OASetProperty @Obj, 'Body', @Mesaj
	END

EXEC @Sonuc = sp_OASetProperty @Obj, 'ContentType', 'text/html'

EXEC @Sonuc = sp_OASetProperty @Obj, 'Charset', 'ISO-8859-9'

EXEC @Sonuc = sp_OAMethod @obj, 'SEND', @Sonuc2 out, @SmtpSunucu

RETURN 0

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.

SQL Server 2005 Database Mail

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.

“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.

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.

T-SQL içerisinde Database Mail ile ilgili kullanılabilecek başlıca yordamlar şunlardır;
sp_send_dbmail
sysmail_add_account_sp
sysmail_add_profile_sp
sysmail_delete_account_sp
sysmail_delete_profile_sp
sysmail_start_sp
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.

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.