locked
How to fix deadlock issue - Sending a DB Mail Data from ##Temp Table RRS feed

  • Question

  • User-582711651 posted

    Hi, 

    Please check my below SQL SP, (sample script)  when I execute manually Part 1 & Part 2 separately the SP works well, suppose I execute combinedly, it takes a long time for execution, and deadlock is happening, kindly check what I did wrong?

    --- PART 1 : Begin ---
    IF OBJECT_ID(N'tempdb..##tmpTbl') IS NOT NULL
    BEGIN
    DROP TABLE ##tmpTbl
    END
    
    Create Table ##tmpTbl (RowId [int] IDENTITY(1,1), BranchName varchar(100), Branchid varchar(10), EmployeeName varchar(200), EmployeeID varchar(20),MobNo varchar(20),EmailID varchar(60))
    INSERT INTO ##tmpTbl (BranchName ,Branchid  ,EmployeeName,EmployeeID, MobNo ,EmailID )
    SELECT				'BranchName','Branchid','EmployeeName','EmployeeID','MobNo','EmailID'
    UNION ALL 
    SELECT 
    'Mumbai',
    '1001',
    'Shekar R',
    'EMP1001',
    '9123456789',
    'test@123.co.in'
    UNION ALL 
    SELECT 
    'Chennai',
    '1002',
    'Raman A',
    'EMP1002',
    '9123456788',
    'test2@123.co.in'
    ---- Part-1 End --
          				
    ---- Part:2 Begin----					
    Declare @p_SelQry varchar(MAX),@p_esub varchar(300),@p_ebody Varchar(700),@tab char(1) = CHAR(9),@fname Varchar(50)
    Set @p_SelQry=''
    Set @p_esub=''
    SET @p_ebody = '<html>Hi, <br/><br/> Please find enclosed the <b> Employee Details For BML - Operations. <br/><br/> " THIS IS AN AUTOMATED MESSAGE - PLEASE DO NOT REPLY DIRECTLY TO THIS EMAIL" <br/> </html>'
    SET @fname = 'EmployeeDetails'+ (FORMAT(getdate(), 'yyyyMMdd'))+'.csv'
    SET @p_esub = 'Employee Details | Timestamp '+ CONVERT(VARCHAR(10), GETDATE(), 104) + ' ' + CONVERT(VARCHAR(8), GETDATE(), 108)
    Select @p_SelQry='Set nocount on; SELECT BranchName,Branchid,EmployeeName,EmployeeID,MobNo,EmailID FROM ##tmpTbl '
    
    EXEC  msdb.dbo.sp_send_dbmail 
    @recipients = 'test@123.com'
    ,@subject = @p_esub
    ,@execute_query_database = 'EmpMaster'
    ,@body = @p_ebody 
    ,@body_format = 'HTML'
    ,@profile_name = 'PrF_Email'
    ,@query = @p_SelQry
    ,@query_result_header = 1
    ,@attach_query_result_as_file = 1
    ,@query_attachment_filename = @fname
    ,@query_result_separator=@tab
    ,@query_result_width =32767
    ,@exclude_query_output = 1
    ,@query_no_truncate = 1;
    
    IF OBJECT_ID(N'tempdb..##tmpTbl') IS NOT NULL
    BEGIN
    DROP TABLE ##tmpTbl
    END
    ---- Part:2 End----

    Friday, December 4, 2020 10:28 AM

Answers

  • User475983607 posted

    Have you tried basic troubleshooting or asking your peers for a code review?  I ask because the temp table is global yet the code clearly drops the table.  Why build a global temp table when the table is dropped after sending the email?  Is the global table accessed elsewhere in the code base?  If not, use a locally scoped temp table because you could have created a concurrency bug.

    IF OBJECT_ID(N'tempdb..#tmpTbl') IS NOT NULL

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 18, 2020 2:17 PM

All replies

  • User364663285 posted

    Hi,

    Sending Email would not be affected by other query.

    Try to remove the If line right after the statement handling Email message.

    Monday, December 7, 2020 7:18 AM
  • User-582711651 posted
    Hi wmec,

    Noted, can you please elaborate little bit.

    Thanks in advance.

    Aypn CNN
    Friday, December 18, 2020 1:55 PM
  • User475983607 posted

    Have you tried basic troubleshooting or asking your peers for a code review?  I ask because the temp table is global yet the code clearly drops the table.  Why build a global temp table when the table is dropped after sending the email?  Is the global table accessed elsewhere in the code base?  If not, use a locally scoped temp table because you could have created a concurrency bug.

    IF OBJECT_ID(N'tempdb..#tmpTbl') IS NOT NULL

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 18, 2020 2:17 PM