check the SQLAgent.out file on your server as well as the ERRORLOG file of the SQL Server !

To figure out which CHAR s are in the string ... check this thread https://www.sqlservercentral.com/forums/topic/how-to-remove-special-chars#post-4157818

You should ask the author.

There is no benefit writing it like that, could even be a performance downside due to the potential less optimal plan due to the calculation/conversion that...

...
AND ( 
          (D.CLS LIKE '%'+ U.IND_TYPE '%'
   AND D.CLS_OPERATOR = 'IN'
   )
 ...

We use extended events to capture that data.

ref: https://www.brentozar.com/archive/2015/12/tracking-tempdb-growth-using-extended-events/

100 results is incorrect !

due to your query:

SELECT Description + ' ' FROM RawDataDescriptions FOR XML PATH(''))

it produces 1 row !

test it with the data of  Steves...

Just to get you back on track ...

How many rows do you expect from your nested select(s) ?

SELECT Description + ' ' FROM RawDataDescriptions FOR XML PATH(''))...

Have a look at Powershell module DBATools - Copy-DBALogin

Did you try to run this NOT using MERGE ?

ref: https://michaeljswart.com/2021/08/what-to-avoid-if-you-want-to-use-merge/

Did you check the "Restore ... with standby" option ?

BOL ref: RESTORE ... With Standby

Default trace holds the information you seek ! ( as long as it did not get cleaned up after a number of rolled forward for the xe files )

Have a look at Copy-DBATable of the ( free ) DBATools module in Powershell

same as with an on-prem Sql server instance

I've been testing a bit to detect such queries at the linked server itself ...

For me, it turns out the only actual way to detect it is when you have...

Start an appropriate XE session to monitor that sproc's execution

You need to debug this sproc, because it will hit some error somewhere and may not hand it over to the...