SQL SERVER資料匯入到EXCEL

 1、在SQL SERVER裡往Excel插入資料

 2、將SQL SERVER中查詢到的資料導成一個Excel檔案

 

  

1、在SQL SERVER裡往Excel插入資料

INSERT INTO OPENDATASOURCE('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;DATABASE=c:\book1.xls' )...[Sheet1$] SELECT [BlogID],[Title]   FROM   [庫名].[dbo].[Blog]

可能會遇到的問題：

1、"SQL Server 阻止了對組件 'Ad Hoc Distributed Queries' 的 STATEMENT 'OpenRowset/OpenDatasource' 的訪問，因為此組件已作為此伺服器安全配置的一部分而被關閉。系統管理員可以通過使用 sp_configure 啟用 'Ad Hoc Distributed Queries'。有關啟用 'Ad Hoc Distributed Queries' 的詳細資料，請參閱 SQL Server 聯機叢書中的 "介面區配置器"。"

解決：

--啟用Ad Hoc Distributed Queries：exec sp_configure 'show advanced options',1reconfigureexec sp_configure 'Ad Hoc Distributed Queries',1reconfigure    --使用完成後，關閉Ad Hoc Distributed Queries：exec sp_configure 'Ad Hoc Distributed Queries',0reconfigureexec sp_configure 'show advanced options',0reconfigure

 

2、"列名或所提供值的數目與表定義不匹配。"

解決：在book1.xls的Sheet1中必須有和excel表相對應的欄位，否則會出錯。可在插入前查詢EXCEL表結構。

/*查看 xls表結構*/ SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',  'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[SHEET1$]

 

3、"訊息 7399，層級 16，狀態 1，第 2 行
連結的伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.Jet.OLEDB.4.0" 報錯。提供者未給出有關錯誤的任何資訊。
訊息 7303，層級 16，狀態 1，第 2 行
無法初始化連結的伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.Jet.OLEDB.4.0" 的資料來源對象。"

解決：確保已經該xls檔案已經關閉。

 

 

2、將SQL SERVER中查詢到的資料導成一個Excel檔案

 EXEC master..xp_cmdshell     'bcp "SELECT BlogID ,Title FROM [庫名].[dbo].[Blog]"  queryout   c:\temp1.xls -c -q -S"6350BFDFB9DD413" -U"sa" -P"sa_123"'/*output-----------------------------------------------------------------------------------------------------------NULL開始複製...NULL已複製 8 行。網路資料包大小(位元組): 4096總時鐘時間(毫秒)     : 1      平均值: (每秒 8000.00 行。)NULL(7 行受影響)*/

可能會遇到的問題：

1、"SQL Server 阻止了對組件 'xp_cmdshell' 的 過程 'sys.xp_cmdshell' 的訪問，因為此組件已作為此伺服器安全配置的一部分而被關閉。系統管理員可以通過使用 sp_configure 啟用 'xp_cmdshell'。有關啟用 'xp_cmdshell' 的詳細資料，請參閱 SQL Server 聯機叢書中的 "介面區配置器"。"

解決：

/*允許*/EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;/*關閉*/EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 0;RECONFIGURE;

 

2、"複製方向必須是 'in'、'out' 或 'format'。
　　用法: bcp {dbtable | query} {in | out | queryout | format} 資料檔案"

解決：都是雙引號，還有所有代碼都放在一行，不允許斷行符號或者換行

 

 

引用自：

SQL SERVER 與ACCESS、EXCEL的資料轉換  http://www.microsoft.com/china/community/column/31.mspx

SQL Server 阻止了對組件 ''Ad Hoc Distributed Queries'' 的 STATEMENT''OpenRowset/OpenDatasource'' 的訪問

匯入EXCEL資料到SQL  http://topic.csdn.net/u/20091213/19/72280C51-B7D8-43E0-9964-A7142604F0B9.html

bcp 執行老是 提示 複製方向必須是 'in'、'out' 或 'format'。那位高手指教一下