I need help exporting to excel 2k7 with openrowset

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

I need help exporting to excel 2k7 with openrowset

Author

Topic

Cwm
Starting Member

39 Posts

Posted - 2008-11-14 : 08:59:03

Hi All,
I am having issues again in exporting data from sql2005 to an excel work book in excel 2007. I am using this code then the error is below. I have Ad-Hoc query's enabled and in the excelworkbook I have the the column headers in the excel workbook as CommodityID, Commodity.

ALTER PROCEDURE Send$To$Excel

AS
insert into openrowset('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=C:\Test\TestBook.xlsx', 'SELECT CommodityID, Commodity From [Sheet1$]')
select CommodityID, Commodity from [Commodity]
RETURN

here is the error:
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
The requested operation could not be performed because OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not support the required transaction interface.

any idea on what the issue is? I am stumped.

Thanks

Chris

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-14 : 09:10:43

Repeated Post: Yours early one was

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114296

Cwm
Starting Member

39 Posts

Posted - 2008-11-14 : 09:19:55

Yes I remember that one. I have recently figured that one out. This is different, instead of exporting to an excel 97 - 2003 worksheet, i am trying to export to excel 2007. As for ODBC I have read that it is slow to use.
I would rather keep it in an stored procedure...This is why I need to do this....I am writing an asp web app for work backending with sqlserver, now the company wants me to export the data to an excel file. So now that i go it figured out with the excel 97-2003, i need to figure it out with excel 2007, for the most part i am using excel 2007 on my machine. The difference with excel 97-2003 is the extension 93-2003 is .xls and 2007 is xlsx. So i have to use Microsoft.ace.oledb for 2k7 instead of microsoft.jet.oledb
This post may be considered a repeated post, but its entirely different : )

Thanks

Chris

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-14 : 09:26:33

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-14 : 09:28:12

More specifically http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=17#445531

Madhivanan

Failing to plan is Planning to fail

Cwm
Starting Member

39 Posts

Posted - 2008-11-14 : 09:42:10

Thanks Madhivanan,
I changes my sp to this..
insert into openrowset('Microsoft.ace.OLEDB.12.0','Excel 12.0;Database=C:\Test\TestBook.xlsx; Extended Properties=Excel 12.0 XML; HDR=YES', 'SELECT CommodityID, Commodity From [Sheet1$]')
select CommodityID, Commodity from [Commodity]
I still get the same error as i previously stated.
When i changed the extension from xlsx to xls, the sp wouldn't save. Then i tried removing the Excel 12.0, i get an error when i try to save the s saying that "could not find installable isam".

So I am still at a loss on how to get this to work with excel 2007. excel 97 - 2003 was o.k to do but this excel 2007 is kind of a pain. I even tried using jet.4.0 but that didn't work either.

Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources