Scheduled SQL query output to file

Question

Scheduled SQL query output to file

dmike

SSC-Addicted

Points: 400
More actions
February 17, 2010 at 5:02 pm

#220710

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?

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

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

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)

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

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