March 27, 2012 at 2:48 am
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.
March 27, 2012 at 2:55 am
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
March 27, 2012 at 2:57 am
but my requirement is to push the data into excel. Kindly help me on this.
March 27, 2012 at 3:03 am
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
March 27, 2012 at 3:04 am
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
March 27, 2012 at 3:04 am
i run on sql 2008 and excel 2007
March 27, 2012 at 3:08 am
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
March 27, 2012 at 3:15 am
i tried even using excel 12.0 in the query but ending up with the same issue 🙁
March 27, 2012 at 3:24 am
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
March 27, 2012 at 3:29 am
sql server 2008 is 64 bit
March 27, 2012 at 3:32 am
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
March 27, 2012 at 3:43 am
excel is mixture
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply