1

I have a stored procedure that I currently manually run whenever a user needs me to. I am developing an interface for them to be able to kick it off themselves. I've got it up and running using SQL Service Broker to queue the users' call to the stored procedure.

It works if I remove the call, near the end of the stored procedure, to sp_send_dbmail. Otherwise, with the call left in, it creates a block against itself and hangs indefinitely (does not happen when I run it manually).

When I kill the process that is blocked (I'm guessing this is the query within the call to dbmail), the email is sent but does not include any results.

What is going on and how do I fix it?

Here is a rough idea of how the stored procedure is calling dbmail, and what the blocking/blocked processes look like that I have to mess with to get it to "complete":

use mydb

declare @filename char(24)
set @filename = 'Results' + convert(char(10),convert(date,getdate())) + '.csv'

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'AnonMailProfile',
    @recipients = 'me@myemail.com',
    @query = '

select a,b,c
into #tmpTable
from
    (select a,b,c
    from mydb..TableA
    left join mydb..TableB  
        on KeyA = FKeyA

    union all

    select a,b,c
    from mydb..TableA
    left join mydb..TableC  
        on KeyA = FKeyA
) as ct

;with cte as(
select *,rn = ROW_NUMBER() over (partition by something order by something)
from #tmpTable
)
select * from cte

',
    @subject = 'Process Complete',
    @attach_query_result_as_file = 1 ,
    @query_attachment_filename = @filename,
    @query_result_no_padding = 1,
    @query_result_separator = ' ';

| SPID | Status     | Login       | HostName   | BlkBy | DBName | Command     | ProgramName |  
|------|------------|-------------|------------|-------|--------|-------------|-------------|  
| 36   | BACKGROUND | sa          | .          | .     | master | EXECUTE     |             |  
| 66   | SUSPENDED  | DS\USER     | servername | 36    | master | SELECT INTO | SQLCMD      |
wham12
  • 295
  • 5
  • 21
  • You are leaving out something important, because the cause is not in here. Please show the whole procedure, especially anything to do with the tables in question. – RBarryYoung Jul 06 '16 at 20:00
  • It appears you're right. If I comment out the rest of the script, it does run the query and send the mail correctly. Even leaving in a single `update TableA set c = something where b is null` causes the later dbmail call to hang. – wham12 Jul 06 '16 at 21:09
  • Yes, because your service procedure is executing in the context of a transaction. So Updates it does to tables, is putting locks on them that are then blocking the mail query that is being executed in the context of the mail queue's service procedure. This is fixable, but we really need to have a better idea of what you are doing, in total, here. – RBarryYoung Jul 07 '16 at 18:01

0 Answers0