Post reply

xp_sendmail with linked server

  • 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

  • April 22, 2003 at 8:16 am

    #454907

    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.

  • April 22, 2003 at 8:22 am

    #454910

    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

  • April 22, 2003 at 8:30 am

    #454913

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

  • April 22, 2003 at 8:42 am

    #454915

    following are the details of linked server connection.

    security context remotelogin SA.

    Local login-sa

    remote login -sa

  • April 22, 2003 at 9:18 am

    #454916

    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.

  • April 23, 2003 at 10:28 pm

    #455119

    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

  • April 24, 2003 at 7:48 am

    #455186

    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

  • April 24, 2003 at 7:57 am

    #455190

    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.

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

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