RETURN
SELECT 'SET @ContextInfo = CAST(''tTrans.' + TableName + ','' + CONVERT(VARCHAR(30), GETDATE(), 121) AS VARBINARY(128))
SET CONTEXT_INFO @ContextInfo
SELECT @Counter = COUNT(*) FROM tTrans.' + TableName + ' WITH (XLOCK, ROWLOCK) WHERE DataSupplierId = 2
'
FROM Metadata.Tables WHERE SchemaName = 'tTrans'
GO
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @Counter BIGINT, @ContextInfo VARBINARY(128)
SET @ContextInfo = CAST(@@SPID AS VARBINARY(128))
--#region Create Locks for tables
SET @ContextInfo = CAST('tTrans.TransSitus,' + CONVERT(VARCHAR(30), GETDATE(), 121) AS VARBINARY(128))
SET CONTEXT_INFO @ContextInfo
SELECT @Counter = COUNT(*) FROM tTrans.TransSitus WITH (XLOCK, ROWLOCK) WHERE DataSupplierId = 2
SET @ContextInfo = CAST('tTrans.TransText,' + CONVERT(VARCHAR(30), GETDATE(), 121) AS VARBINARY(128))
SET CONTEXT_INFO @ContextInfo
SELECT @Counter = COUNT(*) FROM tTrans.TransText WITH (XLOCK, ROWLOCK) WHERE DataSupplierId = 2
SET @ContextInfo = CAST('tTrans.TransPreForeclosureDetail,' + CONVERT(VARCHAR(30), GETDATE(), 121) AS VARBINARY(128))
SET CONTEXT_INFO @ContextInfo
SELECT @Counter = COUNT(*) FROM tTrans.TransPreForeclosureDetail WITH (XLOCK, ROWLOCK) WHERE DataSupplierId = 2
SET @ContextInfo = CAST('tTrans.TransLegal,' + CONVERT(VARCHAR(30), GETDATE(), 121) AS VARBINARY(128))
SET CONTEXT_INFO @ContextInfo
SELECT @Counter = COUNT(*) FROM tTrans.TransLegal WITH (XLOCK, ROWLOCK) WHERE DataSupplierId = 2
SET @ContextInfo = CAST('tTrans.TransLegalParty,' + CONVERT(VARCHAR(30), GETDATE(), 121) AS VARBINARY(128))
SET CONTEXT_INFO @ContextInfo
SELECT @Counter = COUNT(*) FROM tTrans.TransLegalParty WITH (XLOCK, ROWLOCK) WHERE DataSupplierId = 2
SET @ContextInfo = CAST('tTrans.TransReject,' + CONVERT(VARCHAR(30), GETDATE(), 121) AS VARBINARY(128))
SET CONTEXT_INFO @ContextInfo
SELECT @Counter = COUNT(*) FROM tTrans.TransReject WITH (XLOCK, ROWLOCK) WHERE DataSupplierId = 2
SET @ContextInfo = CAST('tTrans.TransDeed,' + CONVERT(VARCHAR(30), GETDATE(), 121) AS VARBINARY(128))
SET CONTEXT_INFO @ContextInfo
SELECT @Counter = COUNT(*) FROM tTrans.TransDeed WITH (XLOCK, ROWLOCK) WHERE DataSupplierId = 2
SET @ContextInfo = CAST('tTrans.TransLegalPartyName,' + CONVERT(VARCHAR(30), GETDATE(), 121) AS VARBINARY(128))
SET CONTEXT_INFO @ContextInfo
SELECT @Counter = COUNT(*) FROM tTrans.TransLegalPartyName WITH (XLOCK, ROWLOCK) WHERE DataSupplierId = 2
SET @ContextInfo = CAST('tTrans.TransRelease,' + CONVERT(VARCHAR(30), GETDATE(), 121) AS VARBINARY(128))
SET CONTEXT_INFO @ContextInfo
SELECT @Counter = COUNT(*) FROM tTrans.TransRelease WITH (XLOCK, ROWLOCK) WHERE DataSupplierId = 2
SET @ContextInfo = CAST('tTrans.TransAssignment,' + CONVERT(VARCHAR(30), GETDATE(), 121) AS VARBINARY(128))
SET CONTEXT_INFO @ContextInfo
SELECT @Counter = COUNT(*) FROM tTrans.TransAssignment WITH (XLOCK, ROWLOCK) WHERE DataSupplierId = 2
SET @ContextInfo = CAST('tTrans.TransPreForeclosure,' + CONVERT(VARCHAR(30), GETDATE(), 121) AS VARBINARY(128))
SET CONTEXT_INFO @ContextInfo
SELECT @Counter = COUNT(*) FROM tTrans.TransPreForeclosure WITH (XLOCK, ROWLOCK) WHERE DataSupplierId = 2
SET @ContextInfo = CAST('tTrans.TransInvoluntaryLien,' + CONVERT(VARCHAR(30), GETDATE(), 121) AS VARBINARY(128))
SET CONTEXT_INFO @ContextInfo
SELECT @Counter = COUNT(*) FROM tTrans.TransInvoluntaryLien WITH (XLOCK, ROWLOCK) WHERE DataSupplierId = 2
SET @ContextInfo = CAST('tTrans.TransLien,' + CONVERT(VARCHAR(30), GETDATE(), 121) AS VARBINARY(128))
SET CONTEXT_INFO @ContextInfo
SELECT @Counter = COUNT(*) FROM tTrans.TransLien WITH (XLOCK, ROWLOCK) WHERE DataSupplierId = 2
SET @ContextInfo = CAST('tTrans.Trans,' + CONVERT(VARCHAR(30), GETDATE(), 121) AS VARBINARY(128))
SET CONTEXT_INFO @ContextInfo
SELECT @Counter = COUNT(*) FROM tTrans.Trans WITH (XLOCK, ROWLOCK) WHERE DataSupplierId = 2
SET @ContextInfo = CAST('tTrans.TransPcrFields,' + CONVERT(VARCHAR(30), GETDATE(), 121) AS VARBINARY(128))
SET CONTEXT_INFO @ContextInfo
SELECT @Counter = COUNT(*) FROM tTrans.TransPcrFields WITH (XLOCK, ROWLOCK) WHERE DataSupplierId = 2
SET @ContextInfo = CAST('tTrans.TransLot,' + CONVERT(VARCHAR(30), GETDATE(), 121) AS VARBINARY(128))
SET CONTEXT_INFO @ContextInfo
SELECT @Counter = COUNT(*) FROM tTrans.TransLot WITH (XLOCK, ROWLOCK) WHERE DataSupplierId = 2
SET @ContextInfo = CAST('tTrans.TransLegalPartyParsed,' + CONVERT(VARCHAR(30), GETDATE(), 121) AS VARBINARY(128))
SET CONTEXT_INFO @ContextInfo
SELECT @Counter = COUNT(*) FROM tTrans.TransLegalPartyParsed WITH (XLOCK, ROWLOCK) WHERE DataSupplierId = 2
SET @ContextInfo = CAST('tTrans.TransLegalPartyOrigName,' + CONVERT(VARCHAR(30), GETDATE(), 121) AS VARBINARY(128))
SET CONTEXT_INFO @ContextInfo
SELECT @Counter = COUNT(*) FROM tTrans.TransLegalPartyOrigName WITH (XLOCK, ROWLOCK) WHERE DataSupplierId = 2
--#endregion
SET CONTEXT_INFO 0x
ROLLBACK
GO
RETURN
DECLARE @SPID INT = 65,
@recipients NVARCHAR(1000) = 'gvarol@corelogic.com',
@Body NVARCHAR(2000)
DECLARE @ContextInfo VARCHAR(128)
SELECT @ContextInfo = CAST(CONTEXT_INFO AS VARCHAR(128))
FROM master.sys.sysprocesses (NOLOCK)
WHERE SPID = @SPID
AND CONTEXT_INFO <> 0x0
IF @ContextInfo <> ''
BEGIN
DECLARE @SPStr VARCHAR(30) = CAST(@SPID AS VARCHAR)
SELECT @Body = 'SPID: ' + @SPStr + ', TableName: ' + ISNULL(C1, 'NULL') + ', StartDate: ' + ISNULL(CONVERT(VARCHAR(30), dv.DateVal, 121),
'NULL') + ', DateDiffMS: '
+ ISNULL(CAST(DATEDIFF(millisecond, DateVal, GETDATE()) AS VARCHAR), 'NULL') + ', Now: ' + CONVERT(VARCHAR(30), GETDATE(), 121) + '
CONTEXT_INFO: ' + @ContextInfo
FROM Util.dbo.ParseDelimitedColumns4(@ContextInfo, ',') a
CROSS APPLY (SELECT Util.dbo.TryParseDateTime (C2) AS DateVal) dv
EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@subject = N'Failed to exclusively lock the table data',
@body = @Body
EXEC('KILL ' + @SPStr)
END
GO