德瑞克：SQL Server 學習筆記: 2009/11

2009-11-25

新手學SQL Server 2008 「多伺服器作業(Multi-Server Jobs)」 (上)

一般而言，SQL Server在企業上的應用，不會是僅有一兩台，而是有多台資料庫伺服器的使用，這對於資料庫管理師而言，在日常維護作業上，需要同時管理眾多的伺服器，將造成沈重的管理負載。

所以在面對擁有多台伺服器的環境，如何管理自動化，將是一大課題，關於多伺服器管理自動化的主題，包含十分廣泛，本文將以「多伺服器作業(Multi-Server Jobs)」進行討論。

資料庫伺服器所包含的日常維護作業，十分繁瑣，例如：備份資料庫與交易記錄、重組索引、更新索引統計值、對資料庫執行內部一致性檢查、轉檔作業等等。

雖然SQL Server有提供「維護計畫精靈」可以輕鬆地完成上述部分作業，但是面對擁有多台伺服器的環境，對每台資料庫伺服器逐一進行設定，以及後續的變更修正，將是讓人不得不去碰觸的難題。

在過往經驗中，待過百多台SQL Server的單位，也見識到擁有數千台SQL Server的單位，但專屬的資料庫管理人員可能是個位數，真是將「以更少做更多(Do More With Less)」的精神發揮到極致，利用SQL Server內建的「多伺服器作業(Multi-Server Jobs)」，將可以協助資料庫管理人員輕鬆地自動化管理多台伺服器的日常維護作業。

完整的文章，請參考：第91期之二《新手學SQL Server 2008 「多伺服器作業(Multi-Server Jobs)」（上）》

下載資料

更多相關的技術文章，請參考：DB World 資料庫專家電子雜誌
http://www.dbworld.com.tw/

2009-11-18

忘記 sa 密碼，也無法使用 Windows 認證的 Administrator 帳戶來登入 SQL Server。I forgot my SA password and cannot login as administrator using windows authentication.

若是遇到以下的情境，將導致無法登入到 SQL Server：

1. 屬於 sysadmin 固定伺服器角色之成員的所有登入，都因為錯誤而遭到移除。
2. 屬於 sysadmin 固定伺服器角色之成員的所有 Windows 群組，都因為錯誤而遭到移除。
3. 屬於 sysadmin 固定伺服器角色之成員的員工都已經離職，但沒有交接相關資訊。
4. sa 帳戶已遭到停用或是沒有人知道密碼。

已測試過的適用版本：
SQL Server 2005、2008

有數種的解決方法，例如：
1. 重新安裝 SQL Server。
2. 將另外一個執行個體的 master 資料庫的實體檔案(master.mdf、mastlog.ldf)，直接覆蓋掉此受損的執行個體之 master 資料庫。

之後再利用先前對此執行個體的 master 資料庫備份來還原，但這些作法，可能相當耗時。
此外，如果 master 資料庫的備份較舊，可能不會有所有的資訊。
如果 master 資料庫的備份比較新，可能與先前的執行個體擁有相同的登入，因此，系統管理員仍然會遭到鎖定。

在 SQL Server 2008 線上叢書內的「疑難排解：當系統管理員遭到鎖定時連接到 SQL Server」一文，有提供一項節省時間的作法：1. 以單一使用者模式使用 -m 或 -f 選項啟動 SQL Server 的執行個體。
2. 接著，電腦本機管理員群組的任何成員都可以利用 sysadmin 固定伺服器角色的成員身分，連接到 SQL Server 的執行個體。

SQL Server 服務啟動選項之說明：
-f
啟動只含最小組態的 SQL Server 執行個體。
如果組態值設定 (如過度調配記憶體) 造成伺服器無法啟動，這就很有用。
以最低組態模式啟動 SQL Server 會將 SQL Server 放在單一使用者模式下。如需詳細資訊，請參閱隨後的 -m 描述。

-m
當您以單一使用者模式啟動 SQL Server 執行個體。以單一使用者模式啟動 SQL Server 執行個體時，只有單一使用者可以進行連接，且不會啟動 CHECKPOINT 處理序。
CHECKPOINT 會保證將交易規律地從磁碟快取區寫到資料庫裝置。(一般而言，如果遇到一些應該修復系統資料庫的問題時，就會使用這個選項)。
啟用 sp_configure allow updates 選項。根據預設，allow updates 是停用的。
在單一使用者模式下啟動 SQL Server 可讓電腦本機管理員群組的任何成員以 sysadmin 固定伺服器角色的成員身分，連接到 SQL Server 的執行個體。

實作練習：
任務1：停用 SQL Server 與 SQL Server Agent Service
當您以單一使用者模式啟動 SQL Server 執行個體時，請先停止 SQL Server Agent 服務。
否則，SQL Server Agent 有可能會先進行連接，您就無法以另一個使用者的身分進行連接。

任務2：以「單一使用者模式(single user mode)」來啟動 SQL Server 執行個體1. 在命令提示列（Command Prompt）中，執行以下的範例程式碼：

net start mssqlserver /m

上述程式碼是以預設的執行個體為例。若是為 SQLEXPRESS 版本，且安裝為具名執行個體個體時，可能的範例程式碼如下：

net start mssql$sqlexpress /m

任務3：使用本機 Windows 的 Administrators 管理群組之成員來連線 SQL Server
使用本機 Windows 的 Administrators 管理群組之成員來連線 SQL Server 後，就可以各項作業，例如：
1. 重新設定 sa 帳戶的密碼。
2. 啟用 sa 帳戶。
3. 設定「伺服器驗證」模式，例如：修改為「SQL Server 及 Windows 驗證模式」，也就是「混合(mixed)模式」。
4. 加入指定的 Windows 帳戶到 sysadmin 固定伺服器角色內。
... 等等。

1. 使用 SSMS 管理工具連線 SQL Server。
這項 SSMS 管理工具雖然很方便，但可能產生多條的連線來執行，將會導致您無法調整所需要的設定。

2. 使用 sqlcmd 模式來登入系統，這會是可以確保單一使用者連線的作法，以下提供數種範例程式碼：

-- 重新設定 sa 帳戶的密碼為：P@ssw0rd。
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'P@ssw0rd'
GO

-- 啟用 sa 帳戶
USE [master]
GO
ALTER LOGIN [sa] ENABLE
GO

-- 修改為「SQL Server 及 Windows 驗證模式」，也就是「混合(mixed)模式」
/*
1 是指：「Windows 驗證模式」
2 是指：「SQL Server 及 Windows 驗證模式」
*/
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO

任務4：關閉 SQL Server，再一般模式來重新啟動 SQL Server
經過上述的步驟，應該可以讓系統恢復正常登入方式。

參考資料：
疑難排解：當系統管理員遭到鎖定時連接到 SQL Server
http://msdn.microsoft.com/zh-tw/library/dd207004.aspx

使用 SQL Server 服務啟動選項
http://msdn.microsoft.com/zh-tw/library/ms190737.aspx

以單一使用者模式啟動 SQL Server
http://msdn.microsoft.com/zh-tw/library/ms188236.aspx

sp_password (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms174428.aspx

如何驗證與變更 MSDE 系統管理員密碼
http://support.microsoft.com/kb/322336

2009-11-11

新手學 SQL Server 2008 「以原則為基礎的管理(Policy-Based Management，PBM)」(5)：技術文章

在前一篇：「新手學以原則為基礎的管理(4)」文章中，我們討論：檢視原則的健全狀態、匯出與匯入原則、匯入與使用「SQL Server最佳作法」以及排程定期評估指定的原則等主題，每一項的「SQL Server最佳作法」都有其使用情境與需求。

在本期文章中，我們將繼續介紹相關主題：檢視與設定伺服層級的「Facet」、「介面區組態」、「以原則為基礎的管理」搭配「中央管理伺服器」等等。

完整的文章，請參考：第93期之一《新手學以原則為基礎的管理（5）》

下載資料

相關參考資料：
新手學 SQL Server 2008 「以原則為基礎的管理(Policy-Based Management，PBM)」(1)：技術文章
http://sharedderrick.blogspot.com/2009/03/sql-server-2008-policy-based.html

新手學 SQL Server 2008 「以原則為基礎的管理(Policy-Based Management，PBM)」(2)：技術文章
http://sharedderrick.blogspot.com/2009/03/sql-server-2008-policy-based_18.htm

新手學 SQL Server 2008 「以原則為基礎的管理(Policy-Based Management，PBM)」(3)：技術文章
http://sharedderrick.blogspot.com/2009/06/sql-server-2008-policy-based.html

新手學 SQL Server 2008 「以原則為基礎的管理(Policy-Based Management，PBM)」(4)：技術文章
http://sharedderrick.blogspot.com/2009/10/sql-server-2008-4.html

更多相關的技術文章，請參考：DB World 資料庫專家電子雜誌
http://www.dbworld.com.tw/

2009-11-09

將 SQL Server 資料庫建立在網路硬碟(例如：NAS，Network Attached Storage)：network database

本文僅討論實作方式，並未包含：效能、安全等主題的討論。

若在建立資料庫時，選擇是建立到網路硬碟時，將遇到以下的錯誤訊息：

訊息 5110，層級 16，狀態 2，行 1
檔案 "\\10.0.1.8\d$\DBNetDB01.mdf" 所在的網路路徑不支援資料庫檔案。
訊息 1802，層級 16，狀態 1，行 1
CREATE DATABASE 失敗。某些列出的檔案名稱無法建立。請檢查相關的錯誤。

若是確認要在網路硬碟(例如：NAS，Network Attached Storage) 上建立資料庫，請啟用追蹤旗標 1807 。

啟用追蹤旗標 1807 會略過檢查，並允許您使用網路資料庫檔案設定 SQL Server。

因為網路錯誤有損害資料庫完整性的風險，以及使用網路檔案共享來儲存資料庫也可能影響效能。

建議：
您利用存放區域網路 (SAN)、iSCSI 型網路或本機連接的磁碟來儲存 SQL Server 資料庫檔案，因為這個組態可使 SQL Server 效能和可靠性最佳化。
依預設，不會對 SQL Server 啟用使用網路資料庫檔案 (儲存在具備網路功能的伺服器或網路連接的儲存體上)。

實作練習

適用版本：
SQL Server 7、2000、2005、2008

準備工作
請先設定、確認執行個體的「SQL Server 服務帳戶」，對於遠端的網路硬碟具備寫入的權限。

工作1：啟用追蹤旗標 1807
工作2：建立資料庫在 UNC 網路硬碟。

請參考以下的範例碼：

-- 啟用追蹤旗標 1807 。
DBCC TRACEON (1807)
GO

-- 列出所有針對目前工作階段而啟用的追蹤旗標。
DBCC TRACESTATUS;
GO

-- 建立資料庫，已啟用追蹤旗標 1807。
CREATE DATABASE [NetDB01] ON  PRIMARY
( NAME = N'NetDB01', FILENAME = N'\\10.0.1.8\c$\NetDB01.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'NetDB01_log', FILENAME = N'\\10.0.1.8\c$\NetDB01_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

-- 停用指定的追蹤旗標 1807
DBCC TRACEOFF(1807)
GO

-- 列出所有針對目前工作階段而啟用的追蹤旗標。
DBCC TRACESTATUS;
GO

-- 檢查資料庫的檔案
SELECT * FROM NetDB01.sys.database_files

-- 對此資料庫新增資料表與資料
SELECT *
INTO NetDB01.dbo.TransactionHistory
FROM AdventureWorks2008.Production.TransactionHistory

請參考下圖所示：

參考資料：
說明 SQL Server 對網路資料庫檔案的支援
Description of support for network database files in SQL Server
http://support.microsoft.com/kb/304261

每個資料庫管理員都應知道的磁碟機快取與 SQL Server 並用說明
Description of using disk drive caches with SQL Server that every database administrator should know
http://support.microsoft.com/kb/234656/

2009-11-08

使用 SSIS 將 SSRS 所產生 Excel 檔案，做適當的歸檔與分類

友人的需求，因為使用 SSRS 雖然可以直接將 Excel 檔案以電子郵件 E-Mail 方式發送到使用者端，但使用者平常沒時間看，之後還是需要將此 Email自行歸檔到適當的資料夾內存放。

所以，要求能將 Excel 自動歸檔的適合的資料夾內。

可以能處理方式有數種，以下是其中一種處理方式：

(一) 設定 SSRS 產生 Excel 到特定資料夾
請參考下圖所示：

在「路徑」文字方塊中，輸入到您要傳遞報表之現有資料夾的通用命名慣例 (UNC) 路徑 (例如，\\\)。
在路徑的開頭包含雙反斜線字元。請勿在尾端指定反斜線。

關於檔案的覆寫機制有以下的設定方式：
「以較新新版本覆寫現有檔案」。
「如果舊版存在，不要覆寫檔案」。
「加入較新版本時，遞增檔案名稱」。

(二) 利用 SSIS 來移動、複製、改名所需的 Excel 檔案
使用「檔案系統工作」的等功能，將所產生的 Excel 檔案複製移動到適當的資料夾做分類。

「檔案系統」工作會在檔案系統中的檔案和目錄上執行作業。
例如，封裝可使用「檔案系統」工作建立、移動或刪除目錄和檔案。
您也可以使用「檔案系統」工作設定檔案和目錄的屬性。例如，「檔案系統」工作可將檔案設為隱藏或唯讀。

延伸應用：
1. SSRS 產生 Excel 檔案，利用 SSIS 的「指令碼工作」來加密 Excel 檔案，再利用「傳送郵件工作」將此加密的 Excel 檔案，E-mail 發送給所需的使用者。
2. 取代某些需要利用 SSIS 動態產生 Excel 檔案的情境。

適用版本：SSIS 2005、SSIS 2008、SSRS 2005、SSRS 2008

參考資料：
如何：訂閱報表 (報表管理員)
http://technet.microsoft.com/zh-tw/library/ms157386.aspx

檔案系統工作
http://technet.microsoft.com/zh-tw/library/ms140185.aspx

2009-11-03

SSIS 2008 的「指令碼工作(Script Task)」之 ReadOnlyVariables 屬性

更新日期：2013/04/23

經過測試，SSIS 2012版本，在 SSDT 2012 上仍是有此問題。

在SSIS 2008 的「指令碼工作(Script Task)」，與變數有關的兩個的屬性：ReadOnlyVariables 與 ReadWriteVariables 。
ReadOnlyVariables：輸入以逗號分隔的變數清單，以供指令碼轉換進行「唯讀」存取。
ReadWriteVariables：輸入以逗號分隔的變數清單，以供指令碼轉換進行可「讀寫」存取。

實作環境：
SSIS2008 SP1

問題描述：
使用「指令碼工作」，設定輸入變數為「ReadOnlyVariables」。

但實際上卻仍然可以對此變數寫入資料。

預期行為：
不可以對此變數寫入資料。
應改用「ReadWriteVariables」，才可以寫入資料。

處理方式：
這個已經是一個open bug，在以下兩個連結都有討論。看起來是下一個service pack會修復.
SSIS Script Task passed in read only variable is updateable
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=369187

SSIS Script task parameters
http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/c823a993-7b48-49be-8513-3085655ba91f/

在此感謝 Vera 熱心的協助。

更新日期：
2010/0703

使用環境：
SQL Server 2008 R2 中文企業版本。

經過測試，仍然有此問題。

參考資料：
SQL Server 2008 線上叢書 (2009 年 7 月)指令碼轉換編輯器 (指令碼頁面)
http://msdn.microsoft.com/zh-tw/library/ms181225.aspx

SSIS 2008 的「指令碼工作(Script Task)」只能叫用一次中斷點做偵錯作業。debugger hits breakpoints in only one Script task

使用的版本：SSIS 2008 sp1

實作情境：
1. 外層使用「Foreach 迴圈容器」，選取指定的資料夾內的多個檔案。在內層包含一個「指令碼工作」，分別對這些檔案進行處理。
2. 在「指令碼工作」內設定「中斷點」。

預期結果：
1. 每選取到一個檔案，應該就叫起「Visual Studio Tools for Applications (VSTA)」偵錯工具才對。
2. 但實際上執行時，卻僅有在處理第一個檔案時，才有執行「Visual Studio Tools for Applications (VSTA)」偵錯工具。後續的檔案處理部分，都沒有叫起「Visual Studio Tools for Applications (VSTA)」偵錯工具。

但在 SSIS 2005 版本，正確可行。

依據 SQL Server 2008 的線上文件之說明：
當您為包含多個指令碼工作的封裝偵錯時，偵錯工具只會在一個指令碼工作中叫用中斷點，而且將會忽略其他指令碼工作中的中斷點。
如果指令碼工作是 [Foreach 迴圈] 或 [For 迴圈] 容器的一部分，偵錯工具會在迴圈的第一次反覆運算後，忽略指令碼工作中的中斷點。

可能的解決方法：
1. 使用 MessageBox.Show() 。
2. 撰寫foreach的程式碼來loop檔案。

詢問微軟 CSS 後的回覆，重點整理如下：

SSIS 2005 使用的是 VSA 2005，而在 SSIS 2008 使用的是 VSTA 2008，但目前 VSTA 尚未支援在同一個封裝中載入多個「Script task」的「assembly」，因此無法在「Foreach Loop」內對多個「Script Task」執行偵錯作業
預計會下一版的 SQL Server 做討論。

在此感謝 Vera 熱心的協助。

參考文件：
SQL Server 2008 線上叢書 (2009 年 7 月)如何：在指令碼工作中設定中斷點來偵錯指令碼
http://msdn.microsoft.com/zh-tw/library/ms140033.aspx

SQL Server 2008 線上叢書 (2009 年 7 月)指令碼工作
http://msdn.microsoft.com/zh-tw/library/ms141752.aspx

SQL Server 2005 線上叢書 (2008 年 11 月)
指令碼工作
http://msdn.microsoft.com/zh-tw/library/ms141752%28SQL.90%29.aspx

2009-11-01

SQL Server 2008：SSIS 2008 與 Excel 2007(.xlsx、.xlsb)

在使用 Excel 2003(*.xls)時，有數項條件限制，例如：

* 最大資料列筆數是 65,536 列。Excel 匯出選項會檢查此數值，若超過此限制，會在 Excel 中顯示錯誤。
* 最大資料行數是 256 行。Excel 匯出選項會檢查此數值，若超過此限制，會在 Excel 中顯示錯誤。

要解決此問題，可能有幾個方法：

* 當資料筆數會超過 65,536 筆數時，採用新增工作表的方式，讓每個工作表的資料列筆數，不會超過 65,536 筆數的軟體限制。
* 改用 Excel 2007 ，存檔成 *.xlsx 的檔案格式，也可以解決此問題。
將資料匯出成 *.csv 檔案格式，分隔符號選擇為：[逗號{,}]，再利用 Excel 2007 開啟存取，也可以正常運作。

改用新版本的 Excel 2007(*.xlsx) 也可以改善這些問題，舉例來說：

* 可支援到 1,048,576 筆資料列。
* 可支援到 16,384 資料行。
* Excel 2007 讓試算表的容量擴充到超過 1 百萬資料列和 1 萬 6 千資料行。

可以使用兩種方式來將資料轉換為 Excel 2007(*.xlsx、*.xlsb)
一、使用「匯入和匯出資料 (32 位元)」，也就是「SQL Server 匯入和匯出精靈」。
二、使用 BIDS(SQL Server Business Intelligence Development Studio) 的封裝設計師。

注意事項：
Excel 2007 並沒有支援 SQL Server 2008 全部的資料類型，請記得設定合適的資料類型。
異質資料庫的資料轉換作業，資料類型的整併，也是一項需要注意的課題。

本文以使用「匯入和匯出資料 (32 位元)」，也就是「匯入匯出精靈」來做討論：

一、Excel 2007 當做資料目的地
步驟如下：
1. 執行「SQL Server 匯入和匯出精靈」。
2. 在「選擇資料來源」視窗上，本次範例使用以下的選項：
在「資料來源」方塊，選擇：「SQL Server Native Client 10.0」。
在「伺服器名稱」方塊，選擇指定的伺服器。
在「資料庫」方塊，選擇：Northwind。
點選「下一步」。

3. 在「選擇目的地」視窗上，使用以下的選項：
在「目的地」方塊，選擇：「Microsoft Office 12.0 Access Database Engine OLE DB Provider」。
點選「屬性」。

4. 在「資料連結內容」視窗，使用以下的選項：
在「連線」頁面，在「資料來源」方塊，輸入存放此 Excel 2007 檔案的路徑，例如：C:\myOrders.xlsx。
點選「全部」頁面，先點選「Extended Properties」方塊(有些會翻譯為：擴充屬性)，點選「編輯內容值」，請依據需求填入以下的任一值：

若 Excel 2007 的附檔名為：xlsx 時，請填入：Excel 12.0 Xml 。
若 Excel 2007 的附檔名為：xlsb 時，請填入：Excel 12.0 。

假使填寫錯誤，雖然資料能夠成功匯出，但卻無法開啟。
經過測試，請自行修改為正確的附檔名後，就可以開啟。
5. 點選「確定」，完成「選擇目的地」視窗的設定，再點選「下一步」。r
6. 在「指定資料表複製或查詢」視窗，點選「從一或多個資料表或檢視表複製資料」方塊。

7. 點選「下一步」。
8. 在「選取來源資料和檢視」視窗，勾選所需要的資料表和檢視，例如：勾選[dbo].[Orders]。

9. 點選「下一步」。
10. 在「檢閱資料類型對應」視窗，使用以下的選項：
在「資料類型對應」區域，建議取消在「轉換」方塊下，所勾選的部份。

雖然「SQL Server 匯入和匯出精靈」能自動偵測是否需要執行轉換資料類型。
但在 SQL Server 2008 SP1 版本上，這項功能可能會有問題。

11. 點選「下一步」。
12. 在「儲存並執行封裝」視窗，點選「立即執行」方塊。

13. 點選「下一步」。
14. 在「完成精靈」視窗，點選「完成」。

15. 在「已成功執行」視窗，點選「關閉」。

完成本次實作練習。

以下為將此次轉換作業存檔為封裝後，觀察有使用自動轉換與取消自動轉換兩者之差異：
(A) 使用預設的自動轉換
SSIS 2008 會自動轉型為：DT_NTEXT。

(B) 取消自動轉換
使用的資料類型為：DT_WSTR。

有關於Integration Services 資料類型的說明：
DT_WSTR：最大長度為 4000 字元，以 Null 終止的 Unicode 字元字串。如果資料行值包含額外的 Null 結束字元，字串就會在第一個 Null 出現時被截斷。

DT_NTEXT：最大長度為 230 - 1 (1,073,741,823) 個字元的 Unicode 字元字串。

以下是摘錄 SQL Server 2008 線上說明，有關於「Excel 目的地」的說明：

正在儲存備忘 (ntext) 資料。在 Excel 資料行中成功儲存長於 255 個字元的字串之前，驅動程式必須能將目的地資料行的資料類型辨識為備忘，而不是字串。

如果目的地資料表已包含資料列，則驅動程式所取樣的前幾個資料列必須在備忘資料行中至少包含一個值長於 255 個字元的執行個體。

如果目的地資料表是在封裝設計期間或在執行階段建立，則 CREATE TABLE 陳述式必須使用 LONGTEXT (或其同義字之一) 做為備忘資料行的資料類型。
...

Integration Services 對應 Excel 資料類型的情況如下：
...
字串 – Unicode 字串，長度 255 (DT_WSTR)
備忘錄 – Unicode 文字資料流 (DT_NTEXT)
...

資料來源。Excel 活頁簿中資料的來源可以是工作表 (必須附加 $ 符號，例如 Sheet1$) 或已命名的範圍 (例如 MyRange)。

在 SQL 陳述式中，工作表的名稱必須加以分隔 (例如 [Sheet1$])，以避免 $ 符號造成的語法錯誤。

「查詢產生器」會自動加入這些分隔符號。
當您指定工作表或範圍時，驅動程式會讀取連續的資料格區塊，從工作表或範圍左上角的第一個非空白資料格開始。

因此，來源資料的資料列不可以空白，或標題或標頭資料列與資料列之間不可以有空白資料列。
...
遺漏值。Excel 驅動程式會在指定來源中讀取特定資料列數目 (依預設為 8 個資料列)，以猜測各資料行的資料類型。
...
截斷的文字。當驅動程式判斷出某個 Excel 資料行包含文字資料時，驅動程式將會根據其取樣的最長值來選取資料類型 (字串或備忘錄)。

如果驅動程式未在其取樣的資料列中發現任何長度超過 255 個字元的值，則會將該資料行視為 255 個字元字串資料行而非備忘錄資料行因此，長度超過 255 個字元的值可能會被截斷。

若要以不截斷的方式從備忘資料行匯入資料，您必須確保至少在其中一個取樣資料列中的備忘錄資料行包含長度超過 255 個字元的值，否則您就必須增加驅動程式取樣的資料列數目，使其包含這類資料列。

您可以提高 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel 登錄機碼下 TypeGuessRows 的值，以增加取樣的資料列數目。

由上，雖然使用 Excel 2007(*.xlsx、*.xlsb) 與 SSIS 2008 可以超過 65,536 列的限制。
但經過測試並與 Microsoft 確認後：
Excel 2007(*.xlsx、*.xlsb) 所使用的 Microsoft Office 12.0 Access 資料庫引擎 OLE DB 提供者，簡稱為：ACE Provider 可能有無法正確支援 DT_NTEXT 的問題，也就是無法支援超過 255 個字元的資料。這個問題在Excel 2010會修復. 目前的workaround就是使用 excel 2003了….

在此感謝 Vera 熱心的協助。

有關於 Excel 2007 附檔名的說明：
xlsx ：此為預設的 Office Excel 2007 XML 檔案格式。
xlsb ：Office Excel 2007 二進位檔案格式 (BIFF12)。

更新日期：2010/06/26

經過測試，在 Excel 2010、SQL Server 2008 R2 中文版本上，仍是有此問題。
「Microsoft Office 12.0 Access Database Engine OLE DB Provider」，簡稱為：ACE Provider 可能有無法正確支援 DT_NTEXT 的問題。

請參考下圖，仍看不到 Excel 2010 的標籤。

更新日期：2011/05/09使用環境：

英文版本
1. Windows Server 2008 R2 x64 位元
2. SQL Server 2008 R2 x64 位元
3. Office 2010 x64 位元

仍是有上述的問題。

參考資料：
Excel 中支援的檔案格式
http://office.microsoft.com/zh-tw/excel/HP100141031028.aspx

Excel 的規格及限制
http://office.microsoft.com/zh-tw/excel/HP100738491028.aspx

Excel 目的地
http://msdn.microsoft.com/zh-tw/library/ms137643.aspx

PRB： Jet 4.0LEDB 來源資料的傳送緩衝區溢位錯誤而失敗
http://support.microsoft.com/kb/281517

excel column is greater than 255 in ssis
http://venkattechnicalblog.blogspot.com/2008/09/excel-column-is-greater-than-255-in.html

無法匯入超過 65,536 列是文字檔到 Excel 97、 Excel 2000、 Excel 2002 和 Excel 2003
Text files that are larger than 65,536 rows cannot be imported to Excel 97, Excel 2000, Excel 2002 and Excel 2003
http://support.microsoft.com/kb/120596/zh-tw

2007 Office system 驅動程式：資料連線元件
http://www.microsoft.com/downloads/details.aspx?displaylang=zh-tw&FamilyID=7554f536-8c28-4598-9b72-ef94e038c891

SSIS 2005 與 Excel 2007 (*.xlsx , *.xlsb)
http://sharedderrick.blogspot.com/2008/01/ssis-excel-2007-xlsx-xlsb.html

SSIS 2005 與 Access 2007 (*.accdb)
http://sharedderrick.blogspot.com/2008/05/ssis-access-2007.html

訂閱：文章 (Atom)

搜尋本站文章