MySQL as linked server

Question

MySQL as linked server

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

ballenger97 SSC-Addicted Points: 448 More actions · Answer 1

Thanks for trying to help. Unfortunately I cannot install a 32bit driver on a windows 2008 R2 64 windows box successfully. The driver doesn't even show up as an option to create the System DSN. Back to the drawing board...

ballenger97 SSC-Addicted Points: 448 More actions · Answer 2

I suspect the problem is somehow related to this bug

http://bugs.mysql.com/bug.php?id=63386

My version is 5.0.8 enterprise, and this bug was reported in version is 5.1.9

Seems like the MySQL database version just doesn't support what I'm trying to do. Has anyone else success in setting up a MySQL linked server at all with a version similar to what I've posted?

Also noticed this MS bug, EXACTLY the symptoms I'm seeing but with an Oracle ODBC driver instead

http://connect.microsoft.com/SQLServer/feedback/details/187192/openquery-to-linked-server-hangs-leaving-spid-with-open-tran-that-cannot-be-killed-then-templog-ldf-grows-without-limit-requires-sql-server-restart-on-production-servers

agnew-1142997 SSC Enthusiast Points: 190 More actions · Answer 3

wow... you've been digging!!!

our version is 5.1.46-sp1 Enterprise,

fooey.. wish I could have helped more.. I've done ms connects to mysql before, but before this job, never ms-sql-server..

will keep my eyes open, tho..

since our servers are 64-bits, we're using all 64bit s/w..

J.

ballenger97 SSC-Addicted Points: 448 More actions · Answer 4

Thanks for the info, every little bit helps.

At this point I think I'm willing to fork up some $ and resolve this with a 3rd party solution. Assuming that will even work...

agnew-1142997 SSC Enthusiast Points: 190 More actions · Answer 5

in the past, we (about 15 years ago) got a VMS odbc driver for ingres from openlink software.

now, i'm *sure* that there are better products out there. these guys bent over backwards for support, and we used their product for years.. even survived the y2k...

you mileage WILL vary.. 😉

i have no knowledge of any mysql links with them, but was happy with them in the distant past, when the pyramids were new...

let me know how it goes!

ballenger97 SSC-Addicted Points: 448 More actions · Answer 6

Hey, here's some progress, following that MS article I found, it seems that the SPID problem occurs only if the driver is setup to run in process.

Changing the setting @ Server Objects > Linked Servers > Providers > MSDASQL > "Allow inprocess" uncheck this checkbox. Now the SPID issue goes away and I get an actual error message returned! Hurray!

And now for the embarassing part :blush: looks like I've been keying the database name in the provider string wrong. How silly that was to waste this much of my time!!!!

Thanks all for following this with me

ballenger97 SSC-Addicted Points: 448 More actions · Answer 7

Ugh, so I'm not out of the woods yet actually... Still facing pretty much the same problem.

When Allow In Process disabled, Testing the connection works now and returns no error.

But if I try to query anything like

SELECT * FROM OPENQUERY(MYLINKEDSERVER, 'SELECT * FROM SomeTable')

Then I receive this error even though permissions on the security account are all setup correctly

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "MSDASQL" for linked server "SHORETEL_CDR" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "MSDASQL" for linked server "SHORETEL_CDR".

I even went ahead and granted dba for % host on the mysql account. still complaining access denied.

This thread

http://social.msdn.microsoft.com/Forums/en-US/sqlservermigration/thread/446e129b-dc86-4f7f-b217-ffc08d874b31

Seems to suggest that I have to have Allow In Process enabled. Is this true??? I tried re-enabling and then I got the same hang errors I was seeing previously.

EDIT: I noticed that everytime I execute the OPENQUERY from the MSSQL server, a connection is getting spawned on the MySQL server. Still, obviously I'm missing something. Not sure why allow in process would have to be enabled...

ballenger97 SSC-Addicted Points: 448 More actions · Answer 8

Geez, this just is NOT my week. Found another bloody workaround for this issue in this other workaround, but this is getting ridiculous.

So apparently, MSDAINITIALIZE and some other components need some security tweaking in order to run the OLE DB ODBC MySQL driver out of process. I found this great article outlining all the steps in detail.

http://social.technet.microsoft.com/forums/en-US/winservergen/thread/4a94acde-d7dc-496a-9ecf-c1eb04ed83ce/

But low and behold, another hurdle I ran into while walking through this. Just ain't my week. It seems that out-of-the-box, Windows 2008 r2 locks down all the security configuration of the components needed to be modified, which isn't mentioned in the article above resulting in a brick wall. There's a workaround which involves taking Administrator ownership of the reg key back to the Administrators group. This thread outlines the same process while solving a different problem:

http://social.technet.microsoft.com/forums/en-US/winservergen/thread/4a94acde-d7dc-496a-9ecf-c1eb04ed83ce/

I still have to finish the security tweaks, but I wanted to document the 2008 r2 issue before I finish. Once I'm done I'll post the outcome of my efforts.

ballenger97 SSC-Addicted Points: 448 More actions · Answer 9

:crying: Went through the instructions for granting the sql service access to the necessary components, and restarted the sql server. Still not resolved. On a positive note, the error message has now changed:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "MSDASQL" for linked server "SHORETEL_CDR" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "SHORETEL_CDR".

Wow, I can't believe how many bloody issues I'm running into to get this linked server to work... :crazy:

ballenger97 SSC-Addicted Points: 448 More actions · Answer 10

Tried this

SELECT * FROM OPENQUERY(SHORETEL_CDR, 'SELECT 1')

and get the same error even still.

Found this

http://connect.microsoft.com/SQLServer/feedback/details/615000/sql-server-2008-r2-linked-server-to-db2-using-microsoft-oledb-provider-for-odbc-fail

and this

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/f9ddbb1e-b276-47e7-a85a-475c1e8c6793/[/url]

Both of which suggest to turn off prefetch. Tried that with no luck. I will say however, that based on their instructions it sounds like the ODBC driver they're looking at is different than mine because Mine doesn't have just an "Enable prefetch" checkbox on the "security" tab , but instead has a "prefetch from server by [textbox]" option on the "cursors/results" tab. Either way, I tried turning this feature off, recreating my linked server, and it made no difference.

Scott D. Jacobson SSCertifiable Points: 6089 More actions · Answer 11

Would it be totally out of the question to just open a ticket with the MS SQL CSS folks at this point?

ballenger97 SSC-Addicted Points: 448 More actions · Answer 12

In my experiences, they're just going to tell me that the bug exists in the MySQL ODBC driver shoving the ownership back on Oracle. I guess I could try, but I don't expect much of a response from them on this.

In fact, there's already tickets on this subject sitting out there that I've referenced on this thread which pretty much shows that as the result.