Stalkerbee


Hi,

I've got sql code that I want to execute at a set interval. The code contains some select & update statements and then after that it uses xp_sendmail to send some emails depending on some values.

If i run the code in Query Analyzer everthing works fine and the mails are sent. When I excute the DTS package manually it also works fine, but when I run the scheduled job the update statements execute but for some reason the xp_sendmail fails without giving any error.

After some digging I found that if I dont use the @query parameter in the xp_sendmail command it works.

The code i'm using to send the mail:

EXEC master.dbo.xp_sendmail @recipients = @Recip2,
@query = 'SELECT * FROM TABLE',
@subject = @MailSubject2,
@message = @Msg2,
@attach_results = 'TRUE', @width = 250

Is there any reason why i cant attach query results in the xp_sendmail for a scheduled job

Thanks




Re: xp_sendmail with @query parameter in scheduled job?

Chris Howarth


Which database does the Table reside in  It might be better to fully-qualify your table name, like so:

SELECT * FROM [MyDBName].[TableOwner].TABLE

Chris







Re: xp_sendmail with @query parameter in scheduled job?

Stalkerbee

Hi, thanks for the reply.

Unfortunately I¡¯m already using the fully-qualified table name. The select statement I used in the post is just a dummy statement so that my post is less clogged.

@query = 'SELECT COMPUTER_REF, FILE_REF, SUB_FILE_REF, CLIENT_CODE, CLIENT_REF FROM ForwardView.dbo.SSI_ShipShapeInterface WHERE (STATUS = 90) ORDER BY FILE_REF'






Re: xp_sendmail with @query parameter in scheduled job?

Stalkerbee

FIY, the server is SQL2000 with SP3 Developer Edition.