Home Forums SQL Server 2005 Development Error,while inserting data into Excel from sql server 2005!!!!!! RE: Error,while inserting data into Excel from sql server 2005!!!!!!

  • November 28, 2008 at 7:14 am

    #904771

    Here's an OPENROWSET Excel insert which has been working for a while:

    -- INSERT the results into the newly-created spreadsheet

    INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;DATABASE=\\ReportingServer\D$\EDIInvoices.xls',

    'Select * from EDIInvoices')

    SELECT * from #EDIInvoicesPivot

    Remember, this statement is running on the SQL server, not on your desktop, so the location of the Excel file is relative to the server. In the example above, the Excel file is on another (reporting) server.

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden