======Microsoft - SQL Server - Snippets - Write to Excel file======

// Tested with the Microsoft Access Database Engine 2016 Redistributable with SQL Server 2016 on Windows Server 2016. Have not yet tested this from a SQL Server Agent job, which was the intention. //

Prerequisites:
  - The Excel file should already exist.
  - The Excel file should already contain the exact number of required columns.

Microsoft Access Database Engine Redistributables:
  * [[https://www.microsoft.com/en-us/download/details.aspx?id=54920|Microsoft Download Center - Microsoft Access Database Engine 2016 Redistributable]]
  * [[https://www.microsoft.com/en-us/download/details.aspx?id=13255|Microsoft Download Center - Microsoft Access Database Engine 2010 Redistributable]]
  * [[https://www.microsoft.com/en-us/download/details.aspx?id=23734|Microsoft Download Center -  2007 Office System Driver: Data Connectivity Components]]

Steps:
  - Install the Microsoft Access Database Engine 2016 Redistributable.
  - Enabling Ad Hoc Distributed Queries is required:<code tsql>sp_configure 'show advanced options', 1;  
RECONFIGURE;
GO 
sp_configure 'Ad Hoc Distributed Queries', 1;  
RECONFIGURE;  
GO</code>
  - Use the following query:<code tsql>INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=NO;IMEX=1;Database=C:\Map1.xlsx;','SELECT * FROM [Sheet1$]')
SELECT * FROM sysobjects</code>
    - HDR=NO: the first row does not contain column headers.
    - IMEX=1: [[https://www.connectionstrings.com/excel/|"a safer way to retrieve data for mixed data columns"]]
    - Both HDR and IMEX are not mandatory parameters.



Sources:
  * [[https://www.connectionstrings.com/ace-oledb-12-0/|ConnectionStrings.com - Microsoft ACE OLEDB 12.0 connection strings]]
  * [[https://www.connectionstrings.com/excel/|ConnectionStrings.com - Excel connection strings]]
  * [[https://stackoverflow.com/questions/11341535/what-is-the-default-value-of-imex-in-oledb|Stack Overflow - What is the default value of IMEX in OLEDB?]]
  * [[https://www.etl-tools.com/imex1.html|ETL-Tools.com - IMEX=1]]
  * [[https://stackoverflow.com/questions/10102149/what-is-imex-in-the-oledb-connection-string|Stack Overflow - What is IMEX in the OLEDB connection string?]]
  * [[https://docs.microsoft.com/en-us/sql/ado/guide/appendixes/microsoft-ole-db-provider-for-microsoft-jet|Microsoft Docs - Microsoft OLE DB Provider for Microsoft Jet Overview]]
  * [[https://stackoverflow.com/questions/2735415/specification-of-extended-properties-in-oledb-connection-string|Stack Overflow - Specification of Extended Properties in OleDb connection string?]]
  * [[https://docs.microsoft.com/en-us/previous-versions/office/developer/office2000/aa140022(v=office.10)|Microsoft Docs - ADO Provider Properties and Settings]]
  * [[https://social.msdn.microsoft.com/Forums/en-US/cf853e35-ee3f-48d3-89a7-1037f3b04c8f/is-there-documentation-for-microsoftaceoledb120|Microsoft Developer Network - Is there documentation for Microsoft.ACE.OLEDB.12.0?]]
  * [[https://visakhm.blogspot.com/2013/12/how-to-solve-microsoftaceoledb120-error.html|VM Blogs - How to solve Microsoft.ACE.OLEDB.12.0 error "Unspecified error"]]
  * [[https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6f663ab8-c75d-419b-a4bf-bb6c0f5b84c4/ole-db-provider-quotmicrosoftaceoledb120quot-for-linked-server-quotnullquot-returned|Microsoft SQL Server Forums - OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error"]]
  * [[https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2915b2a5-2486-419d-b545-653b80e8ec2c/quering-excel|Microsoft SQL Server Forums - Quering Excel]]
  * [[https://stackoverflow.com/questions/26267224/the-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null|Stack Overflow - The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”]]
  * [[https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/ad-hoc-distributed-queries-server-configuration-option|Microsoft Docs - ad hoc distributed queries Server Configuration Option]]
  * [[https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql|Microsoft Docs - OPENROWSET (Transact-SQL)]]
  * [[https://blogs.msdn.microsoft.com/dataaccesstechnologies/2017/10/18/unexpected-error-from-external-database-driver-1-microsoft-jet-database-engine-after-applying-october-security-updates/|SQL BI / Data Access Technologies  - “Unexpected error from external database driver (1). (Microsoft JET Database Engine)” after applying October security updates.]]
  * [[https://stackoverflow.com/questions/39244418/accessing-microsoft-ace-oledb-12-0-from-sql-job-agent|Stack Overflow - Accessing 'Microsoft.ACE.OLEDB.12.0' from SQL Job Agent]]
  * [[https://granadacoder.wordpress.com/2010/12/07/bug-in-documentation-microsoft-access-database-engine-2010-redistributable/|GranadaCoder C# Tidbits - Bug in Documentation : Microsoft Access Database Engine 2010 Redistributable]]
  * [[https://blog.sqlauthority.com/2015/06/24/sql-server-fix-export-error-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine/|SQL Authority - SQL SERVER – FIX – Export Error – ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine]]
  * [[https://www.excel-sql-server.com/excel-import-to-sql-server-using-linked-servers.htm|www.Excel-SQL-Server.com - Excel Import to SQL Server using Linked Servers]]
  * [[https://stackoverflow.com/questions/22032222/ole-db-provider-microsoft-jet-oledb-4-0-cannot-be-used-for-distributed-queries|Stack Overflow - OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries]]
  * [[https://stackoverflow.com/questions/38148497/how-to-extract-the-query-result-to-directly-to-xlsx-file|Stack Overflow - How to Extract the Query result to directly to .XLSx File]]