SQL Server - MS SQL Server 2005 - Asked By DGL Smith on 08-Mar-12 08:35 AM

Is there a way to write into the SQL querry to -

1) Always write to a file when running the selected querry
2) Pushing the file to a selected file to a speceific location (folder on server).
3) Assigned a selected name (i.e PO History) to the file when it writes to the folder

D Company replied to DGL Smith on 08-Mar-12 12:55 PM

i think the one thing which can serve your purpose is sql server jobs, u can write the jobs in such way so that it can save the history of all the transaction.

check this helpful http://weblogs.sqlteam.com/tarad/archive/2008/06/30/SQL-Server-jobs-on-production-instances.aspx

DGL Smith replied to D Company on 08-Mar-12 01:20 PM
[)ia6l0 iii replied to DGL Smith on 08-Mar-12 09:08 PM
One way is to call the SQLCmd utility from your program. You would then have the flexibility of specifying,
a) The query
b) The filename
c) The save location. 

SqlCmd utility can write to output files with simple command like the one below.
sqlcmd -Q "SELECT GETDATE()" -o outputfilename.txt

If you are executing this from your application server, you can do this. 
sqlcmd -S servername\instancename "SELECT GETDATE()" -o outputfilename.txt

If you need to execute a SQL script, you can use the -i argument.
sqlcmd -S servername\instancename -E -i somescriptfile.sql -o outputfilename.txt

Note that you can use the XP_CmdShell system stored procedure to execute the SQLCommand utilitty as well. The following command shows an example.
EXEC MASTER.dbo.xp_cmdshell 'sqlcmd -S servername\instancename -E -i somescriptfile.sql -o outputfilename.txt'

In order to pass parameters to the SQLCmd utiltiy use the -v attribute, and pass the variables. If your variables are var1, then the following command is a good example.
sqlcmd -S servername\instancename -E -i somescriptfile.sql var1="1" -o outputfilename.txt

Yes, var1 would be accessible from the "somescriptfile.sql" in the above example. 

To know more on sqlcmd Utility, please read about SqlCmd on http://msdn.microsoft.com/en-us/library/ms162773.aspx.

Hope this helps.