Export Query to Excel using linked server

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

SQL Server 2000 Forums

Transact-SQL (2000)

Export Query to Excel using linked server

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2009-09-09 : 22:32:25

Hi,
From a SQL server 2000:
I am trying to export a sql query results to an Excel file located on another server that is different then the sql server. The Excel file exists in the location specified and has the columns names ok.

--Insert new data into the Excel file on the web server
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\servername\e$\inetpub\wwwroot\game\myexcelfile.xls;',
'SELECT * FROM [scores]') select * from LinkedServerName.DBname.tbl_Stats

The error I get is this.....

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:  The provider did not give any information about the error.].
Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.

Please Help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-10 : 02:35:29

You can find solution here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

infodemers
Posting Yak Master

183 Posts

Posted - 2009-09-10 : 07:30:14

Hi Madhivan,

I got my code from your link already and I cannot get it to work.
But in your topic I have found another way that worked and it is this one:

Exec Master..xp_cmdshell 'bcp "Select * from myTable" queryout "C:\testing.xls" -c'

Would you know how I can have the column name using this way,
because BCP does not return them?

Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-10 : 08:11:15

Exec Master..xp_cmdshell 'bcp "Select col1,col2,col3 from myTable" queryout "C:\testing.xls" -c'

Madhivanan

Failing to plan is Planning to fail

infodemers
Posting Yak Master

183 Posts

Posted - 2009-09-10 : 09:32:31

Hi Madhivanan,

It still does not return the columns name running your sample.
I get the same results as using this:
--> Exec Master..xp_cmdshell 'bcp "Select * from myTable" queryout "C:\testing.xls" -c'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-10 : 10:05:09

Did you try specifying the column names as I showed?

Madhivanan

Failing to plan is Planning to fail

infodemers
Posting Yak Master

183 Posts

Posted - 2009-09-10 : 10:08:25

Yes I did Madhivanan!
It should create an excel file with one row that contains the columns name and then the row with the data?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-10 : 10:15:39

quote:
Originally posted by infodemers

Yes I did Madhivanan!
It should create an excel file with one row that contains the columns name and then the row with the data?

Try method 5 from the link

Madhivanan

Failing to plan is Planning to fail

infodemers
Posting Yak Master

183 Posts

Posted - 2009-09-10 : 12:29:55

Hi Madhivanan,

I got it working like this:
Exec Master..xp_cmdshell 'bcp "Select ''col1'',''col2'',''col3'' union all select * from myTable" queryout "C:\testing.xls" -c'

Thanks for your help!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-11 : 02:53:19

quote:
Originally posted by infodemers

Hi Madhivanan,

I got it working like this:
Exec Master..xp_cmdshell 'bcp "Select ''col1'',''col2'',''col3'' union all select * from myTable" queryout "C:\testing.xls" -c'

Thanks for your help!

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources