I'd check the execution plan, not profiler.  You may be able to improve performance with more/less/different indexes or some query hints (like forcing a hash match).

Or, another thought, did you...

Is the service running on the remote server?   The screenshot says "Local" so I wasn't sure...

First thing I'd check is if the remote server has Remote Registry enabled.

If that service...

I think the best way to handle this would be DENSE_RANK().  Something along the lines of:

 SELECT
[SearchParts]
, [CompanyId]
, DENSE_RANK() OVER (ORDER BY searchparts, companyID) AS [GeneratedID]
 ...

Pretty sure you would get those from your S3 server settings (AWS if I remember right).

Worst case, you should be able to generate new ones without any problems (if I...

The likely reason - your certificate isn't issued to the same name as you are using to connect.

For example, SERVERA is not the same name as SERVERA.corp.com but if you...

As an alternate solution, you could have an SSIS package that pulls data from server A and pushes it to server B and you MAY be able to use SSIS...

As far as I know, you cannot access a "RESTORING" database for reads or writes, and this makes sense.

While a database is in the RESTORING state, SQL is waiting for...

NOTE - the below is my opinion; I was not able to find reliable documentation on it, but based on how the settings are set on both the client side...

yep!  That looks like it should work and looks more efficient than my loop approach!  And easier to tune as it isn't 52 loops!

That one looks much nicer than my...

The HEADER table will always have a value for each week  There is never a case where the header table will have a NULL for a week.

In the data table,...

Since you are using a SQL Agent Job for this, in the settings for the SQL Agent Job STEP, you can tell it to output to a file.  You should...

My opinion (I do not have SQL Server on Linux installed), I see no reason why you wouldn't be able to do that.

My expectation as to why it isn't well...

To me, I would start by looking at the process that didn't get killed.  whatever process 143 was doing, it needed an exclusive lock on pec_prod.dbo.claim and an intent exclusive...

What happens if you do that insert on the other server without using the linked server?

The error "Cursor operation conflict" makes me think that there MIGHT be a trigger on...

My 2 cents - if you can't change the query, you only have 2 options to improve performance:

1 - reduce the data set (ie archive data)

2 - indexes

My reading of...