I would guess one or maybe both of these indexes would fix your problem:

 CREATE INDEX IX_c_b_party_comm_1 
     ON dbo.c_b_party_comm(ROWID_OBJECT, COMM_TYP_CD) 
INCLUDE (COMM_TYP_CD,...

Okay I am not understanding your comment "I have a feeling it is taking it literally as a string" -- how else should it take it? I mean you...

try:

 like '%' + p.forenames + '%'

So it it the case that there is an AD account that you can remotely connect but the SQL login cannot remotely connect?

If it works when they remote to the host...

I've got no idea but ChatGPT suggested:

Error 18456 is a common error in Microsoft SQL Server, indicating that a login attempt has failed due to invalid login credentials or insufficient...

You can print the SQL statement instead of executing it and then run it manually.

Someone like Paul White could answer...

Why does creating and inserting data into a table with insert into cause blocking but inserting into an existing table not cause blocking?

I still don't understand why an AWS server that costs more per month to rent than the entire cost of buying my laptop new should take 10 times...

You and I have know each other for...

I think you might want this for a recursive CTE:

;with cte as
(
    select * 
      from [#ChannelInfo] 
 ...

When I said "we", I should have said "they".  What...

drop table if exists #temp;

select *
  into #temp
  from (values (1,21.5,1,20.5, cast('20230121' as date)),
           ...

I can't see how you can order the data in that table?

Can you provide how the table looks before and after the update?