Mysql – How to do an “insert” statement from MS SQL -> MYSQL using linked servers

MySQLsql-server-2005

Is it possible to do an "insert" statement through linked servers. I know it is possible by using MSDTC…but does this work between MS SQL and MYSQL? Any help would be greatly appreciated. As of right now…I can update and select between the 2 databases but it gives me an error when I try to run an insert statement.

OLE DB provider "MSDASQL" for linked server "**************" returned message "Query-based insertion or updating of BLOB values is not supported.".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "MSDASQL" for linked server "**************" could not INSERT INTO table "[*******************]...[**************_options]".
Location: memilb.cpp:1493
Expression: (*ppilb)->m_cRef == 0
SPID: 76
Process ID: 1644

Best Answer

So long as the permissions are set up properly you should be able to an INSERT just fine. Make sure that the user context that is connecting through the linked server to MySQL has the proper permissions on the table in your MySQL server.

To see the security settings for the linked server

  • Expand Server Objects in SQL Server Management Studio's Object Explorer
  • Expand Linked Servers, right-click on the linked server in question and get properties.
  • Click Security on the Linked Server Properties page and verify the security mappings.
Related Topic