Problem With Triggers Between Linked Servers

Jul 20, 2005

I have two SQL Server 2000 machines (server_A and server_B). I've
used sp_addlinkedserver 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.dbo.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.dbo.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.dbo.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'
ITransactionJoin::JoinTransaction 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.dbo.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 myStoredProcedure
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

View 4 Replies


ADVERTISEMENT

Triggers And Linked Servers

Jun 17, 2004

I've been working on this for a couple of days now, trying different scenarious.

Problem:
I created a 'FOR INSERT' trigger on server1.dbs1.owner.table1 to collect information and insert to a linked server server2.dbs2.owner.table2. When I run with the trigger active, SQL Analyzer just hangs in there "Executing Query Batch ..." indefinately. When I don't create the trigger, but run all parts manually from SQL Analyzer, it works fine.

What did I try:
1. create a 'FOR INSERT' trigger on server1.dbs1.owner.table1 to insert into server1.dbs1.owner.table2. This worked fine.
2. create a stored proc to execute within the trigger on server1.dbs1.owner.table1 to insert to server2.dbs2.owner.table2. This just hangs in there "Executing Query Batch ..." indefinately.

What now?
I have a suspicion that something is not working correctly with the triggers and the linked servers. Has anyone encountered a similar problem and what did you do to overcome this? I greatly appreciate all responses and suggestions. Thanks all.

View 1 Replies View Related

Linked Servers And Triggers

Oct 21, 2004

I have two servers one on SQL Server 2000 one on SQL Server 7
I have setup the two servers so that they are linked and have added appropriate logins.
How it works is a record is inserted into a database on SQL 2000 which has a trigger on it that send the record to a stored procedure on the SQL 7 server, from there this places the record into a table, which calls a trigger. Now this all works fine when I use the query analyser however when I don't use it, the record does not get inserted anywhere. Now I have stepped through it and it works up until the last trigger, if I remove that everything works fine. However the code in this trigger works fine, as when I use the quuery analyser everything works just as it should.

Does anyone have any suggestions as to how I can get this to work?

Thanks :-)

View 2 Replies View Related

Triggers On Linked Servers

Apr 19, 2004

Can i write triggers beteen the linked servers.Here is the whole scenario. I have 3 servers. Server aaaa and server bbbb has replication in between them.Server aaaa is a publisher and server bbbb is the suscriber. I have another server cccc.So If i make any change on a table xxxx should effect the tablee xxxx on server aaaa and bbbb.So i am writing a trigger(for insert,update and delete on the table) xxxx on the server ccccc.So that trigger should take care of any DML(insert,update and delete) happend on table xxxx on server cccc and should effect on server aaaa and then the replication should take care of server bbbb.This want i am planning right now?Is it a good practice to implement in such a requirement?
Please help me.
Thanks.

View 5 Replies View Related

Triggers Across Servers

Aug 6, 2002

Hi All,

Can I write a trigger on one server and if I reference the server.db.owner.table can it work on a different server?

Thanks in advance,

Brad

View 1 Replies View Related

Triggers In Different Database Servers

Feb 16, 2004

Dear All,

I am trying to create a trigger on table A existing in Database A on Server A which on insert,update,delete would reflect these changes on table B in DatabaseB on Server B. I wrote the following syntax but it doesnt work

CREATE trigger trg_upd_tableA
On dbo.tableA
for UPDATE
as
--update the fields
declare @cust_no varchar
SELECT @cust_no = cust_no FROM inserted

update ServerB.DatabaseB.dbo.tableB
set
entity_type_cd = 'MFG',
cust_no = 6699,
name_en = 'NOVARTIS',
status ='A',
create_date=getdate()
,modify_date=getdate()
where cust_no= @cust_no


I get the error below

Another user has modified the contents of this table or view ; the database row you are modifying no longer exists in the database

Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server] MSDTC on server 'DatabaseB' is unavailable'



I would like anyone to reply to me as soon as possible please because i need the solution urgently


Thanks alot in advance,
Noha

View 2 Replies View Related

Creating Triggers Among Multiple Database Servers

Nov 14, 2000

Hello,

