locked
Inserting data into MSSQL from MYSQL Stored Proc. RRS feed

  • Question

  • Hi All,

    Im trying to insert the data that i get from MYSQL SP into SQL server 2005.

    im able to read the data from MYSQL SP using below command but unable to insert the same into a table.

    EXEC (Call <Proc Name>(<Parameters>) AT <Linked Server>)

    but when i use the Openrowset or Openquery it fails any work arounds for this.

    greatly helps

    Thank you


    Jai Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Tuesday, July 10, 2012 8:04 AM

Answers

  • Consider using the OLE DB provider for MySQL rather than using OLE DB over ODBC. Not I am sure that this will help in this case, but it is one less component involved.

    Had you been on SQL 2008, you could have configured the linked server not to enlist in a distributed transaction, but this option is not available with SQL 2005. Although you could try adding "Enlist=false" in the @provstr argument to sp_addlinkedserver.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Wednesday, July 11, 2012 2:29 AM
    • Marked as answer by Iric Wen Thursday, July 19, 2012 8:42 AM
    Tuesday, July 10, 2012 9:31 AM
  • This isn't going to work as such. When using OpenQuery or Exec, no metadata is actually returned, which would be needed to populate the table you are wanting to insert into.

    For instance, if I were to do this:

    select * from OPENQUERY(LINKSVR,'call SCHEMA.RETSALES')

    I get an error:

    The OLE DB provider "<>" for linked server "LINKSVR" indicates that either the object has no columns or the current user does not have permissions on that object.

    Doing similar to this will work:

    create table #SALESTABLE (PRODID int, ORDYEAR char(4), ORDMONTH char(3), QTY smallint, AMOUNT decimal(9,2))
    insert into #SALESTABLE
    	select * from OPENQUERY(LINKSVR,'SELECT * FROM SALES')
    select * from #SALESTABLE
    drop table #SALESTABLE
    go

    Your mileage may vary.


    Charles Ezzell - MSFT

    • Marked as answer by Iric Wen Thursday, July 19, 2012 8:42 AM
    Wednesday, July 11, 2012 12:59 PM

All replies

  • INSERT INTO sqlserver_tbl EXEC (Call <Proc Name>(<Parameters>) AT <Linked Server>)

    PS. Make sure that numbers of columns in MySQL should be matched to number of column in SQL Server table.


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Tuesday, July 10, 2012 8:09 AM
    Answerer
  • Hi Uri,

    yes the columns are same as that of in mysql below is the error message im getting

    OLE DB provider "MSDASQL" for

    linked server "Macaroni" returned message "[MySQL][ODBC 5.1 Driver]Optional feature not supported".

    Msg 7391, Level 16, State 2, Line 1

    The operation could not be performed because OLE DB provider

    "MSDASQL" for linked server "Macaroni" was unable to begin a distributed transaction.


    Jai Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Tuesday, July 10, 2012 8:32 AM
  • You said that you can read the data from MySQL being on  SQL Server am I right?

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Tuesday, July 10, 2012 8:39 AM
    Answerer
  • Yes i can read it when i just execute "EXEC (Call <Proc Name>(<Parameters>) AT <Linked Server>)"

    but it throws an error when i use  "

    INSERT INTO sqlserver_tbl EXEC (Call <Proc Name>(<Parameters>) AT <Linked Server>)

    "


    Jai Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Tuesday, July 10, 2012 8:42 AM
  • Consider using the OLE DB provider for MySQL rather than using OLE DB over ODBC. Not I am sure that this will help in this case, but it is one less component involved.

    Had you been on SQL 2008, you could have configured the linked server not to enlist in a distributed transaction, but this option is not available with SQL 2005. Although you could try adding "Enlist=false" in the @provstr argument to sp_addlinkedserver.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Wednesday, July 11, 2012 2:29 AM
    • Marked as answer by Iric Wen Thursday, July 19, 2012 8:42 AM
    Tuesday, July 10, 2012 9:31 AM
  • This isn't going to work as such. When using OpenQuery or Exec, no metadata is actually returned, which would be needed to populate the table you are wanting to insert into.

    For instance, if I were to do this:

    select * from OPENQUERY(LINKSVR,'call SCHEMA.RETSALES')

    I get an error:

    The OLE DB provider "<>" for linked server "LINKSVR" indicates that either the object has no columns or the current user does not have permissions on that object.

    Doing similar to this will work:

    create table #SALESTABLE (PRODID int, ORDYEAR char(4), ORDMONTH char(3), QTY smallint, AMOUNT decimal(9,2))
    insert into #SALESTABLE
    	select * from OPENQUERY(LINKSVR,'SELECT * FROM SALES')
    select * from #SALESTABLE
    drop table #SALESTABLE
    go

    Your mileage may vary.


    Charles Ezzell - MSFT

    • Marked as answer by Iric Wen Thursday, July 19, 2012 8:42 AM
    Wednesday, July 11, 2012 12:59 PM