Re: query didnt work in xp_sendmail

Question

Post reply

Re: query didnt work in xp_sendmail

XuJohn

Right there with Babe

Points: 775
More actions
February 24, 2004 at 8:07 am

#109057

Hi,
I have a query 'select * from t_srf_rqst where no_id = (select MAX(no_id) from t_srf_rqst)', which works in Query Analyzer'.Hwever, when I use it with xp_sendmail like this:
EXEC master..xp_sendmail @recipients = 'john123@yahoo.com',
@query = 'select * from t_srf_rqst where no_id = (select MAX(no_id) from t_srf_rqst)',
@subject = 'New SQL Installation Request'
I got the following error message:
ODBC error 208 (42S02) Invalid object name 't_srf_rqst'.
ODBC error 208 (42S02) Invalid object name 't_srf_rqst'.
Can someone tell me what's wrong?
Thanks in advance.
John

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

John,

Have you tried the following:??

SELECT * FROM databasename.owner.t_srf_rqst???

The xp_sendmail runs from master and your table probably isn't in master. I think that once you tell it where to look this should work for you.

Good Luck,

AJ

Good Hunting!

AJ Ahrens

webmaster@kritter.net

XuJohn Right there with Babe Points: 775 More actions · Answer 2

AJ,

You are right!! I misseed a parameter @dbuse, which tells SQL the database that query is going to run again. Now I have another issue: the result is a mess. How do I format it the way I want?

Thanks again for your help.

P.S. Query Result:

no_id fnm_nm

lnm_nm

bsunt_nm

ph_cd

cell_cd

appl_nm

srv_nm

opsys_nm

vrsn_nm

coll_nm

lic_nm

dtd_dt

insl_nm

cmnt_tx

----------- --------------------------------------------------

--------------------------------------------------

-----------------------

--------------------------------------------------

-----------------------------------------------------------------------

-------------------------------------

7 John

Lee

2019892354

WWW

Windows 2000 Enterprise

SQL 2000 Enterprise

Other

Per Processor mode

1900-01-01 00:00:00.000

SG

test-test

sgordon Old Hand Points: 359 More actions · Answer 3

sgordon

Old Hand

Points: 359

February 25, 2004 at 8:31 am

#495579

Try the @Width= 'your size here' parameter.

XuJohn Right there with Babe Points: 775 More actions · Answer 4

I tried @width, however, the format of the query result was still not good.

sgordon Old Hand Points: 359 More actions · Answer 5

I always use as an attachment instead of the body of the email.

@attach_results = 'true'

XuJohn Right there with Babe Points: 775 More actions · Answer 6

That parameter makes lots sense. thanks again for your help!

Have a nice day.