4

I have an issue SQL Server 2005.

sp_send_dbmail fails when mailing out with the result of a query (inline or attatchment).

No error message is given / reported, the query just keeps executing but nothing returned (have left it for 28 minutes so far) with no time out etc.

Mails go out fine when no queries are involved.

Does anyone know how to solve this?

  • What happens if you run the query on its own? Does it run okay? – Jon Seigel Jul 27 '12 at 13:11
  • @Jon - Hi Jon, yes query works fine... any query with mail causes issue, even Select 1; – Jonno Jul 27 '12 at 13:43
  • 2
    Could you edit the question to include the T-SQL you're using? – Jon Seigel Jul 27 '12 at 18:27
2

Is it possible that you're running on a deadlock situation? Create a test table that you know for sure no one is accessing it, populate it with some sample data, and run a query in proc against that table just to test it out.

  • thanks for the suggestion, we have done just that with a query we know has no issue etc, but the same occurs when the query is part of the sp_send_dbmail – Jonno Jul 27 '12 at 12:36
  • I'm not sure I understand what you are saying. Execute your send_mail T-SQL statement as yourself, and try also executing the same T-SQL statement as the SQL Server Agent account. If it goes through as the agent and not your own account, it's a relay issue. – Eric Higgins Jul 27 '12 at 20:50
0

The SQL Server Agent is the sender if you are indicating jobs are sending successfully. When running a query manually, you are the sender.

You can test by logging into your SSMS query window as the Agent Account.

I'd suggest talking with your mail admin to see if you have rights under your account to relay mail.

-1

I've come across recently myself. The problem, in my case, seemed to be with the number of records returned by the query.

To troubleshoot, try limiting the results by using SELECT TOP

  • In comments Jonno said the issue persisted even with a 'SELECT 1', so no. of records returned isn't the problem. – LowlyDBA Jan 29 '15 at 22:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.