sql server : export table data to excel

Question

sql server : export table data to excel

rams.prsk

Ten Centuries

Points: 1241
More actions
March 27, 2012 at 2:48 am

#266190

Hi All,
Iam trying to export sql server table data to excel in the following manner :
Enable Ad Hoc Distributed Queries
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
After enabling it, we can proceed further to write the query for exporting the SQL Serer Data.
Export into Excel Query:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\test.xls;',
'SELECT FirstName, LastName FROM [Sheet1$]')
SELECT FirstName,LastName FROM Users
GO
But iam gettying the below error. kindly help me on the same
Msg 7308, Level 16, State 1, Line 1
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.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

Run the query from Excel to extract the data, rather than pushing it into xls.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

rams.prsk Ten Centuries Points: 1241 More actions · Answer 2

but my requirement is to push the data into excel. Kindly help me on this.

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 3

I assume you run sql 2008 and Excel 97?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Thinknook SSCrazy Points: 2531 More actions · Answer 4

My recommendation would be to do this using SSIS (or the Import/Export Wizard). you can easily output the result of a query to EXCEL through SSIS, save the SSIS package to be used later, and provide an extensible platform ontop of which you can customize your output EXCEL file (if you needed to later on down the line).

/>L

-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].

I believe in Codd
... and Thinknook is my Chamber of Understanding

rams.prsk Ten Centuries Points: 1241 More actions · Answer 5

rams.prsk

Ten Centuries

Points: 1241

March 27, 2012 at 3:04 am

#1464802

i run on sql 2008 and excel 2007

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 6

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\test.xls;',

'SELECT FirstName, LastName FROM [Sheet1$]')

SELECT FirstName,LastName FROM Users

GO

Your reference to 'Excel 8.0' indicates Excel 1997, not 2007, which is 12.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

rams.prsk Ten Centuries Points: 1241 More actions · Answer 7

i tried even using excel 12.0 in the query but ending up with the same issue 🙁

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 8

Which platforms are your SQL and Excel, 32 or 64 bit or mixture?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

rams.prsk Ten Centuries Points: 1241 More actions · Answer 9

rams.prsk

Ten Centuries

Points: 1241

March 27, 2012 at 3:29 am

#1464811

sql server 2008 is 64 bit

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 10

And the excel ?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

rams.prsk Ten Centuries Points: 1241 More actions · Answer 11

rams.prsk

Ten Centuries

Points: 1241

March 27, 2012 at 3:43 am

#1464820

excel is mixture