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.