Sudden xp_send mail error

[Jennifer]

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 = 'j******@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

[Simon Hayes]

je**********@hotmail.com (Jennifer) wrote in message news:<33**************************@posting.google. com>...

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 = 'j******@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

A similar problem is described in this KB article, and I guess one of
the proposed solutions should fix it:

http://support.microsoft.com/default...&Product=sql2k

I've seen this error occasionally in the past, and I've always
resolved it by setting the job owner to sa.

Simon