Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

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.
share|improve this answer
up vote 0 down vote accepted

In case anyone else wants to know the answer: The problem was with the variable - I was trying to insert into a "longtext" and through the linked server MS SQL did not allow me to update anything larger than a "varchar" Therefore the fix is to:

1) Update and change the column from longtext -> varchar

2) Insert new data

3) Update and change column back from varchar -> longtext

I realize this is a horrible hack and won't work for many people but this worked in my situation. Thanks to Squillman for the communication

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["                  <li><a href=\"//area51.stackexchange.com\" title=\"proposing new sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2015 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a> \r\n                with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2015.3.30.2413\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Server Fault works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class=\"dno\"></div>\r\n    </noscr"], "copyright__copyright__copyright": ["\n\n1) Update and change the column from longtext -> varchar\n\n2) Insert new data\n\n3) Update and change column back from varchar -> longtext\n\nI realize this is a horrible hack and won't work for many people but this worked in my situation. Thanks to Squillman for the communication\n\nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}