I've been trying to use MySQL as a data source for a SQL Server 2000 linked server. To make this work, the metadata has to be correct when using the ADO openschema method. I test this using a bit of vbscript. Having discounted the ODBC drivers since neither returns a columns list to OLEDB Provider for ODBC (one of the required schemata), I've been looking at MyOLEDB.
I wonder how anyone manages with this? It reports support for the adSchemaCatalog schema, even though no catalog is returned (in MySQl speak, this is the database name). It reports support for the adSchemaSchemata schema, even though the only schema returned is the fixed value 'INFORMATION_SCHEMA', regardless of who created the objects.
In case anyone else wants to do the same as me, set the OLEDB provider 'level zero only' attribute to true, and use a 4 part name ignoring the catalog and schema. This rather limits the functionality, but so long as your MySQL tables are small you should be OK.
I wonder how anyone manages with this? It reports support for the adSchemaCatalog schema, even though no catalog is returned (in MySQl speak, this is the database name). It reports support for the adSchemaSchemata schema, even though the only schema returned is the fixed value 'INFORMATION_SCHEMA', regardless of who created the objects.
In case anyone else wants to do the same as me, set the OLEDB provider 'level zero only' attribute to true, and use a 4 part name ignoring the catalog and schema. This rather limits the functionality, but so long as your MySQL tables are small you should be OK.