473,856 Members | 1,762 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem with triggers between linked servers

I have two SQL Server 2000 machines (server_A and server_B). I've
used sp_addlinkedser ver to link them both, the link seems to behave
fine. I can execute remote queries and do all types of neat things
from one while logged onto the other.

I'm working on a project to keep the data in the two systems
synchronized, so I'm using triggers on both sides to update each
other. For testing, I've created a simple, one-column table on both
servers, and also created a trigger on both tables. Consider the
following trigger code on server_A:

CREATE TRIGGER myTrigger
ON myTable
FOR INSERT
AS
SET XACT_ABORT ON
SET NOCOUNT ON
INSERT INTO server_B.myDB.d bo.myTable SELECT * FROM inserted
GO

And also the following trigger code on server_B:

CREATE TRIGGER myTrigger
ON myTable
FOR INSERT
AS
SET XACT_ABORT ON
SET NOCOUNT ON
INSERT INTO server_A.myDB.d bo.myTable SELECT * FROM inserted
GO

Before you start screaming about the recursive relationship between
these triggers, I'm well aware of that issue, so I'm wrapping the
trigger logic with a login ID test. The servers are linked using a
special login account, I'll call it 'trigger_bypass _login', so the
triggers look like this:

CREATE TRIGGER myTrigger
ON myTable
FOR INSERT
AS
SET XACT_ABORT ON
SET NOCOUNT ON
IF SUSER_SNAME() <> 'trigger_bypass _login'
INSERT INTO server_A.myDB.d bo.myTable SELECT * FROM inserted
GO

Although this logically works fine, there seems to be a compile issue,
because I'm running into the error:

The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoi n::JoinTransact ion returned 0x8004d00a].

What is strange is that I CONTINUE TO GET THE ERROR if I change the
trigger code to the following:

CREATE TRIGGER myTrigger
ON myTable
FOR INSERT
AS
SET XACT_ABORT ON
SET NOCOUNT ON
IF 1=0
INSERT INTO server_A.myDB.d bo.myTable SELECT * FROM inserted
GO

So obviously, it has nothing to do with the actual inserting that the
INSERT performs, but rather the fact that the trigger INSERT
references the linked server/table.

So, I moved the INSERT statement to a stored procedure, and it works
and I no longer get the error:

CREATE TRIGGER myTrigger
ON myTable
FOR INSERT
AS
SET XACT_ABORT ON
SET NOCOUNT ON
IF SUSER_SNAME() <> 'trigger_bypass _login'
EXEC myStoredProcedu re
GO

It works.. BUT, the stored procedure does not have access to the SQL
Server 'inserted' trigger table. I've tried using

DECLARE CURSOR myCursor GLOBAL FOR SELECT * FROM inserted

and then letting the stored procedure reference the cursor, but then I
have to deal with the cursor data on a column-level basis, which is
not an option in this project because there are 100's of tables with
many columns, which might change over time.

So it is of extreme importance that I use INSERT INTO ... SELECT to
move the row data in a generic fashion.

I hope I have provided enough, yet not too much, information.

I would really appreciate any suggestions anyone might have as to how
I might handle this situation. Thanks.

Hank
Jul 20 '05 #1
4 14263
ha*********@yah oo.com (Hank) wrote in message news:<39******* *************** ****@posting.go ogle.com>...
I have two SQL Server 2000 machines (server_A and server_B). I've
used sp_addlinkedser ver to link them both, the link seems to behave
fine. I can execute remote queries and do all types of neat things
from one while logged onto the other.

I'm working on a project to keep the data in the two systems
synchronized, so I'm using triggers on both sides to update each
other. For testing, I've created a simple, one-column table on both
servers, and also created a trigger on both tables. Consider the
following trigger code on server_A:

CREATE TRIGGER myTrigger
ON myTable
FOR INSERT
AS
SET XACT_ABORT ON
SET NOCOUNT ON
INSERT INTO server_B.myDB.d bo.myTable SELECT * FROM inserted
GO

And also the following trigger code on server_B:

CREATE TRIGGER myTrigger
ON myTable
FOR INSERT
AS
SET XACT_ABORT ON
SET NOCOUNT ON
INSERT INTO server_A.myDB.d bo.myTable SELECT * FROM inserted
GO

Before you start screaming about the recursive relationship between
these triggers, I'm well aware of that issue, so I'm wrapping the
trigger logic with a login ID test. The servers are linked using a
special login account, I'll call it 'trigger_bypass _login', so the
triggers look like this:

CREATE TRIGGER myTrigger
ON myTable
FOR INSERT
AS
SET XACT_ABORT ON
SET NOCOUNT ON
IF SUSER_SNAME() <> 'trigger_bypass _login'
INSERT INTO server_A.myDB.d bo.myTable SELECT * FROM inserted
GO

Although this logically works fine, there seems to be a compile issue,
because I'm running into the error:

The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoi n::JoinTransact ion returned 0x8004d00a].

What is strange is that I CONTINUE TO GET THE ERROR if I change the
trigger code to the following:

CREATE TRIGGER myTrigger
ON myTable
FOR INSERT
AS
SET XACT_ABORT ON
SET NOCOUNT ON
IF 1=0
INSERT INTO server_A.myDB.d bo.myTable SELECT * FROM inserted
GO

So obviously, it has nothing to do with the actual inserting that the
INSERT performs, but rather the fact that the trigger INSERT
references the linked server/table.

So, I moved the INSERT statement to a stored procedure, and it works
and I no longer get the error:

CREATE TRIGGER myTrigger
ON myTable
FOR INSERT
AS
SET XACT_ABORT ON
SET NOCOUNT ON
IF SUSER_SNAME() <> 'trigger_bypass _login'
EXEC myStoredProcedu re
GO

It works.. BUT, the stored procedure does not have access to the SQL
Server 'inserted' trigger table. I've tried using

DECLARE CURSOR myCursor GLOBAL FOR SELECT * FROM inserted

and then letting the stored procedure reference the cursor, but then I
have to deal with the cursor data on a column-level basis, which is
not an option in this project because there are 100's of tables with
many columns, which might change over time.

So it is of extreme importance that I use INSERT INTO ... SELECT to
move the row data in a generic fashion.

I hope I have provided enough, yet not too much, information.

I would really appreciate any suggestions anyone might have as to how
I might handle this situation. Thanks.

Hank


Check this KB article, which gives a lot of detail on troubleshooting
that error. The most common issues seem to be that MSDTC isn't
running, or that there is a firewall between the servers.

http://support.microsoft.com/default...&Product=sql2k

Simon
Jul 20 '05 #2
sq*@hayes.ch (Simon Hayes) wrote in message news:<60******* *************** ****@posting.go ogle.com>...

Check this KB article, which gives a lot of detail on troubleshooting
that error. The most common issues seem to be that MSDTC isn't
running, or that there is a firewall between the servers.

http://support.microsoft.com/default...&Product=sql2k

Simon

Thanks for your input Simon.

MSDTC does not appear to be a problem. It is running on both
machines, and I can perform distributed transactions with no problem.
I can insert/update/delete linked server tables using four-part names
with no problem. Even the triggers between linked servers work fine,
as long as there are no recursive references in the trigger code,
regardless of whether the statement that causes the recursion gets
executed.

I also checked the troubleshooting link you sent.
- pingtest.bat runs fine on both machines
- both machines are using TCP/IP only
- transactions are not timing out because I can modify data using DTC
and linked servers with no problem when I'm not using triggers that
contain recursive references

The point in my original post that brought me here is the fact that I
get the error even if the recursive statement (the INSERT) doesn't get
executed. I thought this wouldn't be a problem, seeing as SQL Server
supports a server setting for 'nested triggers' which I've set to 1 on
both machines (and RECONFIGURE'd).

I first need to determine if what I'm doing is even supported. I've
searched BOL fairly extensively and all indications are that there
should be no problem. Additionally, I was hoping someone might
understand how SQL Server is compiling/executing this logic at the low
level, to better understand why the server, when executing a trigger,
even bothers looking at statements that don't get executed (i.e. the
false conditions of IF blocks).

Thanks again,
-Hank
Jul 20 '05 #3
I am having the SAME EXACT PROBLEM. Just implemented a new SQL FARM
and moved the databases over. Unable to perform an insert on linked
database...actu ally the fact that the statement is in the trigger and
not even called causes the error.

Did you ever get an answer/discover a resolution?
Your help in this matter would be greatly appreciated!

Thanks,
Reena

Jul 23 '05 #4
[posted and mailed, please reply in news]

(re***@cotterma ngroup.net) writes:
I am having the SAME EXACT PROBLEM. Just implemented a new SQL FARM
and moved the databases over. Unable to perform an insert on linked
database...actu ally the fact that the statement is in the trigger and
not even called causes the error.

Did you ever get an answer/discover a resolution?
Your help in this matter would be greatly appreciated!


In the thread there was a link to
http://support.microsoft.com/default...&Product=sql2k
Did you check whether there was anything that applies to you?

Else, it would helpful, if you gave the complete scenario with triggers
and statements. Preferrably simplified with only some few columns in
the table, but enough so anyone can run the example.

I reviewed the posting you refer to, but it wasn't clear to me how
his initial statement looked like, or at which point he got the error.
And in any case, your triggers may not look like his.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
3725
by: Raghuraman | last post by:
Hi, I was thinking abt linked servers deals only with multiple servers physically inter-connected. Now i come accross that we can link our non-sqlserver databases also. It is thru the E.manager by Security->linked servers -> Other databases -> access(jet 7.0).mdb
3
4422
by: Jonathan Bishop | last post by:
Hi. We are using MSDE2000 on a Point of Sale application. We need to keep a copy of a few key tables as up to date as possible for backup purposes. We are looking at using triggers over the source tables to update the target tables using a linked servers setup. Distributed Transaction Coordinator is running on both source and target servers. This following create command:
1
2313
by: TBlair | last post by:
I am about to start working on a project where I would be required to dynamically create linked servers during the execution of an application. One of the requirements is for the Link Servers to be created and dropped before and after the retrieval of the data. My question is about any type of cap on the number of linked servers SQL Server 2000 can have registered at any single time. If I find out that there is some type of cap, I would...
5
2656
by: yawnmoth | last post by:
say i have a form where, if a user clicks in it, all the text is highlighted, and where, if a user clicks on a button outside of the form, a certain text string is inserted where the cursor in the textarea is. why is it, then, that when i, immediatly after loading the page, click in the textarea, highlighting the text, click again in the middle of the text (effectively unhighlighting everything, as well), and then click on the button to...
8
9251
by: Bri | last post by:
Greetings, I'm having a very strange problem in an AC97 MDB with ODBC Linked tables to SQL Server 7. The table has an Identity field and a Timestamp field. The problem is that when a new record is entered, either from a form or from the table view of the table, when the record gets saved it immediately displays #DELETED# in all of the fields. However, if I close the form or table view and reopen the record has in fact been inserted. The...
3
11857
by: RandyMartin | last post by:
<<cross posted from microsoft.public.sqlserver.server>> Environment: Windows 2003 Standard Edition SP1, SQL 2005 EE RTM (both servers) I am getting the following messages when I run this query: select * from LINKEDSERVER.databasename.dbo.tablename
9
2485
by: dotnetfellow | last post by:
The system has worked for two years. SQL Server 2000 runs on Windows 2000 Server "A". Another instance of SQL Server 2000 was moved from Windows 2000 Server "B" old to Windows 2003 Server "B" new, by restoring a backed up copy from old to new. The system has cross server updates, where Server "A" is updating records in very large tables on Server "B".
2
7080
by: =?utf-8?B?UMSBdmVscyBNaWhhaWxvdnM=?= | last post by:
Hello, I have a problem. I've linked MySql server to MsSql, in MySql I have a table with Latvian data(character set is ucs2, ucs2_general_ci) and the problem is that when I use openquery to read data from MySQL server, some characters are not translated correctly! I receive question symbols instead of Latvian special characters. Maybe someone had this kind of problem with collation?
4
5752
by: --CELKO-- | last post by:
I need to convert a bunch of DB2 triggers to Oracle. Is there any kind of tools for this?
0
9758
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10773
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10378
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7087
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5756
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5956
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4571
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4170
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3196
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.