Linked MySQL server will show tables but not columns

• nmjohnson001

  Valued Member

  Points: 68

  More actions

  April 8, 2016 at 12:42 am

  #326318

  I have successfully (finally!) linked a MySQL server to my SQL Server 2008. The configured server (minus the security and options) is:

  EXEC master.dbo.sp_addlinkedserver @server = N'mySQLHDL', @srvproduct=N'MySQLHDL', @provider=N'MSDASQL', @datasrc=N'MySQLHDL', @provstr=N'driver={MySQL ODBC 5.3 Unicode Driver}'

  When I run the RPC check suggested in another thread I Get:

  innodb_version5.7.11

  protocol_version10

  slave_type_conversions

  tls_versionTLSv1,TLSv1.1

  version5.7.11-log

  version_commentMySQL Community Server (GPL)

  version_compile_machinex86_64

  version_compile_osWin64

  When I click on the Catalogs I can see the all of the tables in the linked database. However, clicking on a table to expand the columns gets me nothing, and trying to generate a select statement shows the message:

  -- [mySQLHDL].[hdl]..[grecondmr] contains no columns that can be selected or the current user does not have permissions on that object.

  I am using the root account that I created on the other system, with the same username or password. The tables are from a snapshot of the production database and all of them have columns and data.

  The ODBC Connector is the latest from Oracle: MySQL Connector/ODBC 5.3. I using SQL Server Management Studio 2012.

  Any ideas on what is not configured correctly here?

  I am trying to do this manual connection because the SQL Server MySQL Migration Assistant crashes part way through loading the data, so I am reduced to try and do this manually.

  Thanks, Neil