|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Friday, March 19, 2010 12:38 PM
Points: 9,
Visits: 39
|
|
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!
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Wednesday, March 17, 2010 10:18 AM
Points: 50,
Visits: 176
|
|
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
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Wednesday, March 17, 2010 10:18 AM
Points: 50,
Visits: 176
|
|
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
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Friday, March 19, 2010 12:38 PM
Points: 9,
Visits: 39
|
|
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!
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Wednesday, March 17, 2010 10:18 AM
Points: 50,
Visits: 176
|
|
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
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Friday, March 19, 2010 12:38 PM
Points: 9,
Visits: 39
|
|
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.
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Friday, March 19, 2010 12:38 PM
Points: 9,
Visits: 39
|
|
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?
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Friday, March 19, 2010 12:27 PM
Points: 258,
Visits: 523
|
|
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,
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Friday, March 19, 2010 12:38 PM
Points: 9,
Visits: 39
|
|
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! 
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Friday, March 19, 2010 12:27 PM
Points: 258,
Visits: 523
|
|
| 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
|
|
|
|
