How to access data from external MS SQL database tables in a Drupal site running on MySQL
As pointed in the question, Views is currently unable to do it. It relies on Drupal's DB Layer which is currently unable to query different DB engines and doesn't support MS-SQL. Views 3 (currently in alpha) provides pluggable query backend so it must be possible to write a backend on top of PDO ODBC or an other mean to reach the MS-SQL DB. Queryin the MS-SQL DB from PHP will probably be a performance issue. Views' caching will certainly be needed to avoid unnecessary repeated queries to the server.

In order to work around this and avoid diving into Views 3, you may use Views (2) to query the Drupal's database, or another one on the same engine. And simply import the needed data into this database. This is what I recently did on a project with a small amount of daily updated data. I used PDO ODBC (with unixODBC and FreeTDS) to periodically import data from a MS SQL Server database into MySQL tables. The tables were then exposed to Views using Views' API. The Data module could also have been used for this purpose.

Another option may be to import the data from MS-SQL as Drupal's nodes. If the data are imported as nodes using a custom content-type with the needed fields (using CCK), you get Views integration as well as the tons of existing modules to leverage Drupal's nodes.

One easy solution to import external data into Drupal is the Feeds module. While initialy build as a rewrite of the Feed API to import RSS and Atom feeds, Feeds is actually a powerful framework to import data into Drupal. It can easily be extended through plugins and hooks to support different data sources, formats and processors.