The query was running a Stored procedure that returned a small subset of data, It would run perfectly fine on its own but as soon as it ran with the additional task of adding the data into a temp table it blew up with the error
The partner transaction manager has disabled its support for remote/network transactions. and followed up with The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "[SERVERNAME]" was unable to begin a distributed transaction.
Odd.
I checked the configuration of the new servers and MS DTC was configured in the exact same way from the old ones but for some reason SQL 2012 seemed to be dealing with it differently (or at least the linked server provider was).
After I amended the local DTC config to accept network DTC access and for remote clients(inbound and outbound) all was well.