Might be a dumb question, but does that need to be dynamic SQL?

My thoughts here are to take your last 2 IF statements and have them assign a value to...

Pretty sure you are going to need to configure that per-server.  The servers don't share any configuration information, so you would need to set that on on a per-database level.

Alternately,...

I think that it should work just fine.  I have used most versions of VS from 2010 through 2019 to build reports for various versions of SSRS (2008 R2 through...

I would expect that to be network related or memory related (experts correct me if I am way out to left field here).

If the network connection on the server is...

I would check the things that the error mentioned such as "low disk condition".  Do you have free space on the disk and in the database?

Next, I'd be checking the...

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,...