Export To Excel

Question

Post reply

Export To Excel

Steve Fouracre

Valued Member

Points: 59
More actions
March 29, 2007 at 5:10 pm

#176901

I'm trying to use OPENROWSET to export a table to excel, but it is going horribly wrong.

I've created a stored procedure as shown below

CREATE Procedure [ExportToExcelSheet](@fileName varchar(255))

as

Declare @sql varchar(1000)

Set @sql ='

insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',

''Excel 8.0;Database='+@fileName+';HDR=YES'',

''SELECT * FROM sheet1$'')

select * from tblcustomer'

Exec (@sql)

I then execute:

exec ExportToExcelSheet 'c:\testing.csv'

The following returns :-

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.

I'm not sure what is happening here or how to test what is going on. It is obviously something wrong with 'Microsoft.Jet.OLEDB.4.0'

I'd much appreciate any help on this

Regards

Viewing 6 posts - 1 through 5 (of 5 total)

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

MohammedU SSCoach Points: 19644 More actions · Answer 1

MohammedU

SSCoach

Points: 19644

March 29, 2007 at 10:01 pm

#697800

Do you/sql have read/write access to the drive...

http://sqljunkies.com/WebLog/madhivanan/archive/2005/07/27/16233.aspx

MohammedU
Microsoft SQL Server MVP

Steve Fouracre Valued Member Points: 59 More actions · Answer 2

Thank you Mohammed for your comments and the link I will read all what you have sent in a second, but I thought I would give you a little more information

The error message I received was on my local computer which I have full read write access to the C drive, but when I try the same execution on the web server I get a different error message. This message is shown below

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

The Sql Server is the free Express Edition. I will be upgrading this to the Workgroup edition which may sort this problem.

I will now read all the information you have sent

Regards

Steve

Steve Fouracre Valued Member Points: 59 More actions · Answer 3

Steve Fouracre

Valued Member

Points: 59

March 31, 2007 at 6:04 am

#697966

I'm still playing around with this and is still not working. It is something to do with the OLE driver. I've checked all the registry settings etc and everything looks to be ok. I will do some more searching around on the net and if I find an answer I will post it for everyone.

Regards

Steve