Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Posts
    4

    Red face Unanswered: 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 guys
    Last edited by nfranklin; 11-25-02 at 04:12.

  2. #2
    Join Date
    Nov 2002
    Posts
    14

    Re: Linking Server to MySql

    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.
    Zlatko Michailov
    Z-SQL

  3. #3
    Join Date
    Nov 2002
    Posts
    4

    Re: Linking Server to MySql

    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'

  4. #4
    Join Date
    Nov 2002
    Posts
    14

    Re: Linking Server to MySql

    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
    Zlatko Michailov
    Z-SQL

  5. #5
    Join Date
    Nov 2002
    Posts
    4

    Re: Linking Server to MySql

    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

  6. #6
    Join Date
    Nov 2003
    Posts
    2

    same problem

    i have exactly same problem, do you have some solution?

  7. #7
    Join Date
    Oct 2003
    Posts
    3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •