2008/03/31

[SSIS]小數位數不見了?

最近使用 SSIS 將 Oracle 資料轉換,實際測試後卻發現小數位數不見了?

經過交叉測試發現是 SSIS 在 OLEDB Source 轉換時,自行猜測資料型別時出了問題。

如下圖所示,原本 NUMBER(16,6) 進到 SSIS 後,變為 NUMERIC(38,0) 就會造成小數位數不見了,

這時要手動調整,點選 OLE DB Source ,按下右鍵 Show Advance Editor,將 Output Columns 調整一下就 OK.

 

image

2008/03/26

[SSIS]Script Task Add Reference Issue

在 Script Task 中,使用 VSA(*註一) 語法時,遇到兩個問題

Q1:Add Reference 一直找不到 Assemblies 組件?

Q2:找到了 Assemblies 後,Design Script 可以過,但是 Runtime 無法找到組件

後來發現 Kirk Haselden 這篇文章(http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/07/16018.aspx)

歸納幾點結論:

  • Reference Strong Name Assemblies ,只有強式名稱組件能被 Runtime 執行
  • Reference Assemblies in GAC & %windir%\Microsoft.NET\Framework\v2.0.50727\

 

*註一:VSA-Visual Studio for Applications

2008/03/18

[Oracle][KB]ORA-04091 table string.string is mutating, trigger/function may not see it

第一次寫 Oracle Trigger 就遇到這種怪問題,其實 Trigger 寫的也很簡單

這是 Sample 的 Trigger

   1:   
   2:   
   3:  select count(*) into v_cnt from tableA where xxxx
   4:   
   5:  if (v_cnt>0) then
   6:   
   7:  ...........
   8:   
   9:  end;

 

結果遇到 TableA 的 Trigger 要再去存取 TableA 時,會出現以下錯誤訊息

 

ORA-04091 table string.string is mutating, trigger/function may not see it

Cause: A trigger (or a user defined PL/SQL function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.

Action: Rewrite the trigger (or function) so it does not read that table.

 

後來找到在 Trigger 使用 pragma autonomous_transaction 可以避免一些 Transaction 問題,

加上去後,果然是可以順利的觸發 Trigger , Thanks Google.

 

另外在補充一點,不只是 Trigger 可以使用  ,例如像自行定義的給號 Oracle Function 也可以加上去,

可以讓很單純的給號程式,可以直接在 Insert 指令做完,例如

Select GetSeqno('活動序號')  from dual
Insert Into Actioin (Seqno,ActionName) values ( GetSeqno('活動序號'),'到訪1萬人次活動');
 

[SSIS] The RPC server is unavailable

從遠端用 SQL 2005 Management Studio 連接 SSIS 服務時,遇到了 The RPC server is unavailable 問題,

 

後來發現是防火牆的問題,查到 SQL 2005 Book 的 HOW TO ,順便引用一下,也提醒一下自己

 

How to: Configure a Windows Firewall for Integration Services Access

To configure the Windows XP Firewall or Windows Server 2003 Firewall

  1. In the Control Panel, double-click Windows Firewall.

  2. In the Windows Firewall dialog box, click the Exceptions tab and then click Add Program.

  3. In the Add a Program dialog box, click Browse, navigate to the Program Files\Microsoft SQL Server\90\DTS\Binn folder, click MsDtsSrvr.exe, and then click Open. Click OK to close the Add a Program dialog box.

  4. On the Exceptions tab, click Add Port.

  5. In the Add a Port dialog box, type RPC(TCP/135) or another descriptive name in the Name box, type 135 in the Port Number box, and then select TCP.

    Important:

    Integration Services service always uses port 135. You cannot specify a different port.

  6. In the Add a Port dialog box, you can optionally click Change Scope to modify the default scope.

  7. In the Change Scope dialog box, select My network (subnet only) or type a custom list, and then click OK.

  8. To close the Add a Port dialog box, click OK.

  9. To close the Windows Firewall dialog box, click OK.

http://msdn2.microsoft.com/en-us/library/ms141198.aspx

2008/03/05

Tips-如何開啟 TOAD 背景執行指令功能?

這個是 TOAD 殺手級必要設定的選項,可以讓你的 TOAD 不會卡住,趕快找出來勾起來吧!!

clip_image002