locked
Script to export data frmo SQL Server 2014 to xls file RRS feed

  • Question

  • Hi All:

    I want to export data form a table in SQl Server 2014 to an xls file.

    Currently I have a working script for SQL Server 2005 in the below lines, but the same script doesnot work for SQL Server 2014.

    set @outFileName='D:\Folder1\File1.xls'
    SET @QueryString = 'INSERT INTO OPENROWSET( ''Microsoft.Jet.OLEDB.4.0'', ' + @outFileName +                             
     ',''select * ' + 'from [Sheet1$A1:IV65000]'') select * from Table1 ' --order by CreatedDate'                             

    EXEC ( @QueryString )                  .

    I have checked on the web but didnot get a good answer. Please share with me if any one has a working script for SQL Server 2014.

    Thanks... Happy Diwali.. :)

    Friday, October 24, 2014 4:26 PM

All replies

  • but the same script doesnot work for SQL Server 2014.


    "Does not work" means what exactly? Are you getting an error message (which one?) or what happens?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, October 24, 2014 4:44 PM
  • I get the below error message

    Msg 7302, Level 16, State 1, Line 15
    Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

    Friday, October 24, 2014 5:24 PM
  • So did you install the provider?

    Well, it is not likely that it will work out anyway. I believe that Jet.OLEDB is 32-bit only. And, hopefully, you are using 64-bit SQL 2014.

    The newer ACE provider exists in 64-bit. You can get it here:
    http://www.microsoft.com/en-us/download/details.aspx?id=13255


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, October 24, 2014 10:13 PM
  • Ok, I have downloaded the exe from the above link.

    As I have to install this on a production environment, I want to be sure of certain things ...

    Say I have installed the Provider, what would be the syntax for exporting the data from sql server 2014 to xls.

    It would certainly not be the same ... i feel.. please let me know.

    Thanks

    Sunday, October 26, 2014 9:00 AM
  • Ok, I have downloaded the exe from the above link.

    As I have to install this on a production environment, I want to be sure of certain things ...

    Say I have installed the Provider, what would be the syntax for exporting the data from sql server 2014 to xls.

    It would certainly not be the same ... i feel.. please let me know.

    Thanks

    Yes

    provider connection string would be different

    like

    INSERT INTO OPENROWSET("Microsoft.ACE.OLEDB.12.0", "Excel 12.0;Database=<full file name>.xlsx;HDR=YES", "SELECT * FROM [SheetName$]") 
    SELECT ..
    FROM table


    Please Mark This As Answer if it solved your issue
    Please Mark This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, October 26, 2014 9:53 AM
  • As I have to install this on a production environment, I want to be sure of certain things ...

    Say I have installed the Provider, what would be the syntax for exporting the data from sql server 2014 to xls.

    I would recommend that you first install the provider in a test environment and try to get things working there first, so that you have syntax and configuration correct.

    It may still fail in production due to permissions etc, but then you would know what ghost you are chasing.

    Or start looking for a less invasing solution. For instance, using the Export/Import Wizard in SSMS, or write an SSIS package. (No, I don't know SSIS myself, but you need to start somewhere.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Charlie Liao Tuesday, November 4, 2014 2:37 AM
    Sunday, October 26, 2014 10:27 AM