Explaining MDAC and TDS Compatibility

As I understand it, SQL Server 2000's Open Data Service (ODS) is compatible with Tabular Data Stream (TDS) 8.0, TDS 7.0, and TDS 4.2 running on client machines; SQL Server 7.0's ODS is compatible with TDS 7.0 and TDS 4.2; and earlier SQL Server releases are compatible with only TDS 4.2. Microsoft Data Access Components (MDAC) 2.6 contains the ODBC driver and OLE DB provider that ship with SQL Server 2000. Therefore, these modules produce TDS 8.0. When MDAC 2.6 communicates with a downlevel SQL Server, does the TDS protocol contain a mechanism (such as the Server Message Block—SMB—protocol) that lets the client and the server negotiate the highest common level of the protocol that both peers support? Then, does MDAC adjust its TDS version down to that level? Documentation merely talks about running the instcat.sql script on downlevel servers to bring stored procedures up to the level of MDAC on the client. So when you run instcat.sql on downlevel servers, MDAC isn't backward-compatible; that is, MDAC doesn't determine that the server is running an earlier version and adjust to the client's TDS level. Why doesn't MDAC 2.6 confuse downlevel servers that aren't compatible with TDS 8.0?

Both of your suppositions are correct and mutually inclusive. MDAC automatically selects the client's highest TDS level, then adjusts its own TDS version to the highest supported level of the server. (MDAC actually tries a Negotiate packet first.) However, some MDAC functions run on the server, and to take advantage of some newer client-side MDAC features, the functions require certain stored procedures that must reside on the server. You must run instcat.sql on the downlevel server so that you can use MDAC's newer features with the older server.