Aklımda Kalası Kelimeler

* давайте работать вместе
* Zarf ve Mazruf, Zerafet(xHoyratlık) ile aynı kökten(za-ra-fe) gelir
* Bedesten
* Suç subuta ermiştir - Suç sabit olmuştur

18 Kasım 2011 Cuma

Dinamik SQL cümleleri çalıştırmak


PROCEDURE kısaltması PROC
TRANSACTION kısaltması TRANS
EXECUTE kısaltması EXEC

Demek ki EXECUTE = EXEC
eğer parametresiz bir SQL cümlesi ise EXEC '....' ya da EXEC @sqlCumlesi
Eğer sql cümleniz: >
'SELECT TOP(@iTopIN) @soyadiOut=soyadi FROM @tabloAdiIN WHERE adi=@kullaniciAdiIN'
ise
@iTopIN, @tabloAdiIN ve @kullaniciAdiIN diye 3 parametre girdi parametresi
@soyadiOUT ise çıktı (OUT|OUTPUT) parametresi.
Böyle bir dinamik SQL ifadesini aşağıdaki örnekteki gibi hazırlayabilirsiniz.

Eğer SQL ifadenizde parametre ifadeleriniz varsa EXEC(@sqlIfadeniz) şeklinde parentezli olarak çalıştırmalısınız.
DECLARE @tabloAdi sysname;
DECLARE @SQL nvarchar(500);
DECLARE @ParmDef nvarchar(500);
DECLARE @count int;

SET @tabloAdi = '[dbo].[Birimler]';
SET @SQL = N'SELECT @cnt=COUNT(1) FROM '+@tabloAdi+';' 
SET @ParmDef = N' @tbl sysname, @cnt int OUTPUT';

EXECUTE sp_executesql @SQL, @ParmDef, @tbl = @tabloAdi, @cnt=@count OUTPUT;
SELECT @count;
Aşağıdaki kaynak kodu LOG kayıtlarımızı taşımak için yazdığımız bir SQL JOB.
DECLARE @kaynak VARCHAR(50)
DECLARE @hedef VARCHAR(50)
DECLARE @aktarilan INT
DECLARE @silinen INT
DECLARE @tblOzet TABLE
(
    id int IDENTITY(1,1),
    kaynak VARCHAR(50),
    hedef VARCHAR(50),
    aktarilan int,
    silinen int
);


DECLARE cr CURSOR FOR SELECT kaynakTabloAdi, hedefTabloAdi FROM medula_V3_test_test_log.dbo.TasinacakTablolar
OPEN cr

 FETCH NEXT FROM cr INTO @kaynak, @hedef

 WHILE @@FETCH_STATUS = 0
 BEGIN
  print @kaynak+' - '+@hedef
  -- 1. Kaynakta şu an kaç aktarılacak satır var (COUNT)
  -- 2. Hedefe TOP(COUNT) kayıdı, kaynaktan SELECT ile INSERT et
  -- 3. Kaynaktaki TOP(COUNT) kayıdı sil
  -- 4. Özet durum mesajı hazırla
    /*
    
KAYNAKHEDEFAKTARILANSİLİNEN
@kaynak@hedef@aktarilan@silinen
*/ -- 5. Özet durumu ileti olarak gönder DECLARE @sql VARCHAR(500) SET @sql ='INSERT INTO '+@hedef +' SELECT * FROM '+@kaynak EXEC(@sql) SET @aktarilan = @@ROWCOUNT SET @sql = 'DELETE TOP('+CAST(@aktarilan AS NVARCHAR(8))+') FROM '+@kaynak EXEC(@sql) SET @silinen = @@ROWCOUNT INSERT INTO @tblOzet VALUES(@kaynak, @hedef, @aktarilan, @silinen); FETCH NEXT FROM cr INTO @kaynak,@hedef END CLOSE cr DEALLOCATE cr SELECT * FROM @tblOzet

Sonuç:

Taşınacak tabloların tutulduğu tablo:

Son Hali

DECLARE @kaynakVeritabani VARCHAR(50)
SET @kaynakVeritabani = 'medula_V3_test';
DECLARE @hedefVeritabani VARCHAR(50)
SET @hedefVeritabani = 'medula_V3_test_test_log';

-- @mailBody : Gönderilecek iletinin gövdesini oluşturacak
DECLARE @mailBody NVARCHAR(MAX)
-- @tr : Gönderilecek iletinin tablosundaki satırı oluşturmak için
DECLARE @tr NVARCHAR(1000)

DECLARE @kaynak VARCHAR(50)
DECLARE @hedef VARCHAR(50)
DECLARE @aktarilan INT
DECLARE @silinen INT
DECLARE @tblOzet TABLE (id int IDENTITY(1,1), kaynak VARCHAR(50), hedef VARCHAR(50), aktarilan int, silinen int);

DECLARE cr CURSOR FOR SELECT kaynakTabloAdi, hedefTabloAdi FROM medula_V3_test_test_log.dbo.TasinacakTablolar
OPEN cr

 FETCH NEXT FROM cr INTO @kaynak, @hedef

 WHILE @@FETCH_STATUS = 0
 BEGIN
  -- 1. Hedefteki kayıtları, kaynaktan SELECT ile hedefe INSERT et
  -- 2. Kaynaktaki TOP(@@ROWCOUNT) kayıdı sil ve işlem sonuçlarını özet tabloya INSERT et
  -- 3. Kaynaktaki Veritabanı sıkıştırılaracak
  -- 4. Özet durum mesajı hazırla
    /*
    
KAYNAKHEDEFAKTARILANSİLİNEN
@kaynak@hedef@aktarilan@silinen
*/ -- 5. Özet durumu ileti olarak gönder -- 1. DECLARE @sql VARCHAR(500) SET @sql ='INSERT INTO '+ @hedef + ' SELECT * FROM ' + @kaynak EXEC(@sql) SET @aktarilan = @@ROWCOUNT -- 2. SET @sql = 'DELETE TOP(' + CAST(@aktarilan AS NVARCHAR(8)) + ') FROM ' + @kaynak EXEC(@sql) SET @silinen = @@ROWCOUNT INSERT INTO @tblOzet VALUES(@kaynak, @hedef, @aktarilan, @silinen); FETCH NEXT FROM cr INTO @kaynak,@hedef END CLOSE cr DEALLOCATE cr -- 3. iş DB Sıkıştırma DBCC SHRINKDATABASE ( @kaynakVeritabani ) -- 4. SET @mailBody =' '; DECLARE crHtml CURSOR FOR SELECT kaynak,hedef,aktarilan,silinen FROM @tblOzet OPEN crHtml FETCH NEXT FROM crHtml INTO @kaynak, @hedef, @aktarilan, @silinen WHILE @@FETCH_STATUS = 0 BEGIN SET @tr = '' SET @mailBody = @mailBody + @tr FETCH NEXT FROM crHtml INTO @kaynak, @hedef, @aktarilan, @silinen END CLOSE crHtml DEALLOCATE crHtml SET @mailBody = @mailBody + '
KAYNAKHEDEFAKTARILANSİLİNEN
'+@kaynak+''+@hedef+''+CAST(@aktarilan AS NVARCHAR(10))+''+ CAST(@silinen AS NVARCHAR(10))+'
'; -- 5. Adım İleti gönderme DECLARE @body VARCHAR(1000) SET @body = '
LOG Kayıtlarının Taşınması ÖZETİ
Çalışma Tarihi: ' + GETDATE() + '
Kaynak Veritabanı: ' + @kaynakVeritabani + '
Aktarılan Veritabanı: ' + @hedefVeritabani + '
' + @mailBody + '
'; DECLARE @args VARCHAR(1000) SET @args = 'Q:\caSendEmailAlert.exe /sbj:["Log Taşıma"] /too:cem.topkaya@fmc-ag.com,duygu.akmaz@fmc-ag.com /bdy:["' + @body + '"]' EXEC xp_cmdshell @args

İleti Gönderen Uygulamaya göre düzenlenmiş

LOG Kayıtlarının Taşınması ÖZETİ
Çalışma Tarihi: Nov 19 2011 12:
Kaynak Veritabanı: medula_V3
Aktarılan Veritabanı: medula_V3_Log
KAYNAKHEDEFAKTARILANSİLİNEN
dbo.LogBirimlerdbo.LogBirimler00

SON HALİ(çift tırnaklı falan)

DECLARE @kaynakVeritabani VARCHAR(50)
SET @kaynakVeritabani = 'medula_V3';
DECLARE @hedefVeritabani VARCHAR(50)
SET @hedefVeritabani = 'medula_V3_Log';

-- @html : Gönderilecek iletinin tüm HTML yapısı
DECLARE @html VARCHAR(4000)
-- @css : Gönderilecek iletinin style tanımları
DECLARE @css VARCHAR(200)
SET @css ='<link rel=''stylesheet'' href=''http://medula.fresenius.com.tr/OrtakDosyalar/FMedulaLogAktarimIleti.css'' style type=''text/css'' />';
-- @head : Gönderilecek iletinin HEAD kısmı
DECLARE @head VARCHAR(300)
SET @head = '<HEAD>' + @css + '</HEAD>'
-- @body : Gönderilecek iletinin BODY kısmı
DECLARE @body VARCHAR(3500)
-- @aktarimOzet : Aktarımın hangi DB den hangi DB ye ne zaman yapıldı özet başlığı
DECLARE @aktarimOzet VARCHAR(3500)
-- @aktarimSonuc : Aktarım bilgisinin(aktarımı yapılmış tablo adlarının ve aktarılan kayıt sayılarının) <TABLE> içinde gösterimi
DECLARE @aktarimSonuc VARCHAR(3500)
-- @tr : Gönderilecek iletinin tablosundaki satırı oluşturmak için
DECLARE @tr VARCHAR(1000)

DECLARE @iletiKime VARCHAR(200)
SET @iletiKime = 'cem.topkaya@fmc-ag.com'

