Take the 2-minute tour ×
Server Fault is a question and answer site for professional 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
add comment

2 Answers

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
    
Do you know which security options should be true/false. Right now the configuration is as follows: Collation compatible: False, Data access:True, RPC: False, RPC Out:true, Use remote collation: True, collation name: blank,...the rest are defaults...0,0,false,false,false, false,false –  bvandrunen Mar 2 '10 at 20:25
    
You're looking at the Server Options information on the linked server. Instead you want to look at the Security Options (the link right above Server Options in the left-hand pane). This is where all of the user mapping between SQL and the target server is configured. –  squillman Mar 2 '10 at 20:31
    
You lost me...haha...sry...about "Server Objects" or about "Server Options" - I can't find a "server options" tab –  bvandrunen Mar 2 '10 at 20:39
    
@bvandrunen: in the top-left corner of the Linked Server Properties window you should see (under Select a page): General, Security, Server Options. Click on Security. –  squillman Mar 2 '10 at 20:57
    
yeah..that is where I was from your first post...those are the setting that I gave you in my first comment. Do you know which one of those settings would allow me to "insert". As far as I know...changing any of these settings haven't allowed me to do "insert" statements. I have tried changing them between true/false...thanks for your help so far –  bvandrunen Mar 2 '10 at 21:35
add comment
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
add comment

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.