Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2008
    Posts
    12

    Unanswered: Queries on 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 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?

    Thanks in advance .

    Warm regards,
    Payal

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's one option to email:
    Code:
    EXEC master..xp_sendmail
           @recipients  = '<email address>'
         , @message     = 'Message body text'
         , @subject     = 'Subject'
         , @attachments = 'C:\location\to\your_file.csv'
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •