How can I prevent the SQL Server Query Optimiser splitting a remote query?
Without seeing the query plans we can't answer in detail. There is likely a query hint you can use to encourage the query planner to use the alternate plan.

Another option is to control it from the other side: create a view in the remote end that does the work and select directly from that at the local side.

Update Once Query Plans Presented:

The warnings in the plan are often good clues, that is why they are there. In this case: Type conversion in expression (CONVERT_IMPLICIT(varchar(20),[NET2-1\NET2].[Net2].[dbo].[Users].[Field14_50],0)) may affect "Cardinality Estimate" in query plan choice.

It would appear that a difference in types between the source [NET2-1\NET2].Net2.dbo.Users.Field14_50 and the target #Net2Data.UserID is causing confusion. Without knowing the source type I suspect it is NVARCHAR(&lt;something&gt;). Perhaps you could add an explicit cast in the SELECT clause or change the temporary tables definition to match so there is no need for conversion at all. Also, if the source column in question is an NVARCHAR make the literal in the filtering clause explicitly a multi-byte string (LIKE N'[a-z][a-z][a-z]%' in place of LIKE '[a-z][a-z][a-z]%').