sp_send_dbmail locks up everything
Hello friends,
Iooks, I have serious trouble with the sp_send_dbmail procedure.
During a transaction with data consolidation I would like to send out an email with the query results for crappy data. The code I use is further below. Just executing the code without being in a transaction is fine, I get an email with attachment. But as soon as I execute the whole procedure around it the whole SQL Server 2005 blocks. From the same Management Studio I cannot even open the Activity Monitor anymore. It says "lock request timeout exceeded" followed by information about switching through a lot database contexts and finally it concludes (?) that I do not have enough permissions for Activity Monitor. What's funny: I can create new queries and execute them without problems if they not target the same tables as in the transaction (TABX lock, clear, no question). But I cannot cancel neither the blocked query nor the process (via KILL #SPID). The only way is to totally restart the SQL Server 2005 instance.
I know that sp_send_dbmail is running in a different session that I get an error immediately (invalid object name) if I don't specify the database.
Am I doing anything wrong? Shouldn't it be possible to at least terminate/kill the deadlocked process?
Appreciating any feedback.
Thanks a lot, Frank
/* mail sanity check ;) */
EXEC msdb.dbo.sp_send_dbmail
@profile_name='default'
,@recipients='notdisclosed@example.org
,@subject='Invalid data, fund is not existing'
,@query= N'SELECT Q.QuestionnaireTemplateName
,Q.[FundGuid]
,Q.[FundStyleId]
,Q.[AsOfDate]
FROM [Staging].[Questionnaires] AS Q WITH (NOLOCK)
LEFT OUTER JOIN Fund.Fund AS Funds WITH (NOLOCK)
ON Funds.FundGuid = CAST(Q.FundGuid AS varchar(50))
AND Funds.AsOfDate = Q.AsOfDate
AND Funds.[FundStyleId] = Q.[FundStyleId]
WHERE Funds.Id IS NULL'
,@execute_query_database='HealthCheckDb'
,@attach_query_result_as_file= 1
,@query_attachment_filename='erroneousQuestionnaires.txt'
,@query_result_separator=';'
