Linking MySQL to MSSQL error

I want to link MySQL server to MSSQL server in MS开发者_Python百科SQL Management Studio. I have followed instructions from different websites to do that:

Link 1

Link 2

Link 3

No matter what I do, when setting up DSN in ODBC Data Source Administrator, the connection is fine and working. But when I try to use given DSN in MSSQL Management Studio to actually link that server, I am getting error:

Datasource name not found and no default driver specified [Error 7303]

I am completly lost, it seems like it just does not see the DSN I have created.

I am using SQL Server 2008 and MySQL 5.

Any ideas? I am determined to link it as I have to move across quite a few big tables and don't want to type all the fields by hand...

Thanks!

Your problem is similar to this.

Referenced link says " Now, the simple explanation is this, when using a linked server (and the OPENROWSET is a sort of linked server) then a temporary DSN (Data Source Name) is created

in the TEMP directory for the account that started the SQL Server service. This is typically an account that is an administrator on the machine.

However, the OLEDB provider will execute under the account that called it. This user can even be sysadmin on the SQL Server, but as long as this user is not an administrator on the machine, it will not have Write access to the TEMP directory for the SQL Server service account. "

Please check your DSN connection.

You might be add it into User DSN instead of System DSN.

If you add DSN into User DSN, it will return 7303 error code, but if you create DSN as system DSN it will work.