This is the same issue as seen with linked servers - SQL Server can parameterize the query where the values are hard-coded but cannot do the same for variables.
This isn't a driver issue - rather it is an issue with how external (linked or remote) queries will be processed. In the first one, SQL Server takes the hard-coded values - parameterizes them - and sends a parameterized query to the external system.
In the second one - SQL Server does not parameterize the query...which results in pulling all of the data from the external system and then filtering using the local variable.
Again - this is seen across linked servers and now external tables (which probably use the same methodology as a linked server). The only way I know of to get around this is to either:
- Call a stored procedure on the external system with appropriate parameters
- Build the query dynamically and execute the dynamic query
- Build the query using dynamic SQL and OPENQUERY to force execution on the external system instead of locally.
You can also see additional issues as soon as you try to join the external table to a local table. Doing this will often force SQL Server to pull more data locally than is needed - or forces a cursor over the external data.