Connecting to MySQL server

• mrhodes2

  SSC Journeyman

  Points: 80

  More actions

  May 28, 2003 at 12:13 pm

  #64220

  I'm trying to connect to MySQL to move some data from SQL2000 to MySQL. I need to do this nightly so a wanted to write a script to execute every night. I setup a linked server (portal), and I can click on tables and see all the tables on the MySQL server. My problem is accessing the server via TSQL. I've tried portal.pn.mrhodes.nuke_users, portal..nuke_users,portal.pn..nuke_users and just about every other combination I can think of. Has anyone linked to a MySQL database successfully. I'm user MySQL ODBC 3.5.1 drivers. Anything else you need or want to know, please let me know.

• K. Brian Kelley

  SSC Guru

  Points: 114704

  More actions

  May 29, 2003 at 7:36 am

  #458993

  Use OPENQUERY. I don't believe the MyODBC driver exposes the proper interfaces for a 4-part naming convention. As an example:

  
  
  SELECT *
  
  FROM OPENQUERY(portal, 'SELECT * FROM nuke_users')
  
  

  K. Brian Kelley

  http://www.truthsolutions.com/

  Author: Start to Finish Guide to SQL Server Performance Monitoring

  http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

  K. Brian Kelley
  @kbriankelley