This is an old problem. INSERT...EXEC runs in an implicit local transaction (an INSERT statement must be atomic, remember) and SQL Server attempts to promote the local transaction to a distributed transaction if the procedure call is remote, for obvious reasons.

SQL Server 2008 introduces a new linked server configuration option: remote proc transaction promotion. You call it like this: EXEC sp_serveroption {linked-server-name}, N'remote proc transaction promotion', 'FALSE'; to turn off the transaction-promotion behaviour.

This option is described in detail, though in a different context, in the following blog entry by the SQL Server Programmability Team:

http://blogs.msdn.com/sqlprogrammability/archive/2008/08/22/how-to-create-an-autonomous-transaction-in-sql-server-2008.aspx

I should stress that I have not tested this personally, but I think it should work. The blog entry is an excellent read by the way - and the subject of it is very interesting too.

edit: it is also well documented in Books Online http://technet.microsoft.com/en-us/library/ms178532.aspx

Paul