Linking Server to MySql

Hello All,
I am stuck with this really frustrating problem...

I am using a linked server to run a qeury to mysql with sp_addlinked server etc...

The problem i am having is I am using this in a trigger with sql server 2000 and it always errors there for the row doesnt get inserted.

the error is
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'insert into mail_relay(ip_addr,timestamp) values('myip',1039087122)'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.

With mysql I cant use 4 part names cause I get this error
Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.

So I have to run
select * from openquery(MailServer,'insert into mail_relay(ip_addr,timestamp) values(''myip'',1039087122)')

and mysql also doesnt support multiple queries in the one statement so i cant do this to stop the error:
select * from openquery(MailServer,'insert into mail_relay(ip_addr,timestamp) values(''myip'',1039087122);select null')

so my question is,
I want to be able to stop the error message being reported in my trigger so the row still gets inserted

I am sure it is possible.. I really hope so... any help would be really greatly appreciated

thanks guysCan you execute this insert statement out of a trigger? Does it still fail with the same error?

Could you also list the settings of the link.|||Originally posted by zmichailov
Can you execute this insert statement out of a trigger? Does it still fail with the same error?

Could you also list the settings of the link.

Outside of the trigger the error still occurs...
Here is a the way i set the connection up...

EXEC sp_addlinkedserver
'MailServer',
'',
'MSDASQL',
NULL,
NULL,
'DSN=MailServer;UID=myuser;pwd=mypass;'
EXEC sp_addlinkedsrvlogin
@.rmtsrvname='MailServer',
@.useself=false,
@.locallogin=null,
@.rmtuser='myuser',
@.rmtpassword='mypass'
Exec sp_serveroption 'MailServer', 'data access', 'true'|||Two more questions:
1. What is the defualt database (catalog) for the remote login? Does MySQL have only one database per server?

2. What happens when you execute this statement out of any trigger and transaction:
select *
from MailServer...mail_relay|||Originally posted by zmichailov
Two more questions:
1. What is the defualt database (catalog) for the remote login? Does MySQL have only one database per server?

2. What happens when you execute this statement out of any trigger and transaction:
select *
from MailServer...mail_relay

Hey, Sorry for delay in reply,
in answer to these questions

1. The default database on the DSN is vpopmail, I am using the latest MyODBC driver for this...
In mySql you can have as many dbs as your want to on any server.

2. i get the following error message
Server: Msg 7313, Level 16, State 1, Line 17
Invalid schema or catalog specified for provider 'MSDASQL'.

i also found MyOLEDB ole db provider but It would create an instanmce of it it just errors saying

Server: Msg 7302, Level 16, State 1, Line 17
Could not create an instance of OLE DB provider 'MySQLProv'.

Well any help would be great, thanks again|||i have exactly same problem, do you have some solution?|||This may help.

http://bside.typepad.com/lifebsideben/2003/08/mysql_mssql_lin.htmlsql