Sudden Xp_send Mail Error

Jul 20, 2005

I've got a job that sends out the results of a stored procedure in an
email via xp_sendmail. The job code is as follows:

DECLARE @rlist varchar(1000)
Declare @Q varchar(100)

Select @Q = 'EXEC impact_exec..TLD_Reconciliation'
Declare @Sub nvarchar(60)
Select @Sub = 'TLD Reconciliation - Condensed Report'
SELECT @rlist = 'jmiller@wbhq.com'
exec master..xp_sendmail @recipients=@rlist,@query=@Q, @subject=@Sub

The error message I get when running this in Query Analyzer is:

ODBC error 7410 (42000) Remote access not allowed for Windows NT user
activated by SETUSER.

In Enterprise Manager the owner of the stored procedure in question is
dbo. Our SQL guru here suggested I change the owner of the stored
procedure from dbo to myself.

I did that and got a warning message that changing the owner will
break connections. (I'm paraphrasing here because I don't remember
the exact wording of the warning.) Anyhoo, after changing the owner,
and then trying to run the code in Query Analyzer I got the error
message that the stored procedure could not be found.

I'm not sure what to do here. I've never seen the error message
before. This same query worked just fine a couple of days ago.

Any ideas?

Thanks,
Jennifer

View 1 Replies

Xp_send Mail (Confused)!!

Jul 2, 2002

I want to use xp_sendmail against a database other than the master. When I run a test using the master database, it sends a test message w/no problems. However when I try to use xp_sendmail against a database I've created, it gives me an error stating:

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'xp_sendmail'.

How can I use xp_send mail using a dabase other than Master?? Please Help.

View 3 Replies View Related

How To Run Xp_send Mail From A Store Procedure

Oct 2, 2001

hi and thanks for your help, I created the following store procedure to run and send query result via sql mail. how can I run the xp_sendmail.... from a store procedure if I cannot use user Master in a store procedure or trigger. ?

Thanks
Ali
---------------------------

create procedure p_ci_notification as

if exists(
select customer_invoice_id,invoice_total,convert(varchar( 10),date_of_service,101) as date_of_service
from ci_master
where convert(varchar(10),date_of_service+60,101) > convert(varchar(10),getdate(),101)
and invoice_total >5000
and customer_invoice_id not in (select customer_invoice_id from ci_notification )
)

BEGIN
insert ci_notification(customer_invoice_id,invoice_total, date_of_service)
select customer_invoice_id,invoice_total ,date_of_service
from ci_master
where convert(varchar(10),date_of_service+60,101) > convert(varchar(10),getdate(),101)
and invoice_total >5000
and customer_invoice_id not in (select customer_invoice_id from ci_notification )

-- send the email
use master
EXECUTE xp_sendmail @recipients = xzy@aol.com;abc@yahoo.com'
,@message = 'Erick,
how to send a query result via sql mail'
,@query ='select customer_invoice_id,invoice_total,convert varchar (10),date_of_service,101)as date_of_service
from cesweb.cesi_intranet.dbo.ci_notification
WHERE notified =0'

-- update the flag
--select customer_invoice_id,invoice_total,convert(varchar( 10),date_of_service,101) as date_of_service,notified
--from ci_notification
-- truncate table ci_notification
update server.dbname.dbo.ci_notification set notified = 1 WHERE notified =0
END

------------------------------------------------------------------------
ERROR message that I got:---------------------
Server: Msg 154, Level 15, State 1, Procedure p_ci_notification, Line 20
a USE database statement is not allowed in a procedure or trigger.