I am trying to create a trigger to update a table on a different database server. (Both databases are SQL server 7.0) Does anyone know the syntax of how to implement this?

Any help is appreciated!!
Thanks,
Lisa

View 1 Replies View Related

Federated Servers - Triggers And Identity Columns

May 1, 2008



Hi Folks.

I am trying to understand federated servers. So far I have been able to link 2 servers, create a distributed view, and run a select and insert query against the view.

The next thing that I need to understand is how to deal with triggers and with identity columns (ie columns that have auto-increments).

Our application is using triggers on tables in several cases to keep track of create and update dates. So there is a trigger that automatically updates the datetime field on a table whenever that row gets updated or changed or created. This way the programmer doesn't need to remember to update those date time values. Now I understand that in the distributed view architecture the triggers on the underlying tables may not work. Can any one comment on this?

As well, I have a table which has the identity enabled, so it is incrementing a counter and creating a unique integer key value for each new record. How does this work when we move to a distributed architecture? How do I assure a unique key is generated in the distributed view architecture?

Thank you in advance.

View 1 Replies View Related

Data Driven Subscriptions - Triggers Multiple Times On Prod Servers?

Mar 7, 2008



Hey!

DDS triggers 3 - 4 times on Report Servers with 15 mints apart..any ideas?

View 3 Replies View Related

SQL 2012 :: Triggers With Linked Server

Nov 10, 2014

I have a system where people can apply for registration and also a registration fee is involved. The screen where the application is captured has a couple of inserts into different tables. Each of these tables have triggers on them to transfer data to a linked server and they work fine. Today I just added another trigger on a different table but also part of this same save process. Now I am getting the following error:

Microsoft OLE DB Provider for SQL Server error '80004005'

The statement has been terminated.

/orisys.int.za/applicants/ApplicantInsert.asp, line 111

I do not know this error and think it might be time-out.

View 9 Replies View Related

Linked Server Insode Triggers Problems?

Aug 8, 2007

Dear all

I am trying to use linked server objects inside a trigger and have some major problems.

Just to explain what i am trying to achieve:

My server A is SQL 2000. When ever a row is added to a table on server A i would like to send some of the inserted values to server B which is a physically different computer and has SQL 2005.

To do that i created linked server object on the SQL 2000 side.

To test the linked server object i go:

Select * ServerB.Database.dbo.Table

This works perfectly and i get the results. I then test the same inside a stored procedure but i do some parameterised queries.

Select id from ServerB.Database.dbo.Table where id=@myId

This also works perfectly inside the stored procedure.

BUT NOW IT COMES THE FUN PART.

As soon as i place this inside the trigger it just doesnt work. My trigger has nothing else pretty much except for that. Here is a sample:

BEGIN
SET NOCOUNT ON
SET xact_abort ON

DECLARE @myValue nvarchar(50)

SET @myValue = '6357'

SELECT * from ServerB.Database.dbo.Table
Where id = @myValue

END

It just gives me a timeout error. But what is even worse is that after this the whole database is crashed and i have to restart the database service to make it work.

I checked both servers and they have the service DTS for the distributed transaction on. No proxies, no firewals. Also i checked the servers configuration and they have RPC,RPC OUT and Data Access enabled.

I have tried everything over the last week and nothing has worked for me.

Any advice would be much much apreciated.

Sincerely
Dan

View 5 Replies View Related

Linked Servers

Aug 17, 2006

Is it possible to link to an SQL 2005 server db from a SQL 2000 server? Is there a driver for this?

View 1 Replies View Related

What Are Linked Servers And What R Its Uses

Apr 5, 2008

can anybody tell me about Linked Servers and their uses and how to add a linked server to my Sql Server 2005.
 any help on this would be highly appreciated.

View 3 Replies View Related

Linked Servers

Jun 10, 2004

Hello All,

I have been trying to Link two sql servers on two different machines over the Internet without any luck. Can someone point me to information about doing this with good examples?

Thanks

View 2 Replies View Related

LINKED SERVERS

Jan 22, 2002

