January 13, 2010 at 9:56 am
I created the DSN using ODBC administrator from Windows Server Admin console. When I tested the DSN named 'DENSONAIL' on port 3306 in the ODBC administrator it tested successfully. So, yes, the MySQL server must be listening on that port.
On the MySQL server - my user has privilages as follows:
User: remoteacct
Host: 64.90.25.%
Thanks again ... I'm baffled!
January 13, 2010 at 10:04 am
Tricky without being able to see it, but add a host of % to your user - it may be resolving the DNS name in this driver, or adding the domain to the end.....
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
January 13, 2010 at 10:10 am
Also, did you enable oledbconnections in your sql2005 surface area config?
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
January 13, 2010 at 11:00 am
I tried updating the HOST to '%' but it still reported the same error message. Also, where in the SQL SAC do you enable oledbconnection ? I looked but could not find it.
Thanks!
January 13, 2010 at 1:34 pm
Sorry, my mistake -
Is there anything in the MySQL server app or security log? What happens if you try to expand the catalogs in the linked server?
Is Excel or similar on the SQL server? Or the mysql client? If the mysql client is on there, maybe you could try connecting to the server/mysqldb using the remote account credentials....
Yup, I'm clutching straws now...
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
January 13, 2010 at 1:44 pm
I haven't checked the MySQL Server. I had a pretty hot incident come through and had to put that on the back burner.
When I get back to it, I'll check the remote server's logs and see if I can find anything.
In the meantime - I was able to pull data using the mysql .net provider. This is an environment that I'm doing some 'proof of concept' work with right now so it's not like there's a deadline that I'm up against. I am just wanting to get it to work. Before I tried the linked server, I used the .net provider and populated a datagrid in just a few minutes of troubleshooting. So, I feel that all credentials are set right as are firewall settings.
Anyways, when I get the other complete, I'll come back. Thanks for all your help.
February 9, 2010 at 12:52 pm
Sorry that it has been so long since I updated. Alot has happened over the last few weeks and I have made some progress with this.
Here's the current situation:
The server that I had been using to create a linked server on is our development server. So, I tried to create a linked server on our production server and it worked. I've been playing around with it and getting really excited about the possibilities that this opens up for this particular system.
However, the development server (where the testing area is for the production system) still will not successfully create a linked server.
I scripted out the working linked server and executed the script on the non-working server. Still no dice. So, I verified that the ODBC was configured properly and that it was named the same as in the linked_server script. Following that, I checked the permissions for all of the sql services are running and they are identical to the production server. The ODBC tests successful but fails a connect test from within SQL management studio.
IT has to be a configuration that I am overlooking or permissions issue with the server itself. The fact that it works from another server on our LAN pretty much rules out any network issues.
Any thoughts?
March 15, 2010 at 2:34 pm
Great thread- helped me to create linked server (mysql 5.1 to Sql Server 2005 64 bit Enterprise),
thanks. I mean select data from mySql (request from sql server) works like a charm.
Unfortunately I still did not succeed with Update/Insert. For example, statement
"UPDATE MYSQL_SRV1...tblTest SET done = 1 WHERE id = 1" fails with error:
"The requested operation could not be performed because OLE DB provider "MSDASQL" for linked server "MYSQL_SRV1" does not support the required transaction interface."
Could not find any explanation on web. Any thoughts are really appreciated. Thanks,
March 15, 2010 at 2:59 pm
When I ran into that issue, I came up with a temp table on the MySQL server that I would populate w/ records using an insert or select into and then I would update the target table ...
Sorry that I don't have any code but I've changed jobs since I posted and now I'm in an ALL SQL Server environment! YAY! 😀
March 15, 2010 at 5:00 pm
Yep, probably we can find some way around but still I would like to find the reason for Update/Insert failure while Select works fine. Thanks for response, Andy
March 16, 2010 at 2:10 am
From memory, that issue can be fixed by turning off one of the settings in the MySQL ODBC driver (I think related to Transactions...) [Sorry, I don't have access currently to the system I did it on...]
March 17, 2010 at 1:09 pm
You are absolutely right-
while configuring System DSN do not check "Disable Transaction Support" under Flag 3.
Thanks a lot 🙂
March 19, 2010 at 10:28 am
Glad it helped!
August 4, 2010 at 10:55 am
I really impressed the way you had provided the linked server details but I am stuck and want to insert data from MS sql 2005 to mysql.
Regards,
Santosh Waghmare
August 10, 2010 at 1:20 pm
Hey on this post one of the steps was to:
Create a Linked Server in SSMS for the MySQL database SSMS (SQL Server Management Studio -> Expand Server Objects -Right Click Linked Servers -> Select New Linked Server
I don't have that option in SSMS. The problem I'm trying to address is setting up SSMS to be able to connect to a mySQL database. Therefore, I don't have any working connections to any servers within SSMS yet...so I can't access/expand Server Objects. Do you know how to deal with this? Is this a dumb question? Sorry...thanks for the help, let me know if you need me to clarify.
Viewing 15 posts - 91 through 105 (of 128 total)
You must be logged in to reply to this topic. Login to reply