Post reply

Export To Excel

  • 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

  • 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

  • March 30, 2007 at 4:39 am

    #697829

    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

  • 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

  • March 31, 2007 at 11:19 pm

    #697986

    By default in SQL express edition remote connections are disabled...

    Enable remote connection using SARFACE AREA CONFIGURATION ....

     

    MohammedU
    Microsoft SQL Server MVP

  • April 2, 2007 at 7:38 am

    #698081

    Excellent article on T-SQL/Excel:

    http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/

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

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