Error when using views on a linked server
This is a bug, though one that only arises in very specific circumstances.

SQL Server uses a component called Distributed Query (DQ) to build T-SQL commands to run against the linked server. The optimizer transforms a CASE expression including a sub-select to a form that works perfectly well on local objects, but which is not translated correctly to remote query form (in this particular circumstance).

The bug results in a remote node being generated that requires the result of local computations. An internal query tree is created that expects these computed values as parameters, but no parameters are generated. This bug only occurs if the sub-selects are not correlated to the outer query (as is the case here), because a simplification that is only safe for a local query is applied.

DQ generates the remote query with parameters (using standard ? syntax), but no parameters are passed, causing the "statement cannot be prepared" error (a prepared statement is the technical name for a parameterized statement).

If subquery contained an outer reference (correlation) instead of literal values, the local-only simplification would not be applied, and the query tree would be correct.

Reproduction

Create a linked server SQL2008 to another instance of SQL Server 2008, which contains the AdventureWorks sample database. Create the following views on the local instance:

CREATE VIEW dbo.Product AS
SELECT * 
FROM SQL2008.AdventureWorks.Production.Product AS P;
GO
CREATE VIEW dbo.TransactionHistory AS
SELECT *
FROM SQL2008.AdventureWorks.Production.TransactionHistory AS TH;


Generate an estimated execution plan for the following query (running it will just produce the error message, of course):

SELECT 
    P.ProductID 
FROM dbo.Product AS P
WHERE P.ProductID IN
(
    SELECT -- Comment this
        CASE 
            WHEN EXISTS 
                (
                    SELECT * 
                    FROM dbo.TransactionHistory AS TH 
                    WHERE TH.ProductID = 1 -- Not correlated
                )
            THEN 
                (
                    SELECT TOP (1) 
                        TH.TransactionID 
                    FROM dbo.TransactionHistory AS TH 
                    WHERE 
                         -- Not correlated
                        TH.ProductID = 1
                    ORDER BY 
                        TH.TransactionID DESC
                )
            ELSE 0
        END
);


The estimated query plan below highlights the problem operator:



The Remote Query property of that operator is:

SELECT "Tbl1001"."ProductID" "Col1019" 
FROM "AdventureWorks"."Production"."Product" "Tbl1001" 
WHERE CASE WHEN ? THEN ? ELSE (0) END = "Tbl1001"."ProductID"


Notice the ? parameter markers. The unresolved parameters generate the second error message when the remote server tries to compile WHEN ? THEN ?.

If you comment out the marked SELECT, the transformation in question is not applied, and the erroneous remote query is not generated.

Equally, replacing either of the TH.ProductID = 1 instances with a correlation, for example TH.ProductID = P.ProductID also generates correct remote queries.

This bug reproduces in all current versions of SQL Server, up to and including SQL Server 2014 RTM CU4.