I want to export the results from a sproc to Excel. Thus, between the exec and SELECT statements I insert the following:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Temp\testing.xls;',
'
SELECT Field1, Field2, Field3
FROM [Sheet1$]
')
Which returns the following 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 a basic test, I should be able to run the following query, which returns the same error:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\testing.xls;',
'SELECT Name, Date FROM [Sheet1$]')
SELECT [Name], GETDATE() FROM msdb.dbo.sysjobs
GO
I'm using SQL Server 2008 and am not using SSRS. Any hints on how to get around this?
Thanks.
This is answered already in the comments by Barry, just following up and posting answer with supporting link.