How to access data from external MS SQL database tables in a Drupal site running on MySQL I am currently working a academic medical department that uses a lot of MS SQL databases because faculty set them up - making us the maintainers :(

We also recently launched Drupal 6 site on LAMP, with the goal to move from what was supposed to be a .NET shop to PHP and MySQL ... go open-source!

My problem: Fetch data from external MS SQL database tables into Drupal which runs on MySQL, more specifically integrating Views 2 with this external data and dynamically show it on the site.

What I have found out so far:


People have tackled this problem since Drupal 4, but no one is owning it and so ... no stable solution.
Drupal can only run one type of db driver per site. Either MySQL/PostgreSQL/etc ... given you have the driver.
The MS SQL drivers that are there are not stable and still only solve the problem if my site would already run on MS SQL.
The are interesting modules that rely on the schema module to build table definitions and enable access to external MySQL data. (Table Wizard, Data API)


Right now: Looking into how the Data API module can be used for my purpose by creating a plugin for it for instance.

Any thoughts that would help me in how to access external MS SQL tables would be very appreciated.
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.