I found this at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataOleDbOleDbCommandClassPrepareTopic.asp

Creates a prepared (or compiled) version of the command on the data source.

If the CommandType property is set to TableDirect, Prepare does nothing. If CommandType is set to StoredProcedure, the call to Prepare should succeed, although it may result in a no-op.

Before you call Prepare, specify the data type of each parameter in the statement to be prepared. For each parameter that has a variable length data type, you must set the Size property to the maximum size needed. Prepare returns an error if these conditions are not met.

If you call an Execute method after calling Prepare, any parameter value that is larger than the value specified by the Size property is automatically truncated to the original specified size of the parameter, and no truncation errors are returned.

Output parameters (whether prepared or not) must have a user-specified data type. If you specify a variable length data type, you must also specify the maximum Size.

According to it - Sprocs can be prepared?
But is this only on the Dataprovider level not on the database? And so i guess it will not work on SQL Server.

Are SQL Server Sproc's prepared or not? According to the documentation it is not. What will happen if you call prepare on a command?....Nothing?

But the Online SQL books say...

Stored Procedures and Execution Plans In SQL Server version 6.5 and earlier, stored procedures were a way to partially precompile an execution plan. At the time the stored procedure was created, a partially compiled execution plan was stored in a system table. Executing a stored procedure was more efficient than executing an SQL statement because SQL Server did not have to compile an execution plan completely, it only had to finish optimizing the stored plan for the procedure. Also, the fully compiled execution plan for the stored procedure was retained in the SQL Server procedure cache, meaning that subsequent executions of the stored procedure could use the precompiled execution plan.

SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans. The database engine uses an efficient algorithm for comparing new Transact-SQL statements with the Transact-SQL statements of existing execution plans. If the database engine determines that a new Transact-SQL statement matches the Transact-SQL statement of an existing execution plan, it reuses the plan. This reduces the relative performance benefit of precompiling stored procedures by extending execution plan reuse to all SQL statements.

SQL Server 2000 and SQL Server version 7.0 offer new alternatives for processing SQL statements. For more information, see Query Processor Architecture.

So is there a difference between "prepare" and "Execution Plan Caching"

Is "Execution Plan Caching" only a MSSQL thing?

I have also posted this at: http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

Prepare is very vague, however it's just this: a call to the DB is made to read the parameter properties. Internally the driver will optimize the query so it can send the query faster to the DB. At least this is what the ADO prepare statement does in OleDb. I used it in the AS400 driver to retrieve parameter info.

the extra roundtrip is not worth the effort.

It has nothing to do with execution plans. Execution plans are stored in sqlserver, prepare just prepares the driver for a faster execution of a query.