February 7, 2012 at 9:53 am
2008 R2...is that 64 bit?
if it is, you have to use 64 bit MYSQL drivers in order to create a linked server.
that would be one more thing to double check....if you installed 32 bit drivers, they could be the headache you are banging against.
Lowell
February 7, 2012 at 10:02 am
Hi Lowell,
Sad to say I did think of that. In fact I decided to set up the data warehouse as 32 bit due to issues I have had in the past with 64 bit.
Another interesting observation. If I try to connect to the MySQL database with SQL Server Business Intelligence Development Studio I can connect & make queries just fine - as I can in CR. The problem is that I'm not familiar with that program like I am with SSMS.
~Erik
February 7, 2012 at 10:14 am
Can you give the DDL for the mySQL table you're querying against? Any odd types?
Linked Server isn't perfect and there isn't always a one to one mapping between types, especially with older versions/drivers. You can usually get around most issues by explicitly casting columns in your OPENQUERY select statement - maybe start by just selecting a single column at a time out of the table and see which you get results back from and which hang...
Is there a Max query timeout setting on the MySQL Server? I've had hanging linked servers to mySQL before as a DBA had set the query timeout far too low and it had the effect of waiting indefinitely rather than returning the error back...
February 7, 2012 at 12:35 pm
Howard,
You may have hit on something. Perhaps one of you all can guide me in this as my MySQL knowledge is nil.
I remoted into the MySQL server in question & thought I'd do some poking around to look at the data types of the table(s) and check on query time out values. The data types looked fine for the table I'm experimenting on. I found nowhere to set any timeout values in my poking around.
However I have fund something interesting. In the MySQL Control Center I browsed the tables & decided to open one of the ones I have had luck with in Crystal Reports. I right clicked on the table and selected open to limit. As it has lots of data in it I decided to just select 10 records as a test. Imagine my surprise when the query window seems to be stuck on Executing Query!! It is still stuck there many times over the time limit to pull all of the records several times into CR..
If that openquery command is trying to execute on the target server it now makes sense to me that it wouldn't work.
Of course I have no idea as to where to go from here...
February 7, 2012 at 12:37 pm
Ha! Now you go to a MySQL forum 🙂
Jared
CE - Microsoft
February 7, 2012 at 1:08 pm
Apparently queries will run on the MySQL server. While they don't seem to run in the native tool, someone has installed a different management tool on that server - perhaps because the MySQL Control Center doesn't work (0.9.1-beta). Using the SQLyog tool I return results like a champ.
So now I'm back at square one...
February 7, 2012 at 1:11 pm
Well... I think you have to find out why SQLyog works and the native client does not. Therein lies your answer.
Jared
CE - Microsoft
February 7, 2012 at 1:40 pm
Do you really think so? I'd have thought that any SQL query tool running locally on the MySQL server (that returns a result) would prove that end of it...
~Erik
February 7, 2012 at 1:43 pm
I wouldn't say that when the native tool doesn't work either. We need to know why.
Jared
CE - Microsoft
February 8, 2012 at 8:13 am
Hi Jared,
You are the man! I was able to get the native SQL Control Center working for queries by installing a newer version. Now for some reason I can also get a linked server query to return results. That being said the query results come in MUCH slower than with the BI Studio or CR.
Are you aware of any techniques to speed up the results so they come inline with data extraction by those other methods?
Thanks again.
~Erik
February 8, 2012 at 8:21 am
SSIS is the way to go if you want to speed this up. I find non-MS linked server connections to be really slow.
February 8, 2012 at 8:30 am
Thanks Howard,
I've been meaning to learn SSIS for some time - and I guess the time is now!
~Erik
January 25, 2018 at 11:10 am
Just an update for Lowells script. I had to add the datasource for the MySQL ODBC 5.3 Driver. Basic changes are:
--http://qa.sqlservercentral.com/Forums/Topic340912-146-1.aspx
CREATE PROC uspCreateLinkToMySQLDB @linkedservername varchar(50), @mysqlip varchar(50), @dbname varchar(100), @username varchar(50), @password varchar(50)
, @datasource varchar(50)
AS
--@linkedservername--@linkedservername = the name you want your linked server to have
--@mysqlip--@mysqlip = the ip address of your mysql database
--@dbname--@dbname = the name of the mysql database you want to operate against. Without this, some of the features of openquery fail
--@username--@username = the username you will use to connect to the mysql database
--@password--@password = the password used by your username to connect to the mysql database
-- @datasource = the remote database on the mysql server
BEGIN
--DROP THE LINKED SERVER IF IT EXISTS
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = @linkedservername)
EXEC master.dbo.sp_dropserver @server=@linkedservername, @droplogins='droplogins'
--ADD THE LINKED SERVER
DECLARE @ProviderString varchar(1000)
Select @ProviderString = 'DRIVER={MySQL ODBC 5.3 Driver};SERVER=' + @mysqlip + ';Port=3306;OPTION=3;DATABASE=' + @dbname + ';'
EXEC master.dbo.sp_addlinkedserver
@server=@linkedservername,
@srvproduct='MySQL',
@provider='MSDASQL',
@datasrc= @datasource,
@provstr=@ProviderString
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'use remote collation', @optvalue=N'true'
EXEC sp_addlinkedsrvlogin @linkedservername, 'false', NULL, @username, @password
END
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply