[AS400] 小量數據轉移數據到SQL Server之方法 (2013/07/11更新)

這幾天要幫公司把data從老舊的AS400轉移到SQL Server上面去。
由於之前沒有任何經驗所以先問一下古哥。
很多大大也是使用SQL Server內建的Import Export Wizard。
以下是我利用odbc的方法成功移轉的經驗,未必是最好不過希望給大家參考一下。

準備工作:

  1. 先在目的SQL Server建立一空的資料庫, 例如"AS400"
  2. 由於我用的是ODBC,所以目的地端的SQL SERVER一定要先設好IBM的Client Access,我的版本是V5R4M0。最後要設好連接到成功連接到AS400的ODBC連接字串。
  3. 另外檢查一下Client Access中有沒有設定使用者名稱,不然等一下會連不到喔。
    • 在ODBC資料來源管理員中選擇AS400的連接,按"設定"
    • 在"General"頁面中按"Connection Options"
    • 在"Use the user ID specified below"中輸入登入名稱,按"OK"
步驟:
  1. 在剛剛建立的空資料庫按右鍵-> Tasks-> Import Data,彈出匯入和匯出精靈後按下一步。
  2. 接下來是 "選擇資料來源" 頁面:
    資料來源選擇".Net Framework Data Provider for Odbc"

    Dsn填寫ODBC中連接AS400的字串名稱,如我的ODBC字串是AS400ODBC,填寫的就是這個名稱。並不是Dsn=AS400ODBC;uid=terence 這種喔。

    填寫完以後按下一步。
  3. 如果連接成功會來到"選擇目的地"頁面,不然就會出現謎一樣的錯誤,小弟試過的錯誤包括:
    1. DSN格式錯誤
    2. 在Client Access中沒有指定使用者名稱。
    在選擇目的地畫面設好目的地的資料庫,如我目的地的資料庫叫AS400,設好以後按下一步。
  4. 選擇"從一或多個資料或檢視表複製資料"或"寫入查詢來指定要傳送的資料",我選的是第一頁,按下一步。
  5. 接下來選擇需要匯入的資料表,要注意的是在準備工作中提到的在Client Access中的User權限是否足夠。選擇好以後按下一步。
  6. 再按下一步,直接按"完成"就開始複製工作,完成後出現以下畫面
  7. 最後打開查詢視窗打一下SQL Query, 查詢到的話就表示OK囉。
下面部門是一些小弟總結出來的經驗,會繼續更新:
  • 在轉移的過程中出現
    "Creating or altering table ‘%.*ls’ failed because the minimum row size would be %d, including %d bytes of internal overhead. This exceeds the maximum allowable table row size of %d bytes."

    這是因為在AS400中的單筆資料列超過了8060bytes. 例如在AS400中其中一欄位長度為6000, 那麼轉移過來的時候就很容易發生這種情況. 解決方法我暫時想到的有兩種:
    1. 把長度為6000的欄位轉成動態長度的data type, 例如varchar, ntext. (資料來源在此)
    2. 直接把過長的欄位縮短, 例如把6000改成1000.

  • 在轉移的過程中出現 0xc0202025 錯誤
    這是因為把AS400中資料長度很大的欄位轉成varchar(MAX)的原故, 只要放棄使用varchar改成ntext就可以了, 因為在AS400中column size可以設很大, 而SQL Server中過長的data要使用ntext.

  • 錯誤0xc020902a Text was truncated....DTS_E_PRIMEOUTPUTFAILED....
    這個錯誤的原因有很多, 我試過以下兩種情況:
    • 可能是目的地的Column長度不夠.
    • 一次insert太多data. 小弟最搞不清楚是這種情況. 例如我要同時匯入有很多欄位(40個)而且資料數量比較多(才2萬而已)的資料表的時候, 就會出現這個問題. 解決方法有:
      • 用import/export wizard一個一個資料表匯入. (不保證一定成功喔)
      • 把Data轉成過萬筆的insert statement, 然後直接執行. 不過根據小弟的經驗, 超過7MB的 Insert Statement是無法在management Studio使用的. 他會彈出提示說記憶體不足. 所以要使用下面所說的SQLCMD來解決

  • Running large MSSQL scripts & resulting memory errors
    Management Studio最令人火的是不能同時跑太大的insert statement (7MB以上), 所以一定要使用SQLCMD.
    • 先把要新增的insert statement存成.sql檔案
    • 打開cmd, 輸入 sqlcmd -S <IP位置> -H <資料庫名稱> -U <使用者名稱> -P <密碼> -i "<.sql檔案的位置>" 例如 sql -S 127.0.0.1 -H northwind -U user -P xxxx -i "C:\abc.sql"

  • 那怎麼辦? 下一篇會說.^_^

Popular posts from this blog

[SQL SERVER] 找出LOCK方法懶人包

[SQL Server] 解決log檔(ldf file)過度膨脹的實戰經驗

[Windows7] 跨距磁碟區, 等量磁碟區, 鏡像磁碟區之區別