I find some ways to import data from a SQL Server query to Excel like below but none of them seem to work
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from tblName
Error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
As indicated by the error message, SQL server will not be able to transfer the data to Excel using this method. One possible solution that does not require programming: Open Excel, click the Data tab, From Other Sources, SQL Server. A window will open prompting for Server name, provide the server name and instance name separated by a backslash. Example: localhost\sqlexpress (if you are running sql server express edition on your PC, otherwise whatever server you installed SQL Server on, along with the instance name). Authenticate by providing your user id and password as defined in SQL server or just clicking Windows authentication. You will then have the opportunity to select the table name. The data will then be transferred into an Excel table that you can refresh at will by right-clicking on it. Please, indicate if you want an alternate solution that may require programming.
If you have 32-bit Office and you don't want to uninstall it, try to install latest AccessDatabaseEngine from microsoft (64-bit). To install it without uninstalling 32-bit version please go to the download directory in comand line and write
AccessDatabaseEngine.exe /passive
Then in SSMS you can import your Excel with code:
FROM OPENROWSET
('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\path\sheet.xlsx', [Sheet1$])
You should have a look at Pinal Dave's solution. Also, possibly you have x64 machine so it won't work due to unavailability of driver. Check out this answer.
