Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MSSQL query that puts results into MySQL database

Posted on 2011-10-19
6
Medium Priority
?
379 Views
Last Modified: 2012-05-12
Hi,

using SQL server 2008 R2 and want to run a scheduled query that the results are put into A MySQL database. I have used SSISTO d o this the other way. But how can I get results from MSSQL to MySQL?

Thanks
0
Comment
Question by:andybrooke
  • 3
  • 3
6 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 36991686
create a linked server in your sql 2008 box (after installing the MySQL drivers on the windows box), then you can use openquery, aka insert into openquery

mysql odbc: http://www.mysql.com/products/connector/
oledb provider as alternative: https://cherrycitysoftware.com/CCS/Providers/MySQLWithVS.aspx

linked server (mysql):
http://www.ideaexcursion.com/2009/02/25/howto-setup-sql-server-linked-server-to-mysql/

insert/openquery:
http://msdn.microsoft.com/en-us/library/ms188427.aspx

hope this helps
0
 

Author Comment

by:andybrooke
ID: 36994147
I think open query is the key. I have set up a linked server which works. I think maybe its best to create a trigger that once specific data is entered into a field it copies this to my myql table. I need help..lol.
0
 

Author Comment

by:andybrooke
ID: 36995536
I want to make a trigger in a table that when a status column is populated with 1 of these 4 numebrs (2000,2010,2020,2030). It then runs a query that does the following. It looks up the ID of the current row Table A and uses this to find the related row in Table B.  

From Table B it then gets a lead ref from that row. It would now connect to a MySQL database via openquery (Linked database already set up)look up the ID in a Table C and in the status column enter a value based on the initial trigger.

So if 2000 was in the status column in Table A MSSQL. Now the mySQL Table C status would show 'Completed'.

If 2010 was in the status column in Table A MSSQL. Now the mySQL Table C status would show 'ONGOING'.
a
and so on...

I know this possible but not sure where to start.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37005335
via trigger: not recommended (if working at all), because mysql does not really support distributed transactions.

so, you can put a trigger, but just to insert into a "process" table the key values of the rows that updated, and than need to be "replicated" to the mysql table.

then, in a scheduled job, you can then perform the update (see this article):
http://www.experts-exchange.com/A_1517.html

0
 

Author Comment

by:andybrooke
ID: 37026268
I added a linked server and running the query works ok. But when I put it insidea  trigger it fails.


OLE DB provider "MSDASQL" for linked server "TEST" returned message "[MySQL][ODBC 5.1 Driver]Optional feature not supported".
Msg 7391, Level 16, State 2, Procedure STATUS_UPDATE, Line 6
The operation could not be performed because OLE DB provider "MSDASQL" for linked server "TEST" was unable to begin a distributed transaction.
create trigger yourtriggername
on tableA
for insert,update
as
begin
update t
set t.status= case i.status  when 2000 then 'Completed' when 2011 then 'Ongoing' end
from inserted i
inner join tableB b
on b.id = i.id
inner join [linkedservername]...tablename t
on t.id = b.leadref
end

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37029753
that is exactly what I wrote: you won't be able to do this in the trigger itself directly, but only in a dedicated job outside of the trigger.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question