Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    2

    Exclamation Unanswered: MySQL linked in SQLServer

    Hallo! I'm trying to link a MySql 's DB in SQLServer with ODBC: the operation was susseful but I'm not able to read and write/update the data.
    HELP ME PLEASE!!!
    silvia, Italy

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm confused by "the operation was successful" followed by "I'm not able to read and write/update the data". I don't understand what you mean.

    Check out sp_addlinkedserver. If you tinker with the link a bit, you ought to be able to get it in Italian if that helps.

    The quickest and easiest way is to use the ODBC link that uses a system DSN. That way if you change MySQL servers all you do is fix the DSN and your SQL Server link works.

    A more complex but more powerful way is to use the ODBC with a connection string. It is kind of like a car with a manual transmission... You have more control, but you have to pay more attention to how you use it.

    -PatP

  3. #3
    Join Date
    Oct 2004
    Posts
    2
    IF I RIGHT CLICK IN A TABLE IN THE REMOTE SERVER, I FIND ONLY "COPY" AND "?". IT'S CORRECT????

    ciao e molte grazie!!!
    silvia

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without knowing a lot about your system(s), I can only guess. What you describe is one possible outcome of a linked server to MySQL that is actually fairly common.

    There are two problems that commonly interfere with cross-server operations.

    The most common problem is that the MySQL login used by the ODBC connection doesn't have sufficient permissions within MySQL. The only real solution for that problem is to get more permissions by either changing the MySQL login used by the ODBC connection to one that has more permissions, or by getting the MySQL administrator to grant the MySQL login used by the ODBC connection more permissions.

    The next most common problem is that the MySQL ODBC driver on the SQL Server itself is old. The ODBC drivers have improved a lot over time. Older drivers (sometimes even a few months can make a real difference) are often a problem. Visit the MySQL site to see if you can get newer ODBC drivers.

    -PatP

  5. #5
    Join Date
    Apr 2006
    Posts
    2
    I have set up a link server to MySQL 5

    Sometimes mySQL do require a password. The following link server works for me.

    EXEC sp_addlinkedserver ‘mysqlDB’, ‘MySQL’, ‘MSDASQL’, Null, Null, ‘Driver={MySQL ODBC 3.51 Driver};DB=mysql_db;SERVER=mysql_server;uid=userna me;pwd=password’

    If MSDTC is running you can use the following to create a view without a link server.
    SELECT * FROM OPENROWSET(’MSDASQL’,'Driver={MySQL ODBC 3.51 Driver};DB=mysql_db;SERVER=mysql_server;uid=userna me;pwd=password’, ‘SELECT * from table’)


    If you get an error like
    OLE DB provider 'MSDASQL' returned an unexpected data length for the fixed-length column '[MSDASQL].pensionNo'. The expected data length is 7, while the returned data length is 5.

    You can resolve this using the option tag
    'Driver={MySQL ODBC 3.51 Driver};DB=mysql_db;SERVER=mysql_server;option=512 ;uid=username;pwd=password'

    Notice the option=512

Posting Permissions

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