Problem Connecting SQLServer2016x64 using LinkedServer to MySqlx32
I'm migrating a working system from Windows2003x32 with SQL2005x32 and MySqlx32 to Win2012R2x64 with SQL2016SP3x64 and MySqlx32 on the old system back I am still sucesfully using LinkedServer to read and write to MySql from SQL. With MySql ODBC 3.51 Dreiver.
On the new server I tryed a bunch of options all resulting in DSN architecture mismatch.
What I did: I installed MySql-connector-odbc-3.51.30-win64 (later I tryed v5.1.13 and 5.3.14 both 32 and 64 versions) Using odbcad32.exe from System32 and/or SysWOW64 (tryed both) I sucefully added under System DSN the datasource and tested Ok.
From SQLServerManager I tryed to add linked server using
EXEC master.dbo.sp_addlinkedserver @server='SVTPASS', @srvproduct='SVTPASS', @datasrc='SVTPASS',
@provider='MSDASQL',
@provstr='DRIVER={MySQL ODBC 3.51 Driver}; SERVER=LOCALHOST; Port=3333; USER=******; PASSWORD=********; OPTION=3; DATABASE=svt_fpc_auth'
later tryed using "DRIVER={MySQL ODBC 5.3 Driver}" and "DRIVER={MySQL ODBC 5.3 Unicode Driver}"
But the strange thing is that whatever I try, even writing nonsense at DRIVER={} the same architecture mismatch error I get.
When testing the connection of linked server I recieve the following error: "[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application"
where am I doing it wrong? I know that I'm trying to acess a 32bit MySql from 64bit SQLServer but there should be a solution.
I read many forum posts about this issue and tryed a few options suggested here and there, but I'm confused and and I don/t even know now what combinations I tryed and what I didn't tryed yet.
Can someone help with at least telling what are the right weapons to use to fight this issue... Thanks in andvance
