SA 12.0.0.2589, Win XP SP3
I'm trying to create a remote server to retrieve data from a MySQL database another machine in our LAN. The MySQL ODBC 5.1 Driver is installed, a ODBC data source named TestMySQL created, test connection successfull. But creating a remote server always fails while testing the connection trying to use localhost!?.

Connection failed.
Unable to connect to server 'TestMySQL': [MySQL][ODBC 5.1 Driver]Can't connect to MySQL server on 'localhost' (10061)
[Sybase][ODBC Driver][SQL Anywhere]Unable to connect to server 'TestMySQL': [MySQL][ODBC 5.1 Driver]Can't connect to MySQL server on 'localhost' (10061)
SQLCODE: -656
SQLSTATE: HY000
SQL Statement: SELECT FIRST table_name FROM dbo.sp_remote_tables( 'TestMySQL', NULL, NULL, NULL, 1 ) ORDER BY 1

Sample statement:

CREATE SERVER "TestMySQL" CLASS 'MYSQLODBC' USING 'TestMySQL';

Tried variations (server:port/database, driver=...) to no avail. The documentation is rather terse.
Any ideas what I'm doing wrong?

asked 10 Nov '10, 14:25

Reimer%20Pods's gravatar image

Reimer Pods
4.2k334481
accept rate: 12%

edited 11 Nov '10, 14:30

Siger%20Matt's gravatar image

Siger Matt
3.1k486493


Directly after posting this question I remembered my old motto: double check twice! I went through all again, but this time incidentally recreated the ODBC data source as a system dsn (previously it was a user dsn).
Lo and behold! That was the solution, everything runs smoothly, my question is answered. But still I think, the docs could use some more detail on that topic.

permanent link

answered 10 Nov '10, 14:34

Reimer%20Pods's gravatar image

Reimer Pods
4.2k334481
accept rate: 12%

So you say the user DSN doesn't work since the SA database server runs under a different account (e.g. the local system)? - Then at least your solution seems very comprehensible...

(10 Nov '10, 14:54) Volker Barth

I've run into that before as well, not with the remote server but the issue that User DSN is not the same as System DSN.

Also on Windows boxes that are 64 bit there are at that point 2 different ODBC managers, one for 32 bit drivers and one for 64 bit drivers and to make it even more difficult, the 32 bit version is in a folder called c:WindowssysWOW64.

(10 Nov '10, 15:37) Siger Matt
1

@Siger: I always thought the even more difficult point with Windows 64 bit is that the 64 bit ODBC Admin is still named Odbcad32.exe...

(10 Nov '10, 16:07) Volker Barth
1

That's why I generally prefer to create System DSNs...

(10 Nov '10, 16:40) Volker Barth
2

@volker re 32/64 wonderful isn't it. The 64bit version is called odbcad32.dll and is in a folder called system32 and the 32 bit one is also called odbcad32.dll but is in a folder called syswow64. It's like trying to explain cricket!

(10 Nov '10, 17:09) Justin Willey
1

@Volker: yes, the engine runs as a service and has no access to my user dsn (HKCU for registry freaks), but only to system dsn (HKLM). It should have been clear to me from the start, but sometimes even coffee doesn't help.

(11 Nov '10, 13:57) Reimer Pods

@Reimer: Agreed - and it's fine that now anyone else running into this (not uncommon) issue may find the solution here:)

(11 Nov '10, 14:22) Volker Barth
More comments hidden
showing 5 of 7 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×124
×46
×15
×14

question asked: 10 Nov '10, 14:25

question was seen: 3,263 times

last updated: 11 Nov '10, 14:30