I currently have a main SQL Server which had a column on the majority of the tables. This column also had a check contraint on it. I dropped the column and the constraint and I now get the following message when trying to query the tables through a linked server 'OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.'.

Can anyone help

View 1 Replies View Related

Linked Servers.....

May 10, 2001

Hi All,
I have successfully linked a server and had SQL queries running
OK for a few weeks, but today I get the following message....

"OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time."
(Server: Msg 7353, Level 16, State 1, Line 1)

I've dropped the link and recreated it but I still get the same error
message. Can anyone help???

Thanks
David.

View 2 Replies View Related

Linked Servers In DTS...

Jun 25, 2001

Hi,

I'm using SQL Server 7.0 SP1.

I have a DTS package that requires me to link 2 servers
and query both in order to get my set of records that I
want to Transform. My query runs fine in QA. I copy and
paste it into the SQL Query window of the transformation.
I click Preview and all that happens is a quick flicker of
the DTS window. When I go to Transformations, there are
no source columns.

Here is a copy of my code that I'm trying to use:
-------------------------------------------------------
declare@start_dteas datetime,
@end_dteas datetime

SELECT@start_dte = date_data_range_start_dte,
@END_DTE = date_data_range_END_dte
FROMSIDDEV.SID_DEVELOPMENT.DBO.T901_RUN
WHERERUN_ID = (selectmax(run_id)
from
SIDDEV.SID_DEVELOPMENT.DBO.t901_run
whererun_type_cd = 'M')


SELECTRUN_ID
INTO#RUNS
FROMCRMDEV02.MDCORE.DBO.T901_RUN
WHEREDATE_DATA_RANGE_START_DTE >= @START_DTE
ANDDATE_DATA_RANGE_END_DTE <= @END_DTE

SELECTT70.*
FROM
CRMDEV02.MDCORE.DBO.T70_MD_UNIVERSE_RELATIONSHIP_M A
NGT AS T70 JOIN #RUNS
ONT70.RUN_ID = #RUNS.RUN_ID

DROP TABLE #RUNS
------------------------------------------------------
Is there an issue with Linked Servers and DTS?
Any help would be greatly appreciated.

Thanks in advance,
Darrin

View 1 Replies View Related

LINKED SERVERS

Aug 7, 2001

FOLKS
I GET THE FOLLOWING ERROR WHEN I RUN A SELECT FROM THE LINKED SERVER
THE LINKSERVER EXISTS AND THERE IS A RECORD IN SYSSERVERS TOO.

Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'

I AM NOT SURE WHERE TO GO FROM HERE.

REGARDS

GIRISH

View 1 Replies View Related

Linked Servers

Aug 10, 2001

Hi Gurus,
I am looking for literature to find out Pros and Cons of Using DTS Verses
Linked Servers in SQL 7.0.

My requirement can be done by either DTS or Linked servers. But I would like to know more about resorce utilisation of these tecniques before making a decission.

Can somebody suggest where to look for.

Thanks
sekhar

View 1 Replies View Related

Linked Servers

Sep 18, 2001

When I create linked server using integrated security and <they will be impersonated> option I get this:
Server: Msg 18456, Level 14, State 1, Line 1 Login failed for user ''
MS Technet says that SQL Server 7 doesn't allow double hops and to use mappings to standard security login to work around. @#%%~~@@#@#&^%@#
Impersonating to version 6.5 works fine.
Is there any way to link servers using ONLY integrated security?
Any help would be much appreciated.
Thanks

View 1 Replies View Related

Linked Servers

Jul 18, 2000

I have setup a linked Informix server in SQL7.0 and I am trying to create a
simple View with the following SQL statement

SELECT doc_code
FROM FOURSITE.foursite.informix.watdoc
WHERE (order_no = [PV01963B ])


I am getting the following error:

---------------------------
SQL Server Enterprise Manager
---------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid use of schema and/or
catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but
the provider does not expose the necessary interfaces to use a catalog
and/or schema.
---------------------------
OK Help
---------------------------

Can any one tell me how to correct this problem?

Thanks

Peter
pczurak@bigfoot.com

View 5 Replies View Related

Linked Servers

Apr 17, 2000

