xp_sendmail with linked server

Question

xp_sendmail with linked server

jma100

Mr or Mrs. 500

Points: 576
More actions
April 22, 2003 at 7:16 am

#80000

Hello Friends,
Iam using xp_sendmail using @query parameter to put the query results as body of message.
The sqlstatement using a linked server.
I have got the following error while executing xp_sendmail.
ODBC error 7410 (42000) Remote access not allowed for Windows NT user activated by SETUSER
What could be the probable reason for this method? What can be do to avoid this error?
Best regards.
Joseph
Edited by - jma100 on 04/22/2003 07:17:45 AM
Edited by - jma100 on 04/22/2003 07:18:23 AM

Viewing 9 posts - 1 through 8 (of 8 total)

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

This error may come from the query to linked server instead of from x_sendmail. Make sure the query work properly. Meanwhile, if you are runing a job to run this query, ensure the job owner has the 'sa' right.

jma100 Mr or Mrs. 500 Points: 576 More actions · Answer 2

The linked server query is functioning well. The query gives error only used with xp_sendmail

Edited by - jma100 on 04/22/2003 08:22:49 AM

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

Can you post more information how do you configure the linked server, especially the security setting.

jma100 Mr or Mrs. 500 Points: 576 More actions · Answer 4

following are the details of linked server connection.

security context remotelogin SA.

Local login-sa

remote login -sa

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 5

I am still look into this problem but meanwhile, I would suggest you to insert result of your linked server query into a temp table and send email by query the temp table.

Greg Norris Old Hand Points: 300 More actions · Answer 6

We had the same problem here.

It has been introduced in SP3. We registered the prob with MS and they say that it is a "feature" of the new SP.

We now have to perform the query outside of xp_sendmail and then attach the results.

There is no other way around it. We are looking for an alternate to xp_sendmail as it uses outlook and we are trying to avoid that too.

Hope this settles your search for what is wrong.

Greg Norris
DBA to Development team

Linda Johanning SSCertifiable Points: 6727 More actions · Answer 7

Don't know if this will help you any or not, but we got the same error when a job that was owned by a user (other than sa) tried to issue a statement on a linked server. The workaround was to use OSQL to bypass the SQLAgent. Take a look at KB 811031.

Linda

drnetwork SSCommitted Points: 1815 More actions · Answer 8

If you are interested in a replacement for SQL Mail, see my sample chapter at

http://www.wrox.com/books/1861008252.htm

It has a section on replacing exchange-based SQL mail with an SMTP-based solution. The wrapper around Gert Drapers xp_smtp_sendmail extended proc can be modified to do cross-server queries quite easily.