SQL Server ve Linked Server

Sizin geliştirme yaptığınız makinenizde SQL Sunucu kurulmuş olsun.
Sizin ulaşabildiğiniz 2 farklı SQL sunucu daha olsun.
Bu harici 2 sunucudaki verileri kıyaslamak istiyor, yahut birindeki bilgileri diğerinde güncellemek istiyor olun.
Kendi sunucunuza SQL Management Studio ile bağlanın. Server Objects altındaki Linked Server ikonuna sağ tuş ve New Linked Server diyerek birinci sunucunuzu tanımlayın:

OPENQUERY ile sorgu yazabileceğim gibi normal bir nesneye erişir gibi de sorgu yazabilirim:

Fiddler ile SQL Server'dan yapılan Web Servis Çağrılarını İzlemek

Fiddler and Microsoft Sql Server

SQL Prosedürmüzde sp_OACreate ile Web Servisi çağrısı yapacağımız bir OLE nesnesi örneğini yaratıp üstünden bir HTTP paketi gönderebiliriz ama Fiddler bunu göstermeyecek. Haydi başlayalım...
Temel itibarıyla aşağıdaki basit kod ile herhangi bir web sayfasını mssql içinden çağırabilirsiniz:

DECLARE @Request INT,
  @hr INT,
  @activeX VARCHAR(50) = 'Msxml2.ServerXMLHTTP.6.0';

 SET @activeX = '{88d96a0b-f192-11d4-a65f-0040963251e5}';
 EXEC @hr = sp_OACreate @activeX, @Request OUT;
 SELECT @hr, @Request
 
 EXEC sp_OAMethod @Request, 'open', NULL, 'GET','https://www.random.org/cgi-bin/randbyte?nbytes=1307&format=h', 'false'
 EXEC sp_OAMethod @Request, 'send', null, ''
  
 DECLARE @ResponseText Varchar(4000)
 EXEC sp_OAMethod @Request, 'responseText', @ResponseText OUTPUT
 SELECT @ResponseText
 Exec sp_OADestroy @Request
 
 IF @hr <> 0
 BEGIN
       DECLARE @src varchar(255), @desc varchar(255)
    EXEC sp_OAGetErrorInfo @Request, @src OUT, @desc OUT 
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
 END
 ELSE
    Exec sp_OADestroy @Request

Ekşi Sözlük'te Ole tanımları:

Biraz anlatayım ne nedir diye. sp_OACreate ile MSSQL içinde bir OLE nesnesinin örneğini yaratır.

ole 2.0 kendi icinde (bkz: com) (bkz: activex) gibi teknolojileri icherir..

nesne baglama ve nesne gomme(yerleshtirme). bir yazilim tarafindan olushturulan bir nesnenin ozelliklerini kaybetmeden bashka bir yazilim tarafindan kullanilabilmesini saglar. bu tur nesneler uzerinde deishiklik yapmak istenirse nesne uzerinden orjinal yazilima baglanmak olanaklidir.

object linking and embedding kisaltmasidir turkcesi ise nesne baglama ve gömme olarak cevrile bilir microsoft office uygulamaları arasında verialı$veri$ini mümkün kılan teklojinin adıdır.ole'nin 2 farkli boyutta islem gormektedir.bunlar baglama ve gömme ne demek simdi bu derseniz sole ki excelde bi islem yaptınız worldbelgesine transferini yapmayı dusunuyorsunuz iste bu hadise bunu mumkun kılmaktadır.

Demek ki bizim bir nesnemiz var ve ondan bir örnek yaratıp, örneğimiz üzerinden işlemler yapmak istiyoruz.

Peki buradaki OLE nesnemiz olan Msxml2.ServerXMLHTTP.6.0 kimdir?

Önce MSXML'den başlayalım. Microsoft XML Core Services (MSXML) W3C uyumlu XML API leri ile yüksek performanslı XML temelli uygulamalar geliştirmeye yarar. Bu kısım biraz genişçe devam edebilir ama konumuzdan sapmayacağım. Neydi konumuz Fiddler ile SQL Server üstünden yaptığımız Web Servisi çağrılarını görüntülemek.
Sırada bu örneği yaratırken kullandığımız INT tipinde @hr ve @Request değişkenleri. @hr sayesinde bu örnekleme işimiz eğer 0 dan başka bir sonuç gelmişse HATA olarak değerlendirecek ve hatayı görüntülemek için IF @hr <> 0 satırını kullanacağız. Burada detaylandırılacak bir şey varsa ben bilmiyorum. Gelin ekran çıktılarına bakalım:

Peki BAŞARISIZ bir örneklendirmeye bakalım:

Açıktırki örneklendiremememiz makinede olmayan bir OLE nesnesini kullanmak arzumuzdan.
Msxml2.ServerXMLHTTP.6.0 OLE nesnesi sistemimizde kayıtlıyken

Msxml2.ServerXMLHTTP.5.0 Registry'de kayıtlı değil.

Sorgulamak için registry sorgusu da deneyebilirsiniz:

C:\>reg query hkcr /f xmlhttp

HKEY_CLASSES_ROOT\Microsoft.XMLHTTP
HKEY_CLASSES_ROOT\Microsoft.XMLHTTP.1.0
HKEY_CLASSES_ROOT\Msxml2.ServerXMLHTTP
HKEY_CLASSES_ROOT\Msxml2.ServerXMLHTTP.3.0
HKEY_CLASSES_ROOT\Msxml2.ServerXMLHTTP.4.0
HKEY_CLASSES_ROOT\Msxml2.ServerXMLHTTP.6.0
HKEY_CLASSES_ROOT\Msxml2.XMLHTTP
HKEY_CLASSES_ROOT\Msxml2.XMLHTTP.3.0
HKEY_CLASSES_ROOT\Msxml2.XMLHTTP.4.0
HKEY_CLASSES_ROOT\Msxml2.XMLHTTP.6.0
End of search: 10 match(es) found.

C:\>

Ayrıca OLE nesnelerimizi ProgId ya da ClsId ile çağırabiliriz

Peki CLS Id nedir?

Ya ProgId?

OLE nesnelerimizi eğer CLS ID leri ile yaratmak istersek önce CLS ID lerini nerede bulabileceğimize bakalım:

Ve şimdide CLS ID ile SQL içinde örnekleme işine bakalım:

Devamını yazacağım ama önce Verdi nin konserine gitmeliyim. Yarın devam ederim ;)

---

Bir Verdi Konseri harikaydı...

Fiddler SQL'i nasıl dinler

Normalde dinlemesini bekleriz ama Fiddler'e düşmez SQL web servis çağrılarınız. SQL Server sizin login olduğunuz oturum üstünden çalışmadığı için Fiddler görüntüleyemeyecektir.
Aşağıda nasıl yapacağımızı görelim:

Artık fiddler sql web service çağrılarını görebileceksiniz.

Neden tüm web servis çağrılarım Fiddler'e düşmüyor

Önce düşen web servis sonuçları var ve düşmeyen var. O halde sorunu tespit için fiddlere baktım ve http talebinin sonuçlarının boyutundan şüphelendim. Bunu test etmek için dönen cevabın boyutunu ayarlayabileceğim bir site buldum. Url adresinden dönecek sonucun boyutunu değiştirebilirsiniz https://www.random.org/cgi-bin/randbyte?nbytes=1324&format=h ve fiddler üstünde görebiliyorsunuz.

SQL kodumuzla çağrı yapalım ve sonucu SQL tarafından alınamayan durumu inceleyelim:

DECLARE @Request INT,
  @hr INT,
  @activeX VARCHAR(50) = 'Msxml2.ServerXMLHTTP.6.0';
  SET @activeX = '{88d96a0b-f192-11d4-a65f-0040963251e5}';

EXEC @hr = sp_OACreate @activeX, @Request OUT;
EXEC sp_OAMethod @Request, 'open', NULL, 'GET','https://www.random.org/cgi-bin/randbyte?nbytes=1307&format=h', 'false'
EXEC sp_OAMethod @Request, 'send', null, ''

DECLARE @ResponseText Varchar(4000)
EXEC sp_OAMethod @Request, 'responseText', @ResponseText OUTPUT
SELECT @ResponseText as Response
Exec sp_OADestroy @Request
 
IF @hr <> 0
BEGIN
   DECLARE @src varchar(255), @desc varchar(255)
   EXEC sp_OAGetErrorInfo @Request, @src OUT, @desc OUT 
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
   RETURN
END
ELSE
 Exec sp_OADestroy @Request

Bu kezde başarılı bir web sayfası çağrısını SQL'den yapalım:

Peki Sorun OLE Nesnesinde mi?

SQL'de göremiyoruz ama arada OLE nesnemiz var acaba sorun onda mı? OLE nesnemizin çektiği sonucu bir dosyaya yazalım ve görelim gelen cevabı kesiyor ya da kırpıyormu.
Test için yine fiddler ve OLE nesnemizden örneğimizi yazıp konsoldan çalıştırmak için bir script dosyası oluşturalım.

Dim oXMLHTTP
Dim oStream

Set oXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")

Dim byteSize 
byteSize="1306"

oXMLHTTP.Open "GET", "https://www.random.org/cgi-bin/randbyte?nbytes="+byteSize+"&format=h", False
oXMLHTTP.Send

If oXMLHTTP.Status = 200 Then
 Wscript.Echo oXMLHTTP.responseBody
 Set oStream = CreateObject("ADODB.Stream")
 oStream.Open
 oStream.Type = 1
 oStream.Write oXMLHTTP.responseBody
 oStream.SaveToFile "c:\temp\sonuc"+byteSize+".txt"
 oStream.Close
End If

Yukarıdaki kodumuzun çalışması için MSXML nin internet explorer->internet options -> Security Settings -> Custom Level -> Access data sources accross domains -> Enable ayarının yapılması gerekecek:

4000 altındaki cevapları SQL içinde görebildiğimiz için öncelikle bunu dosyaya yazdırabiliyor muyuz diye bakalım:

SQL'e yazdıramadığımız sonucu bakalım dosyaya yazabilecek miyiz ve acaba OLE nesnemiz mi SQL'e aktarmıyor diye kontrol edelim:

Geldiğimiz noktada sorun sp_OACreate 'in NVarchar(4000) limitine takılmamızmış :) Öğrendiğim adres ve çözmek isterseniz çözümleri için buraya veya buraya tıklayabilirsiniz.

Bu noktadan sonra ben .Net assemblysi yazarak ilgili fonksiyonlarını çağırmayı tercih edeceğim :)

Azim ve bilgi dolu okumalar olsun....

SQL Server üstünden Web Servisine Çağrı Yapmak

USE [FEInvoice]
GO
/****** Object:  StoredProcedure [dbo].[WS_QueryOutboxInvoiceStatus]    Script Date: 04/03/2015 19:45:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[WS_QueryOutboxInvoiceStatus]
 @faturaUUID varchar(40),
 @username varchar(40)= NULL,
 @password varchar(40)= NULL,
 @invoiceStatus as Varchar(20) OUTPUT,
 @ResponseText as Varchar(8000) OUTPUT
AS
BEGIN
 SET NOCOUNT ON;

 IF(@username IS NULL OR @password IS NULL)
 BEGIN
  DECLARE @vkn VARCHAR(20),
    @gibPk VARCHAR(100);
    
  SELECT @username=UyumSoftKurumKullanicilari.kullaniciAdi, 
         @password=UyumSoftKurumKullanicilari.sifre,
         @vkn=Kurumlar.vergiNo,
         @gibPk=Kurumlar.gibPostaKutusu
    FROM UyumSoftKurumKullanicilari 
 INNER JOIN Kurumlar ON UyumSoftKurumKullanicilari.refKurum_id = Kurumlar.id 
 INNER JOIN FaturaNoOtomatik ON Kurumlar.vergiNo = FaturaNoOtomatik.vkn AND Kurumlar.gibPostaKutusu = FaturaNoOtomatik.gibPk
   WHERE (FaturaNoOtomatik.faturaUUID LIKE @faturaUUID)
  
  IF LEN(@username)=0 RAISERROR('Vergi numarası "%s", GİB Posta Kodu "%s" olan kuruma ait Uyumsoft Web Servislerini sorgulayabilecek kullanıcı bilgileri Fatura Numarası üreten veritabanında yok. Lütfen kullanıcı bilgilerinin veritabanına girilmesini sağlayınız ve tekrar deneyiniz!', 11, 1, @vkn, @gibPk)
 END

 
 BEGIN -- Sorgulamak için soap zarfını oluşturalım.
  DECLARE @Request INT
  Declare @Body as varchar(8000) = '<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
 <s:Header>
  <o:Security s:mustUnderstand="1" xmlns:o="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
   <o:UsernameToken>
    <o:Username>'+@username+'</o:Username>
    <o:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">'+@password+'</o:Password>
   </o:UsernameToken>
  </o:Security>
 </s:Header>
 <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <QueryOutboxInvoiceStatus xmlns="http://tempuri.org/">
   <invoiceIds>
    <string>'+@faturaUUID+'</string>
   </invoiceIds>
  </QueryOutboxInvoiceStatus>
 </s:Body>
</s:Envelope>'  

  EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @Request OUT;
  EXEC sp_OAMethod @Request, 'open', NULL, 'post','https://efatura.uyumsoft.com.tr/Services/Integration', 'false'
  EXEC sp_OAMethod @Request, 'setRequestHeader', null, 'SOAPAction', 'http://tempuri.org/IIntegration/QueryOutboxInvoiceStatus'
  EXEC sp_OAMethod @Request, 'setRequestHeader', null, 'Content-Type', 'text/xml; charset=utf-8'
  EXEC sp_OAMethod @Request, 'send', null, @body
  
  EXEC sp_OAMethod @Request, 'responseText', @ResponseText OUTPUT
  
  -- Uyumsoft kullanıcı adı ve şifresi geçersiz. Raiseerror !
  IF (CHARINDEX('You dont have enough permission to access this system',@ResponseText) > 0) RAISERROR('Uyumsoft servislerini sorgulamak için tanımlı "%s" kullanıcı adı ve "%s" şifresi geçerli değildir. Lütfen geçerli kullanıcı adı ve şifresinin Fatura Numarası Üreten veritabanına girilmesini sağlayınız. Alınan cevap: %s',19,1, @username, @password, @ResponseText);
  -- Uyumsoftun yanıtı boş ya da cevap içeren zarf yok. Raiserror!
  IF (@ResponseText IS NULL OR CHARINDEX('Envelope', @ResponseText) = 0) RAISERROR (N'Uyumsoft QueryOutboxInvoiceStatus metodundan hata alındı. Dönen sonuç: %s', 19, 1, @ResponseText);
  
  BEGIN -- Fatura durumunu çekip VT'yi güncelleyelim
   IF CHARINDEX('<Value Status="', @ResponseText) > 0
   BEGIN
    -- Faturanın son durumu 
    DECLARE @indexStart INT
    DECLARE @indexEnd INT
    
    SET @indexStart = CHARINDEX('<Value Status="', @ResponseText)+LEN('<Value Status="')
    SET @indexEnd = CHARINDEX('" StatusCode=', @ResponseText)  
    
    SET @invoiceStatus=SUBSTRING(@ResponseText, @indexStart, @indexEnd-@indexStart)
   END
  END

  -- Request objesini sonlandırım belleği özgürleştirelim
  Exec sp_OADestroy @Request
 END
END

IsEInvoiceUser

POST https://efatura.uyumsoft.com.tr/Services/Integration HTTP/1.1
Content-Type: text/xml; charset=utf-8
SOAPAction: "http://tempuri.org/IIntegration/IsEInvoiceUser"
Host: efatura.uyumsoft.com.tr

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/" xmlns:o="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
 <s:Header>
  <o:Security s:mustUnderstand="1" xmlns:o="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
   <o:UsernameToken>
    <o:Username>xxxxx</o:Username>
    <o:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">xxxxx</o:Password>
   </o:UsernameToken>
  </o:Security>
 </s:Header>
 <s:Body>
  <iseinvoiceuser xmlns:ns1='http://tempuri.org/'>
   <vkntckn>xxxxx</vknTckn>
  </IsEInvoiceUser>
 </s:Body>
</s:Envelope>

Sql Server'da Mirror yapılan DB nin LOG database'ini truncate etmek

arkadaşlar eger mirro olan databaelerin trans logu şitiyse sakın normal
yollarden yapmaya calısmayın, baska dosya isimleri kullanıp attach detach
filan yapmayın...

- sadece translog backup nı alın, genel ayarlarla oynamanıza gerek yok

- Databe uzerinde sql query acıp, DBCC LOGINFO('database ismi') diyip,
"status" değerinin sıfır oldugunu görün
- DBCC SHRINKFILE(logun virtual ismi, istediginiz boyut) ama sizin
database ler buyuk oldugu icin boyut 1gb altı olmasın yani1024
yazabilirsiniz
- eğer yukarıdaki işlem kucuktemdiyse SELECT name,log_reuse_wait_desc FROM
sys.databases; diyip duruma bakın, "NOTHING" yazmalı, yok LOG_BACKUP
yazıyorsa translog backup ı ilk adımdaki alıp yine shirink deneyin

bu yontemle medula 80gb dan 1 gb a indi, belki daha da inerdi ama cok
ihtiyac yok


Saygılarımla / Best Regards

Bülent Naci ALPAY
Sistem & Network Muduru
IT

Database içindeki tüm TRIGGER ları silmek

DECLARE @dbName AS NVARCHAR(MAX)
SET @dbName = 'AdventureZapaZupa'
DECLARE @sqlUse AS NVARCHAR(MAX)
SET @sqlUse = N'use '+@dbName
EXEC sp_executesql @sqlUse

DECLARE @sDelete AS NVARCHAR(MAX)
DECLARE @trigName AS NVARCHAR(200)
DECLARE @tableName AS NVARCHAR(200)
DECLARE @sql AS NVARCHAR(MAX)
  
DECLARE crTrigs CURSOR FOR
  SELECT name FROM sys.triggers order by name 
  
 OPEN crTrigs
  FETCH NEXT FROM crTrigs INTO @trigName
  
  WHILE @@FETCH_STATUS = 0
 BEGIN   
  SET @sql = N'use '+@dbName+' DROP TRIGGER [dbo].[' + @trigName + ']'
  EXEC sp_executesql @sql
    FETCH NEXT FROM crTrigs INTO @trigName
  END
   
 CLOSE crTrigs
DEALLOCATE crTrigs

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
    /*
    

     

      
KAYNAK
HEDEF
AKTARILAN
Sİ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
    /*
    

     

      
KAYNAK
HEDEF
AKTARILAN
Sİ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 + '

 

  
KAYNAK
HEDEF
AKTARILAN
Sİ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
KAYNAK
HEDEF
AKTARILAN
SİLİNEN
dbo.LogBirimler
dbo.LogBirimler
0
0

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

MSSQL ile Virtual Table ve UNPIVOT

Ref: http://stackoverflow.com/questions/2113251/tsql-help-with-unpivot

create table #tmp ( ac varchar(100), bc varchar(100), cc varchar(100))



insert into #tmp (ac,bc,cc) Values ('test1','test2','test3')



SELECT * FROM #tmp



SELECT * FROM #tmp

UNPIVOT ( [Column] FOR Data IN (ac, bc, cc)) uPIVOT





drop table #tmp

Sonuç:

MSSQL Server üstünde Kullanıcıya Profile edebilme izni vermek

 USE master
GRANT VIEW SERVER STATE TO kullaniciAdi
GRANT ALTER TRACE TO kullaniciAdi 
GO

MSSL de yedekleri otomatik sıkıştır

In SQL Server 2008 Enterprise and later only, used by the BACKUP Transact-SQL statement to select the backup compression setting if a user does not specify WITH COMPRESSION or WITH NO_COMPRESSION.

Ref: technet.microsoft.com

USE master;
GO
EXEC sp_configure ‘backup compression default’, '1';
RECONFIGURE WITH OVERRIDE;

Restrictions

The following restrictions apply to compressed backups:
* Compressed and uncompressed backups cannot co-exist in a media set.
* Previous versions of SQL Server cannot read compressed backups.
* NTbackups cannot share a tape with compressed SQL Server backups.

SQL 2005 için benim çözümüm

Harici bir batch çalıştırmak için xp_cmdshell çalışıtırılabilir olmalı.

EXEC master..xp_CMDShell 'ISQL -L'

Eğer yukarıdaki çalıştırılmak istendiğinde

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

hatası alınıyorsa, xp_cmdShell önce aktif edilmeli. Bu kısmı burada buldum.

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

-- xp_cmdshell özelliğini aktif edelim
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO 

Artık EXEC master..xp_CMDShell 'ISQL -L' çalıştırıldığında sonuç aşapıdaki gibi olacaktır:


Şimdi batch dosyamızda oluşturulan bak dosyamızı sıkıştırabiliriz. Çeşitli konsol sıkıştırma komutları ile bunu yapabiliriz ama COMPACT komutu windows 2000 ile gelmiş fena sıkıştırmayan bir komut. 10.639 Kb lik dosyayı 4.1 MB a sıkıştırdı (winrar 1.2 kadar sıkıştırmıştı da neyse)

COMPACT /C C:\Projeler\db_backup\DenemeDb.bak
pause

Şimdi bu batch dosyayı çağırma işine bakalım: