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 4 14254 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 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
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
[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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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
|
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:
|
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...
|
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...
|
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...
| |
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
|
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".
|
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?
|
by: --CELKO-- |
last post by:
I need to convert a bunch of DB2 triggers to Oracle. Is there any
kind of tools for this?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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,...
|
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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();...
|
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...
| |
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
| |