February 6, 2012 at 1:03 pm
Hello,
I have a problem which is driving me crazy. I need to extract some data from a MySql database into my data warehouse for analysis purposes. I have setup a MySQL ODBC 3.51 driver which I can use with Crystal Reports. The data looks fine & the reports refresh quickly.
I can create a linked server in SSMS (via MSDASQL) in which the 'Test Connection' reports success. I can expand the catalogs & everything looks fine (structure-wise) down to the table level in the tree view.
However, if I try to execute a query like:
SELECT *
FROM openquery(MySQL_DB, 'Select * from DB.table')
I just get an infinite "Executing query..." message - with no errors thrown. I know that at some level things are working because if the table name is misspelled I get an immediate error.
One other interesting observation is that the "Stop" button on the query doesn't work. To cancel the query I have to use the 'Query => Connection => Disconnect All Queries' route to kill it.
If anyone has any ideas I would be most appreciative.
February 6, 2012 at 1:07 pm
erik-1043807 (2/6/2012)
Hello,I have a problem which is driving me crazy. I need to extract some data from a MySql database into my data warehouse for analysis purposes. I have setup a MySQL ODBC 3.51 driver which I can use with Crystal Reports. The data looks fine & the reports refresh quickly.
I can create a linked server in SSMS (via MSDASQL) in which the 'Test Connection' reports success. I can expand the catalogs & everything looks fine (structure-wise) down to the table level in the tree view.
However, if I try to execute a query like:
SELECT *
FROM openquery(MySQL_DB, 'Select * from DB.table')
I just get an infinite "Executing query..." message - with no errors thrown. I know that at some level things are working because if the table name is misspelled I get an immediate error.
One other interesting observation is that the "Stop" button on the query doesn't work. To cancel the query I have to use the 'Query => Connection => Disconnect All Queries' route to kill it.
If anyone has any ideas I would be most appreciative.
What happens if you change this to:
SELECT *
FROM openquery(MySQL_DB, 'Select TOP 10 * from DB.table')
Jared
CE - Microsoft
February 6, 2012 at 2:25 pm
Hi Jared,
Thanks for your reply.
If I try the using that query with the wild card I get a syntax error. It seems like it should work, but I am no expert at using openquery commands. Especially when MySQL & MDASQL is involved.
Interestingly if I run the query as you suggest against a linked SQL server it works fine.
February 6, 2012 at 2:28 pm
Sorry... Try this:
SELECT *
FROM openquery(MySQL_DB, 'Select * from DB.table LIMIT 0,10')
Jared
CE - Microsoft
February 6, 2012 at 2:38 pm
With that syntax I get no errors but am again stuck on "Executing query...".
As an off the cuff test I tried using CR to pull all of the data from that table - which admittedly has a lot of old data in it - and got the result in < 2 min.
February 6, 2012 at 2:44 pm
And what happens if you run this?
SELECT TOP 10 *
FROM openquery(MySQL_DB, 'Select * from DB.table LIMIT 0,10')
So far, it seems to not want to close the connection. Not sure why yet, but maybe we can filter out some tings by these different queries and their outcomes. If this next one completes, then finally remove the LIMIT 0,10 from the openquery and see if it completes.
Jared
CE - Microsoft
February 6, 2012 at 2:47 pm
Wait a minute... How did you configure the linked server? Did you select the MySQL ODBC driver or did you use something else?
Jared
CE - Microsoft
February 6, 2012 at 2:52 pm
Jared may be right...start with the basics...
here's the proc from a thread on the subject; it's idea for setting up your mysql linked server:
http://www.sqlservercentral.com/Forums
--http://www.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) AS
--@linkedservername = the name you want your linked server to have
--@mysqlip = the ip address of your mysql database
--@dbname = the name of the mysql database you want to operate against. Without this, some of the features of openquery fail
--@username = the username you will use to connect to the mysql database
--@password = the password used by your username to connect to the mysql database
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.1 Driver};SERVER=' + @mysqlip + ';Port=3306;OPTION=3;DATABASE=' + @dbname + ';'
EXEC master.dbo.sp_addlinkedserver
@server=@linkedservername,
@srvproduct='MySQL',
@provider='MSDASQL',
@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
Once you have the Linked Server in place I have found a few different ways of interacting with the mysql database:
Select Statements:
select * from mysql5...country
select * from OPENQUERY(mysql5, 'select * from country')
Insert Statements:
insert mysql5...country(code,name)
values ('US', 'USA')
insert OPENQUERY(mysql5, 'select code,name from country;')
values ('US', 'USA')
Other Statements:
EXEC('truncate table country') AT mysql5;
Resources:
http://213.136.52.24/bug.php?id=39965
/*
works for me with openquery, but not 4 part naming convention
*/
Lowell
February 6, 2012 at 3:18 pm
First, I'd like to thank you guys for lending a hand.
Here is some background on what I tried - setup wise. My ODBC driver is MySQL ODBC 3.51 Driver & I set up the linked server to use the Microsoft OLE Provider for ODBC Drivers.
As I had seen it mentioned after early failures to get this to work I recreated the linked server specifying a Provider string. As far as that goes I think I got that bit right as SSMS doesn't want you to save the connection if it detects an error in the string setup.
That being said I did not script the linked server but used the GUI to create it. I'll go ahead & script it into place as Lowell suggests & let you know what happens.
~Erik
February 6, 2012 at 4:50 pm
Hello,
I tried recreating the linked server via script but am seeing the same behavior. I used the script from here:
http://www.sqlservercentral.com/Forums/Topic340912-146-11.aspx#bm1182941 (Thanks Theo!)
One thing I have seen mentioned in some other posts is odd behavior if there is odd data present.
If I run the following against my linked server:
Exec sp_tables_ex 'MySQL_DB'
In the REMARKS field I get seemingly random characters on some tables which change when I re-run the query. Can this be a clue?
February 6, 2012 at 8:24 pm
Hmm... Maybe try the 5.1 ODBC for MySQL? Seems to be some issue with the conversion of data.
Jared
CE - Microsoft
February 7, 2012 at 8:14 am
I tried the newer ODBC driver first but it is incompatible with the database version.
February 7, 2012 at 8:44 am
You are in an SQL Server 2008 forum, is that the version you are on? Or are you saying it is not compatible with your MySQL database version?
Jared
CE - Microsoft
February 7, 2012 at 9:11 am
Sorry Jared - I was not explicit. I am trying to get the data from an old MySQL database into SQL Server 2008 R2. the new ODBC driver won't work with the MySQL database version.
It is an old database collecting sawmill production data. if I run the SELECT version(); command against the MySQL database it comes out as 4.0.1-alpha-nt which does not give me the warm & fuzzies.
If I try to connect to the MySQL database with the newer driver the connection test tells me that the driver doesn't support server versions under 4.1.1
I am not too comfortable doing anything to or with that MySQL database itself as I know nothing about & in addition it is quite remote from here.
February 7, 2012 at 9:39 am
Totally understood 🙂 Let me think on this a bit more...
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply