none
MSSQL export 問題 RRS feed

  • 問題

  • 您好:

    我參考 https://stackoverflow.com/questions/46594225/export-xlsx-files-out-of-a-sql-server-database

    EXEC sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
    GO
    RECONFIGURE;
    GO
    
    USE [AdventureWorks2012];
    GO
    
    INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=m:\contact.xls;',
    'SELECT * FROM [Sheet1$]')
    SELECT top 5 LASTNAME,fIRSTnAME FROM [Person].[Person]
    GO
    

    想透過 MSQL 匯出 xls

    我已經有先安裝 2010轉發套件了

    但結果仍是

    訊息 7302,層級 16,狀態 1,行 15
    無法建立連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.Jet.OLEDB.4.0" 的執行個體。

    我是用SQL EXPRESS 2014 +WIN10 X64 +OFFICE 2016

    謝謝!

    2019年2月28日 下午 01:27

所有回覆

  • 你如果是 x64 的 SSMS ,無法使用 x86 的 oledb

    你需要安裝 x64 的 Access Runtime ,裡面會包含 x64 的 oledb / odbc Excel driver 。

    你的 Office 2016 若是 x64 ,表示已包含,若是 x86 ,請安裝 Acces 2013 Runtime x64 ,同一 Office 版本的 Access Runtime 不能共存。

    新版的 oledb 連線字串參考:

    https://www.connectionstrings.com/ace-oledb-12-0/


    不精確的問法,就會得到隨便猜的答案;自己都不肯花時間好好描述問題,又何必期望網友會認真回答?

    2019年2月28日 下午 03:11
  • 您好:

    謝謝您,想先釐清一下:

    Q1.我是用 2014SSMS X64 , OFFICE 2016 X64 ,WIN 10 X64

       而我 看新增移除程式內,有

      MS ACCess Database engine 2010  (昨天裝的)

      MS AS OLE DB Provider for SQL Server 2016  (這應該是 SQL EXPRRSS 的)

     所以我需要先移除 engine 2010,再 裝 Acces 2013 Runtime x64 ,或 Acces 2016 Runtime x64   ?

    Q2.這是在同一台機器, 若以後

         MS SQL SRVER在PC-SERVRR

         開發機器,在 PC-DEV

    那我 需要在PC-SERVRR  安裝  Acces 201x Runtime x64  即可嗎?

    主要是要讓他匯出 xlsx,且透過SQL AGENT 自定處理!

    Q3. 之後 我語法改為

    INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=m:\contact.xls;',
    'SELECT * FROM [Sheet1$]')
    SELECT top 5 LASTNAME,fIRSTnAME FROM [Person].[Person]
    GO

    ----

    'Microsoft.ACE.OLEDB.12.0'

    'Excel 12.0;

    這樣對嗎? 因為不知道 Acces 201x 或 OFFICE 2016 是對到 Microsoft.ACE.OLEDB.12.0  的 12 嗎?

    麻煩了,謝謝!

      

    2019年3月1日 上午 02:43
  • a1.

    你 Office 有 2016 x64 ,就會有 'Microsoft.ACE.OLEDB.12.0' x64 版本,所以只需要裝 Access runtime 2013 x86 ,讓你電腦有 'Microsoft.ACE.OLEDB.12.0' x86 版本,這樣的好處是連線字串不用考慮目前執行檔的位元版本。

    一般來說,微軟資料庫連接引擎 odbc/oledb 是有對應的,所以直接從系統管理工具檢查 32/64 位元的 odbc 安裝情形即可。要檢查 oledb 安裝情形可參考這篇:

    [oledb] SQL Server Compact 沒有出現在 OLEDB 提供者清單中

    所以 Access 2010 runtime 是多餘的。

    註:Access 2019 印象中跟 Access 2016 共用,所以用 2013 的

    a2.

    要看你執行匯出的程式是哪位元的版本,裝對應的位元版本,不知道的話,x86/x64 都裝。

    例如上面那篇連結中,有用到 SQL Server 提供的匯入匯出精靈不同位元版本測,就會有不同 oledb 掃描的結果。

    a3.

    前一篇回給你的連線字串連結有範例,你輸出 .xls 應該用 Excel 8.0,輸出  .xlsx 用 Excel 12.0 Xml ,其他格式參考前面一篇給你的連結。


    不精確的問法,就會得到隨便猜的答案;自己都不肯花時間好好描述問題,又何必期望網友會認真回答?

    2019年3月1日 上午 04:37
  • 您好:

    謝謝, 我安裝 Access2013 x64版

    但從MSSQL 中,看不到這一個

    用以以下語法

    INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0'
     'Data Source=m:\contact.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";'
    ,'SELECT * FROM [Sheet1$]')
    SELECT top 5 LASTNAME,fIRSTnAME FROM [Person].[Person]
    GO
    
    INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0'
    , 'Excel 12.0Xml;Database=m:\contact.xls;'
    ,'SELECT * FROM [Sheet1$]')
    SELECT top 5 LASTNAME,fIRSTnAME FROM [Person].[Person]
    GO
    
    
    
    INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0' 
    ,'Excel 12.0Xml;Database=m:\xx.xlsx;HDR=YES;IMEX=1'
    ,'SELECT * FROM [Sheet1$]')
    SELECT top 5 LASTNAME,fIRSTnAME FROM [Person].[Person]
    GO
    

    都出現

    訊息 7302,層級 16,狀態 1,行 27
    無法建立連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 的執行個體。

    很冒昧請教,這還有哪一個可能性!

    謝謝!

    2019年3月1日 上午 08:20
  • 你不是電腦已經有 Office 2016 x64 了?所以你 Access Runtime 2013 要裝 x86 才對,你裝 Access runtime 2013 x64 會影響你 Access 2016 吧?

    我記得這樣會跳警告訊息。

    Access Runtime 2013 要抓 x86 那個版本。

    https://www.microsoft.com/zh-TW/download/details.aspx?id=39358


    不精確的問法,就會得到隨便猜的答案;自己都不肯花時間好好描述問題,又何必期望網友會認真回答?

    2019年3月1日 上午 09:59
  •  您好:

    我移除 Access Runtime 2013 要抓 x64

    我重裝 Access Runtime 2013 要抓 x86

    結果貼圖一樣如上

    我先用以下的語法來測試

    --EX5. 使用 Microsoft.ACE.OLEDB.12.0,連接到 *.xls
    SELECT *
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0'
    ,'Excel 12.0;Database=M:\test_sample.xls;HDR=YES;IMEX=1'
    ,'SELECT * FROM [Sheet1$]'); 
    
    
    SELECT *
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0'
    ,'Excel 12.0;Database=M:\NPOI_test_sample_2010.xlsx;HDR=YES;IMEX=1'
    ,'SELECT * FROM [S1$]'); 
    GO

    但結果一樣是

    訊息 7302,層級 16,狀態 1,行 55
    無法建立連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 的執行個體。

    我安裝時,沒有跳警告視窗!

    我看DB 的連線,沒有ACCESS

    2019年3月1日 上午 11:14
  • 目前 SQL連線  類型

    2019年3月1日 上午 11:36
  • 你有看過這篇嗎?

    http://sharedderrick.blogspot.com/2011/08/openrowset-opendatasource-sql-server.html

    因為你的 SSMS 是 x64 ,應該對應到 x64 的 oledb ,你把 Access runtime 2013 x64 移除完你有修復你的 Office 2016 x64 嗎?

    2013 x64 跟 2016 x64 不能共存,所以當你安裝 2013 x64 會卸載 2016 x64 ,然後你又把 2013 x64 移除掉,不就沒有 2013 x64 的連線引擎嗎?

    你有分別檢查過你 x86 / x64 的 oledb 是否正常安裝嗎?

    另外前面說過,你用 .xls ,對應的是 Excel 8.0 。


    不精確的問法,就會得到隨便猜的答案;自己都不肯花時間好好描述問題,又何必期望網友會認真回答?

    2019年3月1日 上午 11:46
  • 你上面那張圖表示你目前的 oledb 版本是 15.0 ,所以連線字串是

    Microsoft.ACE.OLEDB.15.0

    請確認位元版本


    不精確的問法,就會得到隨便猜的答案;自己都不肯花時間好好描述問題,又何必期望網友會認真回答?

    2019年3月1日 下午 01:05
  • https://docs.microsoft.com/zh-tw/sql/integration-services/import-export-data/connect-to-an-access-data-source-sql-server-import-and-export-wizard?view=sql-server-2017


    不精確的問法,就會得到隨便猜的答案;自己都不肯花時間好好描述問題,又何必期望網友會認真回答?

    2019年3月1日 下午 01:06
  • 您好:

    1.以上哪邊,我有看過,才會另外 寫他的語法來測試

    2.另外。我也有測過

    SELECT *
    FROM OPENROWSET('Microsoft.ACE.OLEDB.15.0'
    ,'Excel 12.0;Database=M:\NPOI_test_sample_2010.xlsx;HDR=YES;IMEX=1'
    ,'SELECT * FROM [S1$]');
    GO

    一樣 無法建立連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.15.0" 的執行個體。

    15.0 依您提供的網址,應該是 office 2013 ?

    3.而  "你把 Access runtime 2013 x64 移除完你有修復你的 Office 2016 x64 嗎? "

    請問 您說的是安裝 Access runtime 2016 x64  ,來修復嗎?

    4.這樣,我是否該移除 Access runtime 2013 x86

    然後重新安裝 Access runtime 2016 x86 與x64 ?

    5.我是有測過

    Excel 12.0;Database=M:\NPOI_test_sample_2010.xlsx

    'Excel 8.0;Database=M:\test_sample.xls;HDR=YES;IMEX=1'

    都不行!

    6.若是出現

    訊息 7403,層級 16,狀態 1,行 49
    OLE DB 提供者 "Microsoft.ACE.OLEDB.13.0" 尚未註冊。

    是未安裝嗎?

    7.若client端 有office 2013 , 另一PC有office 2016,那應該安裝哪一套版本ACCESS?

    謝謝您!

    2019年3月1日 下午 02:04
  • 你先用匯出精靈不同位元版本測試,看看能不能正常匯出,網友們比較好判斷你哪個位元版本的 oledb 安裝正常。

    你把不同位元版本混在一起,根本不知道你現在系統正常狀況是甚麼。


    不精確的問法,就會得到隨便猜的答案;自己都不肯花時間好好描述問題,又何必期望網友會認真回答?

    2019年3月1日 下午 02:21
  • 您好:我實作如下再請麻煩指導

    是否要同時 再安裝  Access runtime 2016 x64 與 Access runtime 2016 x86?

    修復你的 Office 2016 x64 嗎? "  ==>請問 您說的是安裝 Access runtime 2016 x64  ,來修復嗎?

    謝謝!

    -------------

    匯出
    SQL Server Native Client 11.0    -->97-2003 ==>xls
    SQL Server Native Client 11.0    -->2007    ==>xls    但開啟會出現 檔案格式與副檔名不相符
                                                自己產生xls

    匯入
    XLS ==>97-2003 -->  SQL Server Native Client 11.0   ->OK
    XLS ==>2007    -->  SQL Server Native Client 11.0   ->OK
    XLSX==>2007    -->  SQL Server Native Client 11.0   ->OK

    2019年3月2日 上午 07:04
  • 我的錯,剛剛確認 SQL Server 匯入匯出精靈無法來看安裝了那些 oledb

    麻煩下載這個工具,分別跑 32 / 64 bits 列舉工具

    http://www.sqledit.com/odbc/oledb-provlist.html


    不精確的問法,就會得到隨便猜的答案;自己都不肯花時間好好描述問題,又何必期望網友會認真回答?

    2019年3月2日 下午 12:14
  • 您好:

    謝謝

    X86

    Microsoft OLE DB Provider for SQL Server
    Microsoft OLE DB Provider for Analysis Services 11.0
    MSDataShape
    SQL Server Native Client 11.0
    Microsoft Office 12.0 Access Database Engine OLE DB Provider
    Microsoft Office 15.0 Access Database Engine OLE DB Provider
    OLE DB Provider for Microsoft Directory Services
    SQL Server Native Client 11.0 Enumerator
    Microsoft OLE DB Provider for Search
    OLE DB Provider for SQL Server Integration Services
    Microsoft OLE DB Provider for ODBC Drivers
    Microsoft OLE DB Enumerator for ODBC Drivers
    Microsoft Jet 4.0 OLE DB Provider
    Microsoft OLE DB Enumerator for SQL Server
    Microsoft OLE DB Simple Provider
    Microsoft OLE DB Provider for Analysis Services 12.0
    Microsoft OLE DB Provider for Oracle
    Microsoft OLE DB Provider for Analysis Services 13.0
    

    X64

    Microsoft OLE DB Provider for SQL Server
    MSDataShape
    SQL Server Native Client 11.0
    OLE DB Provider for Microsoft Directory Services
    SQL Server Native Client 11.0 Enumerator
    Microsoft OLE DB Provider for Search
    Microsoft OLE DB Provider for ODBC Drivers
    Microsoft OLE DB Enumerator for ODBC Drivers
    Microsoft OLE DB Enumerator for SQL Server
    Microsoft OLE DB Simple Provider
    Microsoft OLE DB Provider for Analysis Services 12.0
    Microsoft OLE DB Provider for Analysis Services 13.0
    

    2019年3月3日 上午 02:07
  • 從列舉內容來看,只有 x86 有安裝 Access Runtime ,x64 沒有任何 Access Runtime 。

    也可以看一下,系統管理工具 odbc 64位元 ,應該也沒有 Microsoft Excel Driver(*.xls, *.xlsx, *.xlsm, *.xlsb)

    Access runtime 裡面包含 oledb/odbc driver ,兩邊一起有,一起沒有。

    所以你現在先確認你的 Office 2016 到底是 x86 還是 x64 ,你如果使用雙位元版本安裝時,預設是安裝 x86 版,只有特別選擇 x64 才會安裝 x64 版本。

    若 Office 2016 為 x86 ,請安裝 Access runtime 2013 x64

    若 Office 2016 為 x64 ,請修復安裝 Office 2016 ,確保 Access 2016 x64 正確安裝

    安裝完後再重新列舉兩個位元版本的 oledb driver 。


    不精確的問法,就會得到隨便猜的答案;自己都不肯花時間好好描述問題,又何必期望網友會認真回答?


    • 已編輯 心冷熱情熄 2019年3月3日 上午 04:03 位元版本打錯
    2019年3月3日 上午 04:02
  • 您好:

    我應該當初是用雙語言版 安裝的,他預設裝X86

    我看 ODBC 以下2內容

    我要安裝 ACCESS 2013 X64,但 出現以下錯誤訊息

    這樣, 是要移除Runtime 2013 X86 , 還是要移除 office 2016 X86 ?


    還是 移除 Runtime 2013 X86,裝 Runtime 2016 X86 與X64?
    2019年3月3日 上午 05:45
  • 看 系統管理工具的 odbc 時,要點 [驅動程式] ,三個資料來源名稱是不同類型的 .dsn ,沒在用 dsn 可以不管資料來源名稱那三頁。

    你 Office 2016 如果是 x86 ,就安裝 Access runtime 2013 x64 ,移除整套 office 2016 x86 不如移除 access runtime 2013 x86 比較方便。


    不精確的問法,就會得到隨便猜的答案;自己都不肯花時間好好描述問題,又何必期望網友會認真回答?

    2019年3月3日 上午 07:12
  • 您好:

    我移除RUN time 2013x86 ,安裝runtime 2013 x64

    結果如下

    參考範例:

    --EX6. 使用 Microsoft.ACE.OLEDB.12.0,連接到 *.xlsx
    SELECT *
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0'
    ,'Excel 12.0;Database=M:\NPOI_test_sample_2010.xlsx;HDR=YES;IMEX=1'
    ,'SELECT * FROM [S1$]'); 
    GO

    一樣

    訊息 7302,層級 16,狀態 1,行 79
    無法建立連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 的執行個體。

    我改 15.0,也是一樣的錯誤。

    2019年3月3日 上午 08:22
  • 你 odbc (32位元) 反白的那個是 Win10 自帶的 Microsoft.Jet.OLEDB.4.0 ,對應你 prov_list 內的唯一一個 access / excel 共用的 oledb driver 。

    我自己電腦裡面 driver 很亂,沒辦法做安裝測試,但是因為你移除 Access runtime 2013 x86 裝 x64 後,x86 就變成沒有新版 driver ,有可能是 Access 2013/2016 是共用 driver ,而非我印象中 Access 2016/2019 共用 driver 。

    你可能要試試 Access 2010 runtime

    https://www.microsoft.com/zh-tw/download/details.aspx?id=10910

    你 SSMS 伺服器帶 IP ,你跑 SSMS 跟你 MS SQL Server 是同一台嗎?因為 T-SQL 是在 MS SQL Server 那台跑,如果不同台的話,你等於在 client 端裝 oledb ,server 卻沒裝。


    不精確的問法,就會得到隨便猜的答案;自己都不肯花時間好好描述問題,又何必期望網友會認真回答?

    2019年3月3日 下午 01:49
  • 您好:

    我之後再找了一些方式,

    要在加

    EXEC master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1
    GO
    EXEC master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1
    GO
    

    這樣, select  與 insert 就可以用了

    但update 與 delete 卻不能用

    UPDATE OpenDataSource( 'Microsoft.ACE.OLEDB.12.0'
        ,'Data Source="F:\Q\0002.xlsx";User ID=Admin;Password=;Extended properties="Excel 8.0;HRD=YES"')...S1$ 
    SET [DeptName]='人事部X'
    WHERE ID = '1'
    
    
    連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 傳回訊息 "UPDATE 陳述式的語法錯誤。"。
    訊息 7321,層級 16,狀態 2,行 60
    準備查詢 "UPDATE S1$ set `DeptName` = '人事部X'  WHERE `ID`=(1.000000000000000e+000)",以針對連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 執行時,發生錯誤。
    


    DELETE FROM OPENDATASOURCE( 
            'Microsoft.ACE.OLEDB.12.0', 
            'Data Source=F:\Q\0002.xlsx;Extended properties=Excel 8.0')...S1$
    連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 傳回訊息 "FROM 子句中的語法錯誤。"。
    訊息 7321,層級 16,狀態 2,行 51
    準備查詢 "DELETE FROM S1$ ",以針對連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 執行時,發生錯誤
    

    請問這有解嗎? 資料不清掉(DELETE),則資料會一直APPEND下去。

    此外,我目前都在同一台機器測

    公司的 SERVER與 CLIEN分開,我應該只需安裝SERVER吧?

    麻煩您了,謝謝!

    2019年3月4日 下午 01:25
  • 我很少在 T-SQL 內把 Excel 當資料庫存,只知道個大概。

    前面

    https://www.connectionstrings.com/ace-oledb-12-0/

    處理 .xlsx 附檔名時,Excel 版本要標記為

    Extended Properties="Excel 12.0 Xml;

    你有試過嗎?

    你的 T-SQL 如果是在 Server 上跑,則應在 Server 上安裝 oledb driver 。


    不精確的問法,就會得到隨便猜的答案;自己都不肯花時間好好描述問題,又何必期望網友會認真回答?

    2019年3月4日 下午 03:48
  • 您好:

    謝謝,另外

    我在一台SERVER上安裝 

    已同時安裝 AccessDatabaseEngine.exe AccessDatabaseEngine_X64.exe  (2010)

    然後想利用 SQL SERVER 匯出匯入精靈,來匯入XLSX  (XLS可以匯)

    「無法完成作業。

    'Microsoft.ACE.OLEDB.12.0' 提供者並未登錄於本機電腦上。 (System.Data)

    網路上找到的訊息:

    解決方式:在執行精靈的那台電腦安裝 "2007 Office system 驅動程式:資料連線元件",即可。
    
    檔案下載:
    .2007 Office system 驅動程式:資料連線元件
    .Microsoft Access Database Engine 2010 可轉散發套件
    
    註1:網路上說安裝"Microsoft Access Database Engine 2010 可轉散發套件",可解決,但是我安裝後仍然有問題(不知道跟什麼有關)
    註2:下載時需注意,因為"2007 Office system 驅動程式:資料連線元件" 和 "Microsoft Access Database Engine 2010 可轉散發套件",(如果下載32位元版本)下載後的檔名都一樣
    
    

    但我找望路上,都已沒有2007 Office system 驅動程式:資料連線元件

    請問,這還有解嗎?








    2019年3月5日 下午 01:22
  • 您好:

    有試過

    UPDATE OpenDataSource( 'Microsoft.ACE.OLEDB.12.0'
        ,'Data Source="M:\X1.xlsx";User ID=Admin;Password=;Extended properties="Excel 12.0 Xml;HDR=YES;IMEX=1"')...S1$ 
    SET DeptName='人事部X'
    WHERE ID = '1'
    

    仍不行

    連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 傳回訊息 "UPDATE 陳述式的語法錯誤。"。
    訊息 7321,層級 16,狀態 2,行 116
    準備查詢 "UPDATE S1$ set `DeptName` = '人事部X'  WHERE `ID`=(1.000000000000000e+000)",以針對連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 執行時,發生錯誤。
    2019年3月6日 下午 01:26