by MyODBC 2.50 (ODBC level 0 with <only some?> level 1 and level 2 features)
driver probably does not conforms to requirements that MS SQL Server needs
to use the "linked_server_name.catalog.schema.object_name" syntax.
It seems that MyODBC driver does not rebuild and pass to applications the
"catalog" or "schema" informations on MySQL databases.
See details in
http://www.veryComputer.com/
bcconformance_levels.asp
I suppose there is no need to upgrade to MySQL-Max or MySQL 4.0 (perhaps
future "stored procedures" will help - see below). I think the problem
reside only in MyODBC.
A related question, that could solve the impossibility of writing data with
OPENQUERY([myLinkedServer], 'SELECT statement') or
OPENROWSET('MSDASQL',
'DRIVER={MySQL};DB=myDB;SERVER=mySQLServer;uid=myUID;pwd=myPWD', 'SELECT
statement') functions, is the chance of submit more than a SQL statement to
MyODBC driver.
In that way we can do UPDATE/INSERT, fooling OPENQUERY, that need a rowset
returning from mySQL Server. For example:
OPENQUERY([myLinkedServer], 'SELECT statement; INSERT statement').
See details in
http://www.veryComputer.com/
Again, I think the problem reside only in MyODBC.
Some other ideas ??
Regards.
Pierfrancesco Consolo
> Inviato: venerd 2 novembre 2001 10.19
> Oggetto: MySQL as Linked server in MSSQL using myODBC.
> Hello!
> I've been trying to access a mysql server on a Linux machine via a SQL
> Server 2000 running on NT. The setup further consists of myODBC on the
> NT machine providing the necessary connectivity. Using myODBC directly
> to run queries on the linux machine works great, but I have so far
> failed when trying to access this server as a linked server in mssql.
> Issuing queries using openquery works fine but issuing direct queries
> such as:
> 'select * from myLinkedServer...myTable;'
> fails with the following message:
> "Error: Invalid schema or catalog specified for provider 'MSDASQL' ..."
> The settings for the linked server properties are:
> Product Name = 'myLinkedServer'
> Data source = 'myLinkedServer'
> Provider String = 'dsn=myLinkedServerDSN;uid=myUID;pwd=myPWD;
> Location = ''
> Catalog = ''
> Trying to enter anything in the catalog field leads to 'Enterprise
> Manager'*.
> Any ideas as to how to solve this puzzle?
> Cheers!
> Aleksander Solheim
Please check "http://www.veryComputer.com/; before
To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,