0

I have a SQL agent job that queries the database and exports the results into a csv file. It needs to run daily and The output file (Output.csv) gets overwritten everytime the job runs. Is there a way to create new files instead of overwriting? Say monday.csv, tuesday.csv etc or maybe {date}.csv

This is the command:

SQLCMD -S SQL-CLUST1 -Q "SELECT * FROM TABLE" -o "\\\PATH\output.csv" -W -s"," 

Step type: Operating system (CmdExec)

Run as : SQL Proxy

Note: The output file and append output to existing file is unavailable in the advanced tab.

  • Which DBMS? SQL Server? SQL is a language, not a DBMS. – Colin 't Hart Mar 9 '15 at 14:48
  • @Colin Yes its MS SQL Server. – Rakesh Mar 9 '15 at 14:55
1

Below script will help you. Just save it as a .bat file.

@echo off
setlocal
set timehour=%time:~0,2%
sqlcmd -S SQL-CLUST1 -E -Q "SELECT * FROM TABLE" -o report-%date:~-4,4%%date:~-10,2%%date:~-7,2%-%timehour: =0%%time:~3,2%.txt

If you have xp_cmdshell enabled, then it is much easier :

DECLARE       @sqlCommand   VARCHAR(max)
DECLARE       @filePath     VARCHAR(100)
DECLARE       @fileName     VARCHAR(100)

SET    @filePath = 'C:\Temp\'

SET    @fileName = 'Output_' +
       + CONVERT(VARCHAR, GETDATE(), 112) + '_' +
         CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR) + '_' +
         CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + '.txt'

SET    @sqlCommand =
       'SQLCMD -S server_name -E -d master -q "select @@servername" -o "' +
       @filePath + @fileName +
       '" -h-1'


PRINT       @sqlCommand

--EXEC   master..xp_cmdshell @sqlCommand
GO 
0

You can add another step after that SQLCMD that performs renaming your outputfile in another CmdExec step type:

ren myfile.csv myfile_%date:~4,2%-%date:~7,2%-%date:~10,4%%time:~0,2%%time:~3,2%%time:~6,5%.csv

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.