I am using SQl 2005 for setting up a job which has 2 steps:-
Step 1: Executes a SP which sends out result to a file
Type : T-SQL
DECLARE @cmd varchar(2000),
SET @cmd = 'osql -E -S CONDOR -s";" -w2000 -d Arion -Q"EXEC dbo.ENRSP_Applicants" -o D:\Applicants.txt'
EXEC master..xp_cmdshell @cmd
Step 2: Mailing the txt file to the user
Type : Operating System(CmdExec)
"D:\MSSQL\JOBS\EmailFile.cmd" D:\Applicants.txt firstname.lastname@example.org@yahoo.com "Applicants Report"
This works fine but the user want some modification to the current Job
* the email subject to include the date also
* Wants the result set to be dumped into a CSV file and to attach the file to the mail
* Can you also tell me how to include a message to the mail plz?
I know its easy to get this all done using a SSIS but Can these be done using an SQL Job , if so can you plz help me with it?