java sql 查询 导出到excel_使用多个工作表和自定义标头将SQL查询导出到Excel

我知道，我知道......你也遇到了这个错误：

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.

您可以通过T-SQL在SSMS中执行此操作，请按照this example进行操作：

首先，您需要允许SSMS绕过错误：

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

EXEC sp_configure 'Ad Hoc Distributed Queries', 1

RECONFIGURE

然后，您可以通过以下方式将结果保存在精确的Excel选项卡中：

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;

Database=C:\Users\Zivko\Desktop\SQL Data.xlsx;','SELECT * FROM [Sheet1$]')

SELECT * FROM dbo.DimScenario

文件.XLSX必须已经存在，并且选项卡具有精确的名称 [Sheet1$]