January 16, 2013 at 12:34 pm
Thank you for your help. I'm sure that I'm failing to express some key piece of information that matters here.
I will try the tracing as you suggested. If I find anything that I think is important I will post it.
Thanks again.
Best Regards,
Derik Hammer
www.sqlhammer.com
January 17, 2013 at 1:51 pm
Can you SELECT from the MySQL tables using the MS SQL Linked Server?
There's an old, long thread here at SSC http://www.sqlservercentral.com/Forums/Topic340912-146-1.aspx that has some recently added information.
In particular, I was able to SELECT from MySQL tables using a linked server, but I couldn't UPDATE them. Though I received a different error than the one you posted, the solution I found may help you. Here's a direct link to my solution on that thread: http://www.sqlservercentral.com/Forums/Topic340912-146-12.aspx#bm1335431
You might try changing that option in the Cursors/Results tab of the ODBC connector and see what happens.
HTH,
Rich
January 17, 2013 at 2:24 pm
Talking of sillier things dealing with MYSQL. Only Yesterday I had a Linked server that would work if the Linked server and the DSN name were in CAPS. Maybe it was something else but I had other Linked servers to the same MySql Server (different databases) that were working fine, I was running the same setups. I have a script for generating the Linked server and clear instructions for the DSN. BUT the only thing that worked for this Linked server was changing to all CAPS.
Also uses the same ODBC driver as you.
Beats me, but there you are.
January 19, 2013 at 8:22 pm
Rich and Shineboy. I tried both of your suggestions and neither worked. Thank you for your attempt, though.
Best Regards,
Derik Hammer
www.sqlhammer.com
January 19, 2013 at 9:05 pm
For anyone who reads this post. I found the solution. I'm not 100% sure what the issues was but it appears to be an issue with data in a particular column. I say this because I was able to get 6 out of 15 rows to delete in a cursor with dynamic SQL and only deleting 1 record at a time.
Then I was able to use the below query to do everything by only selecting the product_id column.
delete OPENQUERY(MYSQL_BFGLOCKER, 'select product_id from product') where product_id IN (SELECT product_id from #Discontinued_Products)
Best Regards,
Derik Hammer
www.sqlhammer.com
February 15, 2013 at 11:06 am
Glad you got it working. If you figure out what data was breaking your query, post back and let us know!
Rich
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply