none
Auto run a query and save to Excel file

    Question

  • I have read a lot of the previous posts on this topic but seem to be struggling slightly..

    I am looking to have some queries I have already set up run automatically a few times a day (around three times a day) and save themselves to an Excel file.

    I am using SQL server 2008 but testing things using SQL server 2012 express (I know this is limited on functionality).. as the 2008 version on the server is primarily meant to be left alone until I have fully functioning queries. 

    Thanks for any help that can be provided. 

    Friday, August 03, 2012 2:54 PM

Answers

  • Hello,

    Do you know Rachel that you can create an ODBC DSN to you database and run queries/stored procedures from Excel, and make Excel to run those queries every x minutes or when you open an Excel document?

    First create the ODBC DSN. After that use Data tab on Excel -> From Other sources -> From Microsoft Query to run the any query/stored procedure.

    Hope this helps.

    Regards,
    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by SQLR12345 Friday, August 03, 2012 3:51 PM
    Friday, August 03, 2012 3:41 PM
    Moderator

All replies

  • Hello,

    Do you know Rachel that you can create an ODBC DSN to you database and run queries/stored procedures from Excel, and make Excel to run those queries every x minutes or when you open an Excel document?

    First create the ODBC DSN. After that use Data tab on Excel -> From Other sources -> From Microsoft Query to run the any query/stored procedure.

    Hope this helps.

    Regards,
    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by SQLR12345 Friday, August 03, 2012 3:51 PM
    Friday, August 03, 2012 3:41 PM
    Moderator
  • Thanks, that's great
    Friday, August 03, 2012 3:51 PM
  • OK, that worked great on my machine but I wish to use this primarily on the server which does not have Excel and it is unlikely we can get a copy for the server. Is there any way of auto running the query and just having it save as a csv file, which will be saved to a shared drive which I can then open on my machine in Excel?

    Thanks

    Monday, August 06, 2012 1:13 PM
  • Hello,

    Please see the following article:

    http://www.mssqltips.com/sqlservertip/1202/export-data-from-sql-server-to-excel/  

    Hope this helps.

    Regards,
    Alberto Morillo
    SQLCoffee.com

    Monday, August 06, 2012 2:38 PM
    Moderator
  • Thankyou, most helpful.
    Monday, August 06, 2012 3:06 PM