Search from MY SQL AND MS SQL at the same time?

[cloudy]

hi all, i got a project that have existing database which is using the MySQL. but now this project need to do a new version and MS SQL is used as database for the new version. so the old data is remain store in the my sql but the new data will store into the new DB(MS SQL). i got a problem here, do u think when i do the searching function, it can search the old data from the MY SQL DB, and the new data from the MS SQL DB at the same time? who can share with me about this knowledge? thank you very much.

[guelphdad]

it could search them at the same time but you would have two separate connections to two separate databases and you'd have to pass two separate search terms.

If you have to use MSSQL now then I'd dump the mysql data and import that into the MSSQL tables.

[Old Pedant]

Ummm...not strictly true, Guelph.

You *can* treat a MySQL table as if it were a SQL Server table, if you use T-SQL.

You can either set up a linked server or you can even do it Ad Hoc.

Look here:
http://msdn.microsoft.com/en-us/library/aa258869(SQL.80).aspx
and then here:
http://msdn.microsoft.com/en-us/library/aa276845(SQL.80).aspx
and then here:
http://msdn.microsoft.com/en-us/library/aa259589(SQL.80).aspx

I've never done this with MySQL, but I *have* used OPENDATASOURCE to treat an Access DB's table as if it were a SQL Server table.

Note that for MySQL you'll use an ODBC data source, so presumably MyODBC will be the driver of choice.