-- KAYITLAR içinde dönmek için kullanılacak ve aktarılmışların özet bilgilerini tutumak için değişkenler
DECLARE @kaynak VARCHAR(50)
DECLARE @hedef VARCHAR(50)
DECLARE @aktarilan INT
DECLARE @silinen INT
DECLARE @tblOzet TABLE (id int IDENTITY(1,1), kaynak VARCHAR(50), hedef VARCHAR(50), aktarilan int, silinen int);


DECLARE cr CURSOR FOR SELECT kaynakTabloAdi, hedefTabloAdi FROM medula_V3_Log.dbo.TasinacakTablolar
OPEN cr

 FETCH NEXT FROM cr INTO @kaynak, @hedef

 WHILE @@FETCH_STATUS = 0
 BEGIN
  -- 1. Hedefteki kayıtları, kaynaktan SELECT ile hedefe INSERT et
  -- 2. Kaynaktaki TOP(@@ROWCOUNT) kayıdı sil ve işlem sonuçlarını özet tabloya INSERT et
  -- 3. Kaynaktaki Veritabanı sıkıştırılaracak
  -- 4. Özet durum mesajı hazırla
    /*
    <TABLE>
     <THEAD><TR>
      <TH>KAYNAK</TH><TH>HEDEF</TH><TH>AKTARILAN</TH><TH>SİLİNEN</TH>
     </TR></THEAD>
     <TBODY>
      <TR><TD>@kaynak</TD><TD>@hedef</TD><TD>@aktarilan</TD><TD>@silinen</TD></TR>
     </TBODY>
    </TABLE> 
    */
  -- 5. Özet durumu ileti olarak gönder

  -- 1.
  DECLARE @sql VARCHAR(500)
  SET @sql ='INSERT INTO '+ @hedefVeritabani +'.'+ @hedef + ' SELECT * FROM ' + @kaynakVeritabani +'.'+ @kaynak
  EXEC(@sql)
  SET @aktarilan = @@ROWCOUNT;

  -- 2.
  SET @sql = 'DELETE TOP(' + CAST(@aktarilan AS NVARCHAR(8)) + ') FROM ' + @kaynakVeritabani +'.'+ @kaynak
  EXEC(@sql)
  SET @silinen = @@ROWCOUNT;

  INSERT INTO @tblOzet VALUES(@kaynak, @hedef, @aktarilan, @silinen);

     FETCH NEXT FROM cr INTO @kaynak,@hedef
 END

CLOSE cr
DEALLOCATE cr


-- 3. iş DB Sıkıştırma
DBCC SHRINKDATABASE ( @kaynakVeritabani )

-- 4.
SET @aktarimSonuc = '<TABLE border=''1''><THEAD><TR><TH class=''metin''>KAYNAK</TH><TH class=''metin''>HEDEF</TH><TH>AKTARILAN</TH><TH>SİLİNEN</TH></TR></THEAD>';

DECLARE crHtml CURSOR FOR SELECT kaynak,hedef,aktarilan,silinen FROM @tblOzet
OPEN crHtml
FETCH NEXT FROM crHtml INTO @kaynak, @hedef, @aktarilan, @silinen

 WHILE @@FETCH_STATUS = 0
 BEGIN
  IF @aktarilan > 0
  BEGIN
   SET @tr = '<TR><TD>'+@kaynak+'</TD><TD>'+ @hedef +'</TD><TD class=''rakam''>'+CAST(@aktarilan AS VARCHAR(10))+'</TD><TD  class=''rakam''>'+ CAST(@silinen AS VARCHAR(10))+'</TD></TR>'
   SET @aktarimSonuc = @aktarimSonuc + @tr
  END
     FETCH NEXT FROM crHtml INTO @kaynak, @hedef, @aktarilan, @silinen
 END

CLOSE crHtml
DEALLOCATE crHtml

SET @aktarimSonuc = @aktarimSonuc + '</TBODY></TABLE>';



-- 5. Adım İleti gönderme
SET @aktarimOzet = '<TABLE><CAPTION>LOG TAŞIMA ÖZETİ</CAPTION><TR><TH class=''metin''>Çalışma Zamanı : </TH><TD>' + CAST(GETDATE() AS VARCHAR(25)) + '</TD></TR><TR><TH class=''metin''>Kaynak VT: </TH><TD>' + @kaynakVeritabani + '</TD></TR><TR><TH class=''metin''>Hedef VT: </TH><TD>' + @hedefVeritabani + '</TD></TR><TR><TD COLSPAN=''2''>' + @aktarimSonuc + '</TD></TR></TABLE>';
SET @body = '<BODY>' + @aktarimOzet + '</BODY>';
SET @html = '<HTML>' + @head + @body + '</HTML>';
--print @html

DECLARE @args VARCHAR(4000)
SET @args = 'Q:\SendMail\caSendEmailAlert.exe /sbj:["Log Taşıma"] /html /to:'+ @iletiKime +' /body:["' + @html + '"]'
--print @args
EXEC xp_cmdshell @args