export data to excel - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

export data to excel

Author

Topic

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2012-03-27 : 04:40:34

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.

kindly help me on the same.
Thanks,
Ram

rams

rajarajan
Starting Member

48 Posts

Posted - 2012-03-27 : 05:32:33

Can check this
http://support.microsoft.com/kb/306397/EN-US/

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2012-03-27 : 05:44:35

hi raj,

thanks for the reply, but no luck yaar

rams

rajarajan
Starting Member

48 Posts

Posted - 2012-03-27 : 05:47:27

wat is the error u get?
did u try the link

rajarajan
Starting Member

48 Posts

Posted - 2012-03-27 : 09:33:48

Can u check u have permission to do that, basically OPENROWSET is off

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2012-03-28 : 01:54:00

hi rajan,

sorry for late reply. yes i have permissions for openrowset bot i think jet provider does not support x64 sql server machines

thanks

rams

rajarajan
Starting Member

48 Posts

Posted - 2012-03-28 : 02:43:43

did u try with link server . once u can able to create linkserver it will work

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2012-03-28 : 03:11:13

hi rajan,

iam a newbie to this field. could you just help me with the query using link server. that would be so helpful for me if you could do so.

Thanks

rams

rajarajan
Starting Member

48 Posts

Posted - 2012-03-28 : 04:50:21

Step 1:
http://msdn.microsoft.com/en-us/library/ff772782.aspx
Create the link server using the above link

Step 2

After creating(link server ) look the link 2

http://support.microsoft.com/kb/306397/EN-US/

Did u know SSIS package ?

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2012-03-28 : 05:43:39

hi raj,

thanks for the reply. iam getting the new error like :

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

rams

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources