Trigger from Server to Server

[vbuser1976]

I have two SQL servers with multiple databases in each one. I would like to be able to create a trigger on Server 1, database A, Table a to update data on Server 2, database B, Table b. Is it possible? Also, is it possible if the servers are two separate versions (Server 1 in SQL 6.5 and Server 2 is SQL 7.0)? I know you can create triggers from table to table within a database, but I want to be able to go to server/database to server2/database2 and I want to make sure it is possible before I start working on it.

Any comments would be greatly appreciated. -vbuser1976
VB6.0 SP4
Crystal 8.0 Developer Edition
SQL 7.0 SP2

http://ccsco.homestead.com

 

[tlbroadbent]

It should be possible if the Trigger is on the 7.0 server and you create a linked server in the SQL 7.0 server. Use sp_addlinkedserver to add a link to the SQL 6.5 server. In the Trigger add the update logic, referring to the linked table by the four part name, server.dbname.owner.table.

Example:

Insert server65.pubs.dbo.titles
Select col1, col2, col3,... Terry

;-) I never worry about the future. It comes soon enough. -Albert Einstein

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[vbuser1976]

Is it possible if the main server is the one with 6.5 instead of 7.0? -vbuser1976
VB6.0 SP5
Crystal 8.0 Developer Edition
SQL 7.0 SP2

http://ccsco.homestead.com

 

[tlbroadbent]

No, you can't access databases on other servers from SQL 6.5. Terry

;-) I never worry about the future. It comes soon enough. -Albert Einstein

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[theresatan]

Hi,Terry,

Do you try to fire a trigger to update the table in anothere server? I have the same problem as this author.

I already set up the linked server and prove the connection is good.

I can update data on remote server table use Query Analyzer, but it did not work when I use a trigger to fire this update statement.
Ex:
following is working
update remoteserver.DB.dbo.tableB
set col1 = 'a'

followin is not working
create a trigger on a local tableA and the trigger is:

create TRIGGER tr1
ON tableA
FOR UPDATE
AS
update remoteserver.DB.dbo.tableB
set col1 = 'a'

When I update tableA,I got remote server is not available and The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

Any ideas?

Thanks!

Theresa

 

[osjohnm]

Hi Theresa

I'm not to sure which version of SQL Server you are running
or which OS but I'm going to assume that this should be the same on any version.

I do know that this does happen in SQL 2000/Win 2000 Server.
There is a service called MS DTC or Microsoft Distributed Transaction Coordinator. This allows and controls mutliple data sources in a single transcation.

It used when stored procedures etc. fetch and update data between servers. Check that the service (Distributed Transaction Coordinator as it is called in Services) is running on both servers, if not then enable it and try your trigger again.

I might not be 100% right in this situation as the error I got a while back actually stated that MS DTC wasn't running and was told to run MS DTC and re-try the transaction.

NT Server and sql 7.0 and sql 6.5 should have something similiar but might not have the same service name. Currently at home, will check my only server still using NT and sql server 7.0 in the morning just to make sure.

Books Online has quite a bit of detail on "Distributed Transactions" and "MS DTC" perhaps you would find some useful info in there that will explain exactly how your trigger is actually connecting to the servers.

Hope this helps

John

 

[osjohnm]

Hi Theresa

One last thing, in future rather start your own post when the post is as old as this one.

John