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!Who is the owner of the job and what is the permission level ?|||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...|||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 ?|||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.|||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