July 30, 2008 at 2:57 pm
Hello All,
I am in the process of finishing the migration our SQL Server 2005 from a Windows 2003 32-bit machine to a Windows 2008 64-Bit machine. Despite the 64-bit O/S on the server, we have the 32-bit version of SQL Server 2005 installed. I haven't made the decision on whether to upgrade it to 64-bit yet.
I am now trying to re-create the linked server to our MySQL databases, but am not having much luck.
I've downloaded and installed the ODBCB driver from MySQL (version 5.1.4 winX64), and have successfully created the System DNS entry. When I test the connection from the ODBC driver GUI, it connects with no problem.
However, when I try to create the linked server in SSMS, I get the following error message:
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MYSQL_MyServerName".
OLE DB provider "MSDASQL" for linked server "MYSQL_MyServerName" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (.Net SqlClient Data Provider).
I've followed all of the steps that were documented so well here, and which worked nicely for me with the 32-bit 3.51.23 version of the ODBC driver.
If anyone can offer any suggestions, I'd be extremely grateful.
Thanks,
Simon Doubt
July 31, 2008 at 9:48 am
Just wanted to post a reply to my own previous question, in case anyone else is following the thread, or runs into the same trouble in the future.
I upgraded the SQL Server 2005 installation from 32-bit to X64, as it's being hosted on the 64-bit version of Windows 2008. With the 64-bit version of the MySQL ODBC driver installed (version 5.1.4), I am now successfully able to create a linked server in SSMS and connect to my MySQL database.
Thanks again to the original poster of this topic for the great instructions.
Regards,
Simon Doubt
August 4, 2008 at 11:30 am
64-Bit OLEDB Provider for ODBC (MSDASQL) for Windows 2003 is now available for download. Here is the link:
August 28, 2008 at 7:30 am
This thread did a great help for me in configuring linked server to MySQL. Thanks a lot for that. For a newbie screen shot may be helpful. I documented the whole process with screen shots and posted in my blog. If anyone feels like need a supportive screen shots for this steps you may refer http://madhuottapalam.blogspot.com/2008/08/configuring-linked-server-from-sql.html .
thanks once again Jim Dillon
Madhu
October 8, 2008 at 5:37 pm
Thanks to Jim for this, it helped me out greatly.
And thanks to Wendy for her dynamic SQL example -- this solved a major problem for me as well, namely being able to call a MySQL-specific function on a linked server & use the result.
On a side note, has anyone been able to successfully call a MySQL sproc thru a linked server connection? Have been banging my head against a wall on this for a while. One thing I can try for this given my sitch is to call it thru the MySQL 3.51 ODBC drive using a parameterized ADO sproc call, but this can wait if the above works.
Vik
October 16, 2008 at 10:15 am
I was stuck on this error for a couple days, and this topic solved the issue! Thanks!!
October 29, 2008 at 9:14 am
Thank you ! You save my day !
ps : I know it's an old thread, but i've spent the day on it ... 🙂
November 4, 2008 at 2:49 pm
Hello,
question on mysql linked server performance...
the mysql TransactionDetail table has 2+ million records and has an index on the EntryDate field.
the linked server to mysql has been implemented and generally works.
The following query works fine (results <=2 seconds) using the OpenQuery method:
select TD.*
from openquery(MSSQL, '
SELECT TD.* FROM TransactionDetail AS TD WHERE TD.EntryDate = ''2008-11-03'';'
) as TD
This 4-part query fails after 20 seconds and returns and "out of memory" error message.
SELECT TD.* FROM MYSQL...TransactionDetail AS TD WHERE TD.TradeDate = '2008-11-03'
Other queries referencing smaller mysql tables using 4-part method work fine, such as:
SELECT A.* FROM MYSQL...Accounts AS A
Any ideas why 4-part method appears to ignore the existing mysql index on EntryDate?
Is there a way to force it to using back-end index?
This is SQL Server 2005 and I'm using the OLE DB Provider for ODBC Drivers as outlined above.
Thanks
Joe
November 4, 2008 at 3:00 pm
I think the problem is the memory or timeout setting in the DSN or the provider
January 7, 2009 at 4:48 am
http://dbfriend.blogspot.com/2009/01/how-to-create-link-server-for-mysql-in.html
My Blog:
February 11, 2009 at 3:27 pm
Hi Vik,
I am not meeting any success getting a SP from MySQL to execute via a linked server...
Do you have any tips?
Your Help is much appreciated.
Many Thanks in advance
Stephen
February 11, 2009 at 4:05 pm
Stephen,
I ended up abandoning the linked server approach because I ran into issues calling the MySQL sproc from SQL Server. It was a while ago so I can't precisely remember why -- think it had to do with requiring the sproc to return values back in a recordset (not just getting a return code) and not being able to do so, given the limitations of openquery/openrowset and linked servers. I think that was it -- the error I was getting indicated that the OLE provider could not determine the types of the returned columns since the recordset was coming from a sproc and not from a table.
I don't what kind of problems you are running into but I ended up implementing this as ODBC/ADO logic from within ASP page. This was used to ultimately synchronize a SQL Server user table with a wordpress blog database.
' blog integration
Dim oDB
Dim sSQL
Dim oRS
set oDB = server.CreateObject("ADODB.Connection")
set oRS = server.CreateObject("ADODB.RecordSet")
on error resume next
' synch GC user list with blog user list
if gbDevServer then
oDB.Open "Driver={MySQL ODBC 5.1 Driver};Server=xxx;database=wordpress-dev;UID=root;PWD=XXX;"
else
oDB.Open "Driver={MySQL ODBC 5.1 Driver};Server=yyy;database=wordpress;UID=root;PWD=YYY;"
end if
sSQL = "call ksp_synchuser( '" & sNick & "', '" & sEmail & "', '" & sPass & "', '" & sFirst & "', '" & sLast & "');"
'sSQL = "call ksp_synchuser( 'testvik', 'vhanda@kmpdesigns.com', 'test123', 'vik', 'handa');"
set oRS = oDB.Execute(sSQL)
if oRS.State = 1 then ' if recordset open
oRS.Close
end if
set oRS = nothing
set oDB = nothing
' end -- blog integration
Hope this helps.
Vik
June 16, 2009 at 10:18 am
Thanks Jim! This was a great help. I've never set up a linked server before, and this worked great!
June 18, 2009 at 5:05 am
EXCELLENT THREAD.
July 27, 2009 at 4:21 pm
Jim: sorry to bother you: I'm trying to pass data from an sql server 2005/asp.net to another server with mysql/php the idea is each time that a record is being added to the sql it sends the same data to the other server; the main issue here is we are not owners of the other server; but we have an account to open and use our databases there; each database allow our clients to access 2 different web sites; what we want to do is: to avoid typing the same data 2 times; is general information from 6, 7 different fields; thats everything; can you help me ??
henry
Viewing 15 posts - 46 through 60 (of 128 total)
You must be logged in to reply to this topic. Login to reply