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.