Openquery with Mysql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Openquery with Mysql

Author

Topic

mase2hot
Starting Member

36 Posts

Posted - 2011-10-19 : 15:34:09

Hi,

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. Please help...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-20 : 00:51:56

something like


create trigger yourtriggername
on tableA
for insert
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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mase2hot
Starting Member

36 Posts

Posted - 2011-10-20 : 13:56:57

I get Conversion failed when converting the varchar value 'clientid' to data type int. Even though both columns are int......

mase2hot
Starting Member

36 Posts

Posted - 2011-10-20 : 14:27:35

got passwed that. but trigger doesnt seem to work or if it does doesnt update mysql. How can I troublesho ot this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 00:38:32

run profiler trace and see if trigger is getting executed

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mase2hot
Starting Member

36 Posts

Posted - 2011-10-21 : 11:37:48

ran that doesnt seem to get executed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 11:47:43

are you doing bulk insert?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mase2hot
Starting Member

36 Posts

Posted - 2011-10-24 : 17:56:30

No, when 1 field is updated in mssql it should activate the trigger which then updates 1 field in mysql... The code works fine when not in the trigger.... Using code visakh16 gave me. Will this code only work for new inserts? as i want it to active on updates...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 00:33:30

what you asked for insert action alone. if you want this to be applied for updates also use like below


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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mase2hot
Starting Member

36 Posts

Posted - 2011-10-25 : 05:19:03

Error I get is below with the amended update added as you stated:

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 05:25:14

quote:
Originally posted by mase2hot

Error I get is below with the amended update added as you stated:

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.

are you trying to do actual update inside a transaction?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mase2hot
Starting Member

36 Posts

Posted - 2011-10-25 : 09:10:54

I think so. When a value is updated in MSSQL I want a trigger to look out for certain values in a column. When these values are entered I want the trigger to recongnise that and then update MySQL with values contained in the trigger you wrote...

mase2hot
Starting Member

36 Posts

Posted - 2011-10-25 : 13:03:55

Is a trigger best forwh at I'm doing? or would it be best to have a trigger run a stored procedure? As I dont need it doing in realtime...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-26 : 00:31:57

quote:
Originally posted by mase2hot

I think so. When a value is updated in MSSQL I want a trigger to look out for certain values in a column. When these values are entered I want the trigger to recongnise that and then update MySQL with values contained in the trigger you wrote...

i think you're trying to wrap whole process inside a transaction. the problem here is since the last update has to be performed in the remote linked MySQL server, you need to use a distributed transaction.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-26 : 00:37:14

quote:
Originally posted by mase2hot

Is a trigger best forwh at I'm doing? or would it be best to have a trigger run a stored procedure? As I dont need it doing in realtime...

Then it might be probably worth implementing it asynchronously.
ie. every update in your MSSQL table will fire trigger and then recognise values to be captured which it puts in another MSSQL table (Audit table) with status open. Have a separate job which runs periodically which looks for the open records in the audit table and moves it to MySQL table and make record closed. this way you dont have to wrap them in same transaction.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources