How can you identify a process that has been cancelled?
for example:
I was running this dynamic sql - via a linked server:
declare @sql nvarchar(max)
declare @i int
declare @z int
DECLARE @vCrlf CHAR(2);
SELECT @vCrlf = CHAR(13)+CHAR(10)
,@Z = COUNT(*)
,@I = 1
FROM #T1
SELECT @sql = '
SET IDENTITY_INSERT bocss2.DBO.tblBCataloguePriceSetItem ON;
INSERT INTO bocss2.dbo.tblBCataloguePriceSetItem(
[lngCataloguePriceSetItemID]
,[lngCataloguePriceSetID]
,[strItemNo]
,[strTier1]
,[strTier2]
,[strTier3]
,[strTier4]
,[strTier5]
,[strTier6]
,[sintNameStructureID]
,[strCurrencyCode]
,[decPrice]
,[decWasPrice]
,[strBCCurrencyCode]
,[decBCPrice]
,[decBCWasPrice])
SELECT
[lngCataloguePriceSetItemID]
,[lngCataloguePriceSetID]
,[strItemNo]
,[strTier1]
,[strTier2]
,[strTier3]
,[strTier4]
,[strTier5]
,[strTier6]
,[sintNameStructureID]
,[strCurrencyCode]
,[decPrice]
,[decWasPrice]
,[strBCCurrencyCode]
,[decBCPrice]
,[decBCWasPrice]
FROM TABLEBACKUPS.DBO._mm_20140324_tblBCataloguePriceSetItem
ORDER BY [lngCataloguePriceSetItemID]'
BEGIN TRY
--PRINT (@SQL)
EXECUTE [SQLBOCSSLON1.DEV.BODEN.LOCAL].[bocss2].[dbo].[sp_executesql] @SQL
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
via a LINKED server, [SQLBOCSSLON1.DEV.BODEN.LOCAL]
when I realised the OLEDB WAIT_TYPE going to the roof, so I cancelled this query, copied and paste the script in the REAL server and run it from there.
This is the OLEDB WAIT TYPE:
This is the picture of cancelling the query via linked server:
These are the 2 processes that are currently running on the server. In yellow is the one that I have cancelled. Wait_type is SOS_SCHEDULER_YIELD, but I cannot see any indication (at least not in my query) - that shows me that this is a process that has been cancelled.
How can I achieve that?
The exact query that I am using is here:
how to find the T-SQL of a sleeping session that is blocking some other process?
and here:
how to get the information about sleeping processes that are causing blocking?
