EXECUTE AT against Athena via Linked Server and "does not support the required transaction interface"

 TLDR - using EXECUTE AT with Athena via linked server.  I used https://www.mssqltips.com/sqlservertip/6191/query-aws-athena-data-from-sql-server to get it set up and it works!  But there are some issues.

1) Partition your tables in Athena.  Better performance if you have a decent amount of data

2) This means you can't just do "select from <linkedserver>...tablename", as that doesn't tell Athena to do partition elimination

3) Once you're using OPENQUERY, if you run into issues because it doesn't know what to expect, you can use CAST or CONVERT to force metadata (aka put "cast(fielda as varchar(500))" if it thinks the field is only 255 characters.

4) That doesn't help with over varchar(8000).  You need to move to EXECUTE AT instead, but that seems better then OPENQUERY as it appears to just pass the data back - I got rid of my CASTs in the athena query. Plus, dynamic SQL is easier! You will have to enable RPC OUT for the Linked Server.

5) I couldn't do INSERT INTO EXECUTE AT until I disabled Distributed Transactions in that Linked Server.

Update 2021/06/04 - and there's something screwy with EXECUTE AT.  It runs without the CAST statements and doesn't complain , but doesn't appear to save everything - I have a handful of rows (out of 100k, mind you) that don't match up - one of the fields is lopped off. It's the most annoying thing, since my other option is SQL Workbench which WORKS but isn't easily automated.  After futzing with it for a bit, I wound up moving over to a pure Powershell solution - I'll post that in a few.

[Servers] No linked server? Need inserts? No problem!

Linked servers can be handy. But, also occasionally annoying, and not necessarily where you need them. Fortunately, OPENQUERY to the rescue. Yes, you can use it to do an adhoc query from another server. But, you can also use it to do an insert into another server.

Code:

INSERT INTO OPENROWSET('SQLNCLI', 'Server=yourserversname;Trusted_Connection=yes',
 'SELECT * FROM northwind.dbo.targettable') SELECT FieldA, FieldB, FieldC
FROM sourcetable 

2016/05/11 And here's the generic openrowset syntax you have to use to get SQL authentication.  Posting it here since I just blew 30 minutes trying to get it to work; despite what the documentation says, you have to use (at least on SQL 2012 SP3 querying SQL 2008 SP4)

SELECT * FROM 
OPENROWSET('SQLNCLI', 'Server=myserver;UID=mylogin;PWD=mypassword;',
 'select @@version')