Post reply

basic xp_sendmail issue

  • May 5, 2006 at 2:39 pm

    #117544

    i'm trying to exec the following basic xp_sendmail email from Query Analyzer - something very basic, but Query Analyzer executes over time with no result - like it's hung but no result set.

    can someone help?

    thanks!

    steve

    use test

    declare @recipients varchar(255),@subject varchar(50)

    select @recipients = 'jane.doe; joe.doe'

    select @subject = 'test email'

    exec master.dbo.xp_sendmail

    @recipients, @subject

  • May 5, 2006 at 2:56 pm

    #636368

    Lots of possible issues, but as an initial test, I always try to send a regular e-mail through Outlook from the server.

  • May 5, 2006 at 3:12 pm

    #636373

    Thanks - I can send email thru DTS via a task. This works.

    Trying to create a stored procedure from the above doesn't work.

    SQL Mail is working - so this s/b working???

    Thanks for your help!

  • May 5, 2006 at 3:13 pm

    #636374

    I should also add that once I get the above working in query analyzer I'll attach a query (@query = [stored_proc_name]

  • May 5, 2006 at 3:37 pm

    #636383

    exec master..xp_cmdshell 'dtsrun -S[server_name] -U[domain]\[login] -E -Ntest_email'

    also works...but can't figure out how to get:

    exec master..xp_sendmail to work...

  • May 5, 2006 at 5:22 pm

    #636389

    I also might add that:

    begin

    use master

    exec xp_stopmail

    end

    does not work either.... from query analyzer

  • May 5, 2006 at 10:01 pm

    #636393

    The fact that it works from DTS and not from a stored proc gives us another possibility. It's possible that the SQLServer service and the SQLServer Agent service have different logins. If so, they each would need to be setup to be able to send mail (I'm assuming you're not using Exchange for mail, as it's a different issue when setting things up with Exchange).

    As an alternative, and what I'd personally recommend, you could try out xp_smtp_sendmail, which I find much easier to setup in a variety of environments, as its needs are pretty minimal. CDO is another option that I find easier to setup than SQL Mail, but I still prefer xp_smtp_sendmail.

    You can find out about it and, if interested, download it, from here.

  • May 6, 2006 at 8:56 am

    #636409

    Hi Dave thanks for your help.

    I'll give xp_smtp_sendmail a shot...

    Also - I'm running MS SQL Server v7.0 on XP and the MAPI profile is setup on Exchange.

    Under Services, the login for MSSQLServer and SQLServerAgent are the same, domain\login. This login is also being used to login to SQL Server and has SA permissions so that xp_sprocs can be executed.

    exec xp_stopmail and exec xp_startmail also work from the query analyzer.

    When I started writing this, I canceled the execution and the analyzer is still trying to cancel...so something perhaps is working.

    I did remove the BUILTIN\Administrator login and this seemed to help exec the stop/start xsprocs...

    Thanks again for any suggestions.

    Steve

  • May 7, 2006 at 1:21 pm

    #636436

    Here's the latest of what I've tried andma am now going to make sure the exchange server is talking named pipes with the sql server.

    1. made nt account the same on sql server and in services

    2. mail profiles in sqlserveragent agree with sqlmail profile

    3. can send/receive mail from outlook client

    4. dts send mail works (of course since i learned dts doesn't use xp_sendmail).

    result:

    1. can exec xp_stopmail and xp_startmail from query analyzer

    2. exec xp_sendmail 'some_email@address.com', 'test message' just makes the query analyzer spin forever with no result like 'sqlmail sent'.

    motivation:

    1. xp_sendmail allows a @query to be executed and the result set in the body of the email (desired result).

    Any help is greatly appreciated.

    steve

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

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