Is it possible to add a 6.5 database as a linked server to a 7.0 database and query from it ?

View 6 Replies View Related

About Linked Servers

Nov 3, 2000

Hi.
I'm working with SQL Server 7.0 and I've been reading about the linked server option. My question is, if it's possible to link a server with a SQLBase database 7.01.

I have download some drivers to my computer, the odbc driver for sqlbase and the sqlbase ole db driver as well, but when I try to add a new linked server in the enterprise, in the provider name option I can not see any provider name addressing to Centura or Sql base driver.

Has anybody an idea how to make this connection then? or the right way for doing it??

Thanks for any help can be offered.
Regards, Elvia.
:)

View 2 Replies View Related

LINKED SERVERS

Feb 10, 2000

I have two 7.0 servers and I want to run a distributed query.

I did sp_addlinkedserver to link server B to server A
and I did sp_addlinkedsrvlogin for a specific login.
After adding login when I try to access a table on server B it
says login failed

All the logins are NT authenticated (in both the servers, So
both servers have same logins.)



but it is not working.

View 1 Replies View Related

Linked Servers

Sep 1, 2000

Hi all
I try to configure Linked Servers from the query analyze , and have some troubles.
My steps are :
1. from the enterprise mennenger , add 2 new servers : srv1 , srv2 (both SQL7)
2. from the query analyzer of srv1.master: "sp_addlinkedserver 'srv2'"
3. from the query analyzer of srv1.master:
"sp_addlinkedsrvlogin 'srv2',false,null,'sa','myPass'"
4. select * from srv2.pubs.srv2Table
5. I get the next error :"error 6 : Specified SQL srever not found."

What do I do wrong ?
Eyal

View 2 Replies View Related

Linked Servers

Apr 19, 2000

Anyone out there use linked servers? I just tried this SQL7 feature and am very impressed. Are there any pitfalls to watch out for? Comments?

Thanks in advance.

Steve

View 1 Replies View Related

Linked Servers

Feb 14, 2002

I have created link servers a few time within SQL2000 and pulled data from Oracle. How would an Oracle Server or Application pull / See from SQL?

Anyone have any experience?

Thanks,
David

View 1 Replies View Related

Linked Servers

May 11, 2002

How to determine by means of T-SQL
if a linked server is available(for processing with its tables)?

View 1 Replies View Related

Linked Servers

Jun 26, 2002

This is my problem I have a Server A and Server B. There is a master table in Table A. I link Server A and Server B. When I look in Under Linked Server tables of B I can only C tables in Server A not the Server B 's Tables. Dpn't know what is happening. Could SOmebody help

View 1 Replies View Related

Linked Servers

Nov 1, 2004

We are creating a newServer grabbing data from an extrnal data source.

We need to perform regular audits to verify the record counts (for now) are the same.


We have 2 physically separate SQL servers 1 with old 1 with new.

I have created a linked server but when i do a query on the linked server the performace is pathetic (2mins 12 secs) to do a simple count.

Is there a better way, or can improv the perf?

Thanks
Greg C

View 3 Replies View Related

Linked Servers

Jan 5, 2006

I have two servers and they are linked servers. When I query a table on ON Box2 from Box1
The distributed query works fine but when I tried the oppisite it says 'SQL Server does not exist or access denied.'
What could be the reason.

Thanks.

View 2 Replies View Related

Linked Servers

Feb 27, 2006

Recently, I had Microsoft review why our SQL Db was unexpectedly terminating. They pointed to our Oracle Linked servers. They recommended that we try to uncheck the enable allow in process option on the ODBC driver. We are currently using the Microsoft OLE Db provider for our Oracle connnections as we were unable to get the Oracle ones to work with a linked server. When we unchecked this allow in process option near the end of the day our website began getting out memory errors from ODBC. It appeared to have been because of this change. Does anyone have any insight as to why changing this option could have caused this?

View 1 Replies View Related

Linked Servers

Sep 29, 2006

Hello,


Is there any limit to the number of linked servers i can add via EM?. where can i find more info on adding/configuring linked servers in MSSQL2k and the limitations/drawbacks if any?


Thanks in advance..

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved