Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Location
    Tokyo Japan
    Posts
    26

    Unanswered: HELP: problem with using xp_sendmail

    below is code snippet (which im assuming is the cause of the problem) from a certain stored procedure

    EXEC Master.dbo.xp_sendmail
    @dbuse = 'DanoneSSS',
    @recipients = 'xxx@yyy.co.jp',
    @subject = 'mmm nnn',
    @message = @parMessage,
    @query = 'SELECT CHECK_JS_CODE, CHECK_TYPE FROM T_CHECK_LOG WHERE CHECK_STATUS = 0 ORDER BY CHECK_ID',
    @attachments = 'ResultSet.txt',
    @attach_results = TRUE,
    @no_header = TRUE,
    @separator = '',
    @ansi_attachment = TRUE

    as in my case, when i execute this particular stored procedure, SQL Server sends me the email AND with the necessary attachment

    however, when i create a Job with "EXEC dbo.SOSOStoredProcedure," though the Job executes successfully, no EMAIL is sent to me.

    the funny thing is, when i comment out the following lines

    @query = 'SELECT CHECK_JS_CODE, CHECK_TYPE FROM T_CHECK_LOG WHERE CHECK_STATUS = 0 ORDER BY CHECK_ID',
    @attachments = 'ResultSet.txt',
    @attach_results = TRUE,
    @no_header = TRUE,
    @separator = '',
    @ansi_attachment = TRUE

    the Job executes successfully with an EMAIL being sent to me

    does SQL Server's job CANNOT handle xp_sendmail that returns a RESULT set? or i may be mistaken somewhere...

    thanks!

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Who is the owner of the job and what is the permission level ?

  3. #3
    Join Date
    Dec 2003
    Location
    Tokyo Japan
    Posts
    26
    Originally posted by rnealejr
    Who is the owner of the job and what is the permission level ?
    thanks for the reply...

    but i did getting it working though...

    by carefully reading through BO regarding xp_sendmail, it seems that in using xp_sendmail to send file attachments, the specific procedure must be defined in the master database. this is what i have done.

    then, defined a permission level for the procedure's properties, and the specific entry in SQL Agent.

    i may have gotten it working... but there still may be a gap in my understanding, and i would appreciate if anyone could check on this - Books Online could get so scarce in regards to explanations and examples.

    again, thanks...

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    the specific procedure must be defined in the master database
    This is only done if the sa wants to conceal the behavior of xp_sendmail.
    Which if that were the case you would have been unsuccessful regardless of where you ran the stored procedure.


    then, defined a permission level for the procedure's properties, and the specific entry in SQL Agent.
    Can you elaborate ?

  5. #5
    Join Date
    Sep 2003
    Location
    Mumbai, India
    Posts
    36
    Alter the SP.
    In the same sp, use exec master..xp_cmdshell 'isql "Your query" -o drive:\filename.txt etc'
    Then remove the query portion from xp_sendmail.
    Run the job again. It would work.

  6. #6
    Join Date
    Dec 2003
    Location
    Luton, Bedfordshire, UK
    Posts
    6

    I also need help

    I beleive (rightly or wrongly) that you are unable to run an open query within an xp_sendmail.

    I am trying to run a stored procedure with an open query within it. wrapping it within an xp_sendmail.

    Which fails.

    Any sugestions on how to get this running.

    Thanks

Posting Permissions

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