How to remove Column Header and White spaces in output file

I come across a requirement that I need to create a job on a SQLServer database and generate output separated by a pipe ("|" ) symbol for each column. Also remove all the white spaces between the columns.

As the requirement is to load the data into an ERP system. I found an easy solution using "sqlcmd"

use -W option to remove white spaces

use -h-1 option to remove column heading or header.

Here is the syntax:-

c:\> sqlcmd -W -h-1 -S <Server_name>,<port> -U <user>-P <password> test -i "c:\query.sql" -o "c:\output.txt"

Where
       query.sql - where you will be writing the query
        for ex:- select  @@version
        I would recommend to use "set nocount on" in the query.sql to suppress the record count.

       output.txt  - where you will see the clean output

Hope this helps.

Regards,
Satishbabu Gunukula

How to add Date and Time to output file for Jobs

In one of my project I  come across this requirement that setup a job to run every hour and the output must have a Date and Time.

I found couple of solutions

1. When using SQLServer Management Studio(SSMS) you can add below syntax for output file

SSMS--> SQL Server Agent --> Job Name--> Properties --> Steps --> Select "edit" -->  Advanced 

$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME))

For exL:-
D:\SQLBackup\ouput_file$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).txt

2.  Use Tokens in Job setup

Refer below MSDN doc
http://msdn.microsoft.com/en-us/library/ms175575.aspx

3. use %date% format with sqlcmd

for ex:-
sqlcmd -S sqlserver,port -U <user> -P <password>  -i "c:\query.sql" -o "c:\ouput_%date%.txt"

You can play around %date% format if you are looking for specific date format.

Regards
Satishbabu Gunukula