Sending Email using SQL Job
-
Hi, 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 Command : 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) Command : "D:\MSSQL\JOBS\EmailFile.cmd" D:\Applicants.txt aa@yahoo.com ss@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 an Excel file and to attach the file to the mail, if there is no results returned no file should be atached * If the Sp doesn't return a result set she wants a message and if it does she wants the message to be another which includes date also. Can all these be done , if so can you plz help me with it? Thanks in advance. Warm regards, Payal
-
Hi, 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 Command : 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) Command : "D:\MSSQL\JOBS\EmailFile.cmd" D:\Applicants.txt aa@yahoo.com ss@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 an Excel file and to attach the file to the mail, if there is no results returned no file should be atached * If the Sp doesn't return a result set she wants a message and if it does she wants the message to be another which includes date also. Can all these be done , if so can you plz help me with it? Thanks in advance. Warm regards, Payal
Firstly: is a CSV file acceptable? This opens just in Excel but is easier to create from SQL than an Excel file. If an Excel file is required then you should probably use SSIS (which allows all of this winderfule logic to be implemented)
-
Firstly: is a CSV file acceptable? This opens just in Excel but is easier to create from SQL than an Excel file. If an Excel file is required then you should probably use SSIS (which allows all of this winderfule logic to be implemented)