Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 Answers 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.

share|improve this answer
    
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

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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