June 14, 2012 at 9:41 pm
Hello! I'm having an issue setting up a linked server connection from SQL 2008 R2 (x64) on a Windows 2008 VM to a MySQL server using the MySQL ODBC 5.1 Driver. The system DSN ('rosters') is set up and tests correctly, however when I create the linked server in SSMS the process simply hangs. I created multiple tests, all of them hang and in order to stop them I must restart the service (killing them in activity monitor leaves them in KILL / ROLLBACK).
Would just like to get the linked server set up and any help you could give would be much appreciated!
PS - Here's the code for the linked server (with user name and password removed):
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ROSTERS', @srvproduct=N'rosters', @provider=N'MSDASQL', @datasrc=N'rosters'
GO
EXEC master.dbo.sp_serveroption @server=N'ROSTERS', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ROSTERS', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ROSTERS', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ROSTERS', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ROSTERS', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ROSTERS', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ROSTERS', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ROSTERS', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ROSTERS', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ROSTERS', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ROSTERS', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ROSTERS', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ROSTERS', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'ROSTERS', @locallogin = NULL , @useself = N'False', @rmtuser = N'<user>', @rmtpassword = N'<password>'
GO
June 25, 2012 at 4:54 pm
Have a look at the below link, It may be helpful
http://sqlserverpedia.com/blog/sql-server-bloggers/linked-server-setup-from-sql-server-to-mysql/
December 5, 2012 at 10:38 am
Did you ever find a solution to this? I have the same problem. I've tried many things from the web and nothing is making any difference.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy