Scheduled SQL query output to file

  • We are trying to schedule a *.sql query to run and then output to a file on the network using the SQL management studio scheduler…

    is this possible? What is the best method without using third party tools?

  • Either setup an SSIS task to do it or a secure "proxy" to turn on, use, and turn off xp_CmdShell just long enough to use BCP.

    Another way to do it is to setup a simple batch file with either an OSQL call, a SQLCMD call, or a BCP job that uses a "trusted" connection. Never put passwords in such a batch file. The advantage here is you don't have to fight city hall to use xp_CmdShell in a secure environment even using "approved and secure" methods like properly created and operated proxies.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I would create a simple SSIS Package and schedule the package to fire off. But keep in mind that you need to have the SQL Service running under a domain account if you want SQL to place the file in a directory that it not on the same box.

    SSIS is very simple and can provide more options. I do not ever use batch or command files, they are just too unsecure, and they will not be in the backup of a database.

    Andrew SQLDBA

Viewing 3 posts - 1 through 2 (of 2 total)

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