I made two SQL Server 2000 as linked server using same remote login
These both server are running on different Domain
Configuration Detail
1. Server A - MS SQL Server 2000 SP4, windows 2003 standard edition SP1
2. Server B - MS SQL Server 2000 SP3, windows 2003 standard edition
I have Created same login in both the servers called test
I have written below trigger on Server A Database
CREATE TRIGGER [insert] ON [dbo].[ITSM_PRIORITY_T ABLE]
FOR INSERT
AS
begin
set xact_abort on
SET NOCOUNT ON
IF SUSER_SNAME() <> 'SDOARF'
insert into test.OARF.dbo.A pplication (ApplicationNam e) values
('test1221')
end
But when i am inserting any record in ITSM_PRIORITY_T ABLE, it is giving me below error [microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: New Transactio cannot enlistin the specified trasaction coordinator. ]
[microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [ OLE/DB Provider 'SQLOLEDB' ITransactionJoi JoinTransaction returned 0x8004d00a].
[microsoft][ODBC SQL Server Driver][SQL Server]The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. Workaround:
1. I checkd DTS Service is running on both the servers
2. Net Access is given in both servers
3. I tried to use stored procedure which is called by insert trigger but won't work
4. I am able to insert between these two server using four part query
e.g insert into test.OARF.dbo.A pplication (ApplicationNam e) values ('test1221')
Please guide me as soon as possible if someone faced the same issue
This issue has really screwed up my head
Thank you
2 24744
Hi Friends,
My issue solved.
These are the steps followed by me.
1. Created Linked Server with the Server Type as ‘SQL Server’
A. provided the following in the security tab
i. Local Login
ii. Remote login
iii. Remote Password
iv. Selected Be made using this security context option and
provided Remote Login & Remote Password
B. Provided the following in the server options
i. Checked data access
ii. Checked RPC In
iii. Checked RPC Out
2. Checked “Enforce Distributed Transaction” in the source server
under Server Properties -> Connections
3. Started Distributed Transaction Coordinator is both the servers
Enabled Network DTC Access under Control Panel -> Adminstrative
Tools -> Component Services -> Computers -> My Computer. Right
click My Computer and go to properties, Go to MSDTC. Click on
Security configuration. Check Network DTC Access, Allow Inbound
and Allow Outbound in both the source and target and restarted
both the server (I mean the server and not sql server)
4. Then executed the query. Before I executed the query, I did SET
XACT_ABORT ON
Thanks & Regards,
Nirad
thank you a lot!
This solution for the trigger is working. You were only missing one thing to enable network DTC access properly within your step 3.
Under Authentication, you cannot select Mutual Authentication Required. Therefore, click to select one of the following check boxes:
• Incoming Caller Authentication Required
• No Authentication Required http://support.microsoft.com/default...b;en-us;817064 Sign in to post your reply or Sign up for a free account.
Similar topics |
by: simonfunston |
last post by:
Hi there,
I'm pretty new to SQL and am having some porblems with a linked server.
I have a table on a SQL server which stores employee information.
I also have a view on a linked server which stores the same information.
What I would like to happen is, whenever the view changes on the linked
server I want the information to be changed in the table on my server.
I've been trying to write a trigger to do this, but have had no
|
by: Steve Bishop |
last post by:
I have an Insert, Update and delete trigger on a table that works fine.
It records the changes into another table called tblTracking.
tblTracking records the changes so they can be uploaded to another
server off site. The problem is that it fires each time my DTS package
fires (every 15 minutes). The DTS package imports data from an ODBC
database then compares what is in the source/destination table with a
Insert Into, select not in (for...
|
by: Elvira Zeinalova |
last post by:
Hei,
We have 2 MS SQL SERVER 2000 installed on 2 different servers (2 separated
machines).
I am triing to connect them så that when one row is added to the table in
the database in main server - then the same row is added to the same table
in the second server database.
I made the insert trigger on the table in the first server ( the second
server is added as a linked server):...
|
by: hitendra15 |
last post by:
Can it be possible to create trigger/procedure
in following case
2 server server A and server B
A has db1
db1 has tblA
B has db2
db2 has tblB
can it be possible to create trigger on server A.db1.tblA as
|
by: Jchick |
last post by:
Im a newbie with a sql table in a MSDE database that contains fields of
CustName, BrokerName, Type, Status.
I am trying to write a trigger that does this:
1. When a new record in a table is added, I would like the selected
fields to be copied to another table (call it Master).
2. If that Master table already contains a record where the CustName =
CustName, then simply ignore. If CustName does not match anything, then
add the record to...
| |
by: gshawn3 |
last post by:
Hi,
I am having a hard time creating a Trigger to update an Oracle
database. I am using a SQL Server 2005 Express database on a Win XP Pro
SP2 desktop, linked to an Oracle 10g database on a remote Windows 2003
server. Both machines are on the same domain and very close physically
(<1ms ping).
I have set up the Oracle linked server in SQLEXPRESS, added the
login/pw information, and I can execute select and update queries
|
by: rdraider |
last post by:
I created and successfully tested a trigger on a test database. Now that I
want to put this on a production system, the create trigger statement takes
way too long to complete. I cancelled after a few minutes. The test
trigger took just a second to create. The test and production databases are
identical in design. Only difference is that there are users in the
production system.
Any ideas?
Thanks
|
by: Stout |
last post by:
Is it possible to create a trigger in one database, that after an
insert, will update a database on a different server?
If so, how would I do this?
Thanks.
Bill
|
by: HotFrost |
last post by:
Hello everyone,
i am trying to work with linked servers...
The local server is the one used by UPS worldwide software (it is Microsoft SQL Server Desktop Engine, v 8.00.2050). The linked server is 2005 MS server.
on update/insert event for some table i hooked up the trigger that connects to linked server and updates the record in some DB.
To test, i create a stored procedure that updates the record in UPS table that has that trigger....
|
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: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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: 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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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
| |