Open Forum

Expand all | Collapse all

SQL EXTRACT

  • 1.  SQL EXTRACT

    GOLD CONTRIBUTOR
    Posted Jun 07, 2018 12:47 PM
    ​Looking for Help - if possible

    I need to automate a SQL select statement to a disk file
    for example

    select * from gl20000

    I need to create a job that will run this select statement every night and create/add to a file
    at a specified location

    Is this possible ?​​

    ------------------------------
    Douglas Hart
    Dynamics Administrator
    Forex Capital Markets
    New York City NY
    ------------------------------
    Conference-GPUG_200x200


  • 2.  RE: SQL EXTRACT

    GPUG ALL STAR
    Posted Jun 08, 2018 08:20 AM
    Hi Doug,
    That's pretty easy.. just schedule a job on your SQL server that would simply run the task and save the export to a TEXT file in some place.. you'd have to manage the error handling of course with notifications in case something goes wrong..  You can even e-mail the result.
    Or you could also use Windows Scheduler and run the task as a command-line T-SQL job.

    Here are various samples to get you started :
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7d2280cf-3b33-46f7-ba82-4131e8a841c0/how-to-export-data-to-csv-file-on-a-scheduled-basis?forum=transactsql

    I for example would prefer to write a quick SSRS reports and have it scheduled to run nightly as an Excel export and have it e-mailed or stored on a shared network folder..
    We do this for our weekly Inventory status report across all our companies.

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Ultra-Electronics Forensic Technology Inc.
    Montreal QC/Canada
    @GP_Beat http://dyngpbeat.wordpress.com/
    Montreal QC GPUG Chapter Leader
    GP2013R2 / MR2012 CU14
    ------------------------------

    Conference-GPUG_200x200


  • 3.  RE: SQL EXTRACT

    TOP CONTRIBUTOR
    Posted Jun 08, 2018 08:25 AM
    ​Beat,
    You are so fast! I was just typing up something similar.

    We use the SQL  BCP (Bulk Copy Program) and SSIS for many of our solutions, depending on the complexity of the need.

    Check out some options here.
    8 Ways to Export SQL Results To a Text File - SQLServerCentral
    Sqlservercentral remove preview
    8 Ways to Export SQL Results To a Text File - SQLServerCentral
    This tip will show eight ways to export the results of a query to a text file.
    View this on Sqlservercentral >


    ------------------------------
    David Morinello
    Senior Dynamics GP Systems Architect
    Ascend Learning, LLC
    Leawood KS
    ------------------------------

    Conference-GPUG_200x200


If you've found this thread useful, dive deeper into User Group community content by role