利用IGNORE_DUP_KEY過濾重複的資料

要從SQL SERVER中某table過濾出重複的資料後，塞入另外一個table裡的方法很多。這幾天工作的關係，發現其實也可利用 ignore_dup_key 這個 relation_index_option來取巧。下面我建立個簡單的範例說明：

-------------------------------------------------------------------------------- --建立uniqindex , 並指定ignore_dup_key -------------------------------------------------------------------------------- --1. 建立target table，儲存無重複的資料 if exists (select name from sys.tables t where t.name = 'category')  drop table category go --2. 刪除既有的index create table category(cat_desc varchar(50), cat_code varchar(10)) if exists(select name from sys.indexes i where i.name = 'idx_cat')  drop index idx_cat on cayrgory go --3. 建立unique index，並指定ignore_dup_key create unique index idx_cat on category(cat_desc) with ignore_dup_key go --4. 建立1,000測試資料，其中5,000筆重複 declare @tb1 table(c1 varchar(50), c2 varchar(10)) declare @i int declare @loop_start_date datetime, @loop_end_date datetime declare @uniq_start_date datetime, @uniq_end_date datetime set @i = 1 set @loop_start_date = GETDATE() --設定迴圈起始時間 while @i <= 10000 begin  if @i % 2 = 0   insert @tb1 values('test' + cast((@i-1) as varchar(50)), cast((@i-1) as varchar(10)))  else   insert @tb1 values('test' +cast(@i as varchar(50)), cast(@i as varchar(10)))  set @i = @i + 1 end set @loop_end_date = GETDATE() --設定迴圈結束時間 set @uniq_start_date = GETDATE() --設定insert 至target table的起始時間 insert category(cat_desc, cat_code) select * from @tb1 set @uniq_end_date = GETDATE() --設定insert 至target table的結束時間 select 'inserting loop takes...' select DATEDIFF(SECOND, @loop_start_date, @loop_end_date) select 'inserting into uniq index with ignore_dup_key takes...' select DATEDIFF(SECOND, @uniq_start_date, @uniq_end_date) select * from @tb1 order by c1 select * from category order by cat_desc go

一開始我先建立一個target table，以儲存待會測試資料中已經過濾出來的無重複資料。在target table上我們建立一個unique index，並以WITH  IGNORE_DUP_KEY指定relational_index_option的值；接著宣告一個 table type的暫存資料表，並以迴圈塞入10,000筆資料，其中有5,000筆重複。最後我們直接將測試資料表insert 到target table中。這裡就是重點了。以往要是在建立索引時沒有加上 with ignore_dup_key這個選項，則insert到重複的資料時，就會得到一個錯誤的訊息，整個insert的transaction就會終止並且rollback：

訊息 2601，層級 14，狀態 1，行 8

無法以唯一索引 'idx_cat' 在物件 'dbo.category' 中插入重複的索引鍵資料列。

為了能略過該筆重複的資料所造成的錯誤並使insert動的繼續進行，create unique index時指定 with ignore_dup_key 即可辦到。以後如果懶得寫一堆過濾語法，或許可以偷個懶用用這個方法。

Oracle複寫到SQL 2005時，卡在DELETE ARTICLE的終極解法

前篇文章提到當利用SQL 2005複寫機制，將Oracle複寫到SQL端時，一段時間後就會卡死在DELTE FROM Articlexxx的SQL Statement，除了速度緩慢之外，也連帶的影響其他工作效能。最後跟同事試了幾種方式，經由同事的實驗確認了以下方式會取得最佳狀態：

• 建立好複寫機制後，替每個Table做好統計值分析；再確認執行計畫是否有使用Index即可(使用Range Scan而非Full Table Access)。這個步驟有點麻煩；我們的問題是當複寫的時候，DELETE FROM Articlexxx 會因為執行計畫不佳而Hang在哪裏。但每個Article都可能會有這樣的問題，所以我們以Oracle端部署時所用的複寫帳號登入後(假設是repadm)，透過以下SQL Statement準備各Article的DELETE Article SQL Statement：
  
   SELECT 'DELETE FROM ' || TABLE_NAME  ||
         ' WHERE EXISTS ' ||
            (SELECT p.POLL_POLLID ' ||
               'FROM HREPL_POLL p ' || 
             'WHERE CHARTOROWID(l.ROWID) = p.Poll_ROWID ' || 
             '       AND p.Poll_PollID = :Pollid)'
     FROM USER_TABLES
   WHERE TABLE_NAME LIKE 'HREPL_ARTICLE%'
  
  之後，我們先塞點假資料給HREPL_POLL以及各個Article以模擬大量資料(剛開始沒資料，執行計畫就會用Full Table Access的方式規劃)，然後分別開始做統計分析；這時候取得統計值就會接近實際狀況。
  
• 準備好統計資訊後，利用 outline 的方式鎖住上述每一個DELETE Article SQL Statement的執行計畫，避免大量DML後Oracle對該DELETE Statement的執行計畫跑掉。
• 以後每建立一個新的發行項目(Article)，就要執行上述步驟(修改一下SQL Statement取得該新發行項目的Delete Articlexxx SQL Statement)以取得並鎖定最佳的執行計畫。

目前跑了一週，已經沒再發生Hang住的問題，這方法提供參考囉。

Oracle複寫到SQL Server的概略圖

下圖為透過SQL Server複寫機制，將Oracle複寫到SQL Server的架構，概略說明日後補上：

相關在Oracle中建立的Table物件請參考 這裡，流程概念請參考 這裡。

錯誤：18456，SQL 2005 "myUserAccount" 登入失敗

早上一來同事就大喊無法登入SQL Server測試機，快速檢查了一下，原來是登入機制有設定「強制設定密碼原則」，但因為測試機的關係隨手就把它關掉。再次嘗試登入後還是出現錯誤，這次的錯誤代碼為「18456」。Google了一下，發現 這裡 有解法，但它更具價值的地方在於文章下方的表格，已列出錯誤狀態碼各自代表的錯誤描述，好讓你更快速的判斷造成「18456」的根本錯誤原因。最後把幾個instance的登入者密碼改改就好啦，分享囉~



後記：


改了登入者密碼後，同事過沒多久又開始鬼叫無法alter procedure。找了半天才發現，原來是sp裡面有使用server link，連結到已經有修改過密碼的target。這只要再修改「遠端伺服器\屬性\安全性\」中的遠端登入密碼即可。

SQL Server 增加欄位的至指定位置的錯覺

如果你以SQL Server Management Studio來編輯現有Table的欄位位置，感覺好像很方便，但說穿了，不過是玩了一招移花接木的招數而已。有圖為證：

原本Test 表只有一個a欄位，我用Studio修改該表，加入一個新欄位v並且放到a前面，然後按下左上角的「產生變更指令」後，就可看到上面的圖示；我把指令給「放大」如下：

/* 為了避免發生資料可能遺失的任何問題，您應該先詳細檢視此指令碼，然後才能在資料庫設計工具環境以外的位置執行。*/

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION

GO

CREATE TABLE dbo.Tmp_test

(

v nchar(10) NULL,

a varchar(20) NULL

) ON [PRIMARY]

GO

IF EXISTS(SELECT * FROM dbo.test)

EXEC('INSERT INTO dbo.Tmp_test (a)

SELECT a FROM dbo.test WITH (HOLDLOCK TABLOCKX)')

GO

DROP TABLE dbo.test

GO

EXECUTE sp_rename N'dbo.Tmp_test', N'test', 'OBJECT'

GO

COMMIT

就這樣，當你以為SQL 2005真的做了什麼了不起的事的時候，小心，說不定它也只是取巧而以。

[SQLSTATE 42000] (錯誤 20036) 解法

如果你發現在「作業活動監視器」有個「清除逾期的訂閱」作業歷程老是告訴你無法執行成功，錯誤訊息又是[SQLSTATE 42000] (錯誤 20036)的話，你應該犯了跟我一樣的「錯」。這個錯誤來自散發者伺服器未將本身加入發行者的所造成的，看起來應該是個SQL2005的Bug。解決方式很簡單，把散發者加入發行者就可以了；步驟參考 這裡，我翻譯成中文如下：

1. 在複寫節點按右鍵，選擇「散發者屬性」。
2. 在左邊「選取頁面」選擇「發行者」。
3. 在右邊選擇「加入」按鈕，並選擇「加入SQL Server發行者」。
4. 提供散發者登入資訊即可。
5. 回到「作業活動監視器」，按右鍵點選出錯的「清除逾期的訂閱」作業，選擇「從下列步驟啟動作業」即可。

一開始假設我沒犯了潔癖的毛病，把預設加入發行者的散發者給移掉的話，這錯誤也不會困擾我好些陣子，真是無語。

解決SQL Server改變Hostname後無法安裝訂閱服務的問題

你遇過改掉SQL Server主機名稱後，就無法繼續安裝訂閱服務之類的事嗎？不管你怎樣試，都會得到一個無法辨識主機名稱之類的錯誤訊息，並且告訴你最好給定SQL Server一個hostname。但事實上這個錯誤來自於SQL Server內部仍記著原來舊的主機名稱，而與instancename不一致所引起的。至於為何如此，目前我尚未有結論，等精神好點再去追根究底一番；目前先請Google大神提供一個解決方案，原理是透過修改@@servername的方式，讓SQL Server重新取得修改過的hostname即可。

修正後請重新啟動SQL Server服務，之後就可以正確的安裝訂閱服務囉。

DatabaseMail的郵件報表功能設定

SQL2005提供郵件通知的服務，並透過profile設定的方式，系統也可發送工作完成後的郵件報表。但很不幸的，從郵件服務設定到產生工作的郵件報表的過程有點類似樂高積木，中間眉眉角角的設定一不小心可能就會漏掉，搞得DBA只好"再度"捲起袖子巡田了。最近遇到的問題是工作完成後無法產生郵件報表，SQL Server給的錯誤訊息如下：

無法產生郵件報表。執行 Transact-SQL 陳述式或批次時發生例外狀況。未設定全域設定檔。請在 @profile_name 參數中指定設定檔名稱。

開始對這訊息是一頭務水，Mail發送測試沒問題，SQL Agent Service也重開過，怎麼還會給這莫名奇妙的訊息？查了一下才知道原來是郵件服務找不到預設的 profile 檔，所以郵件報表的也就沒辦法產生了。解法很簡單：

1. Database Mail按右鍵，點選「設定Database Mail」，會出現Database Mail組態精靈
2. 選擇「管理設定檔安全性」













3. 在「公用設定檔」頁籤下方選擇一個已建立好的Profile，並在「預設設定檔」欄位選擇「是」，這樣就成啦。













說穿了也沒什麼，但沒做這一步郵件報表就是給它看得到吃不到。給大家參看囉。

設定SQL 2005 Net Send 服務

說穿了 SQL 2005的Net Send就是執行 Windows 的 Messenger 服務，要啟動它簡單幾個步驟就搞定：

1. 啟動SQL Server主機 的Messenger 服務
2. 新增操作員
3. 設定操作員的Net Send的主機位址(IP也可)
4. 開啟操作員登入的Client端主機的Messenger 服務

四個步驟就可以搞定通知服務了。

Transaction log 吃掉你的硬碟時怎麼辦?

前幾天剛好遇到交易紀錄檔長滿了(700多GB)，搞到硬碟空間一滴不剩，相依的應用系統陸續掛點。最後判斷log不重要後，解決的方式就採破釜沉舟的方式進行：

1. 對交易紀錄檔作截斷處理(這動作還不會釋放空間)

backup log mydb with truncate_only

參考：http://technet.microsoft.com/zh-tw/library/ms189085.aspx

2. 壓縮交易紀錄檔空間(這個才會釋放空間)

dbcc shrinkfile(‘mydb_log’,2)　//給我縮到2mb!

參考：http://technet.microsoft.com/zh-tw/library/ms189493.aspx