1

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: enter image description here

This is the picture of cancelling the query via linked server:

enter image description here

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?

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.