sql server replication: I am trying to complete a distributed query on two seperate sql instances,
two seperate databases, two seperate tables. I am trying to do an insert from
one database to the other when an insert occurs in the originating database.
I have attempted both a trigger running the sql query and a trigger calling a
sproc. I have copied the table from the second database into database one,
renamed it, and performed the operation flawlessly through a trigger and a
stored procedure. The only problem I have is when I try to go the seperate
server. I have executed the initial insert which fires the trigger from query
analyzer. It says executing query batch and then just continues to say this
and blows up the server which then has to be restarted. I have looked at the
locks that are coming up and see that some of them are X locks. I have used
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to try and override these
locks temporarily. I know this could cause bad data if used but am just
trying to figure out the problem. I can execute the same insert from database
one to database 2 through query analyzer just fine. I'm thinking there may be
some security settings preventing the trigger from performing this but am not
sure. I have been talking with Paul Ibison on a seperate post on this but I
think he has tired of my questions. Any advise would be greatly appreciated
as I have had my head buried in books, newgroups, and google for the past
four days now.

For the love of all that is sacred someone please help my deadline for this
project is fast approaching.
--

Matt,
I never tire of queries :) I'm just very pressed for time
at the moment as we're in the middle of moving our
servers to a hosted environment and I'm managing the
database side of things. If possible, I'll take a look
late this afternoon (UK time) if nobody else has replied.
Rgds,
Paul Ibison (SQL Server MVP)

Paul,
Thanx, I greatly appreciate any time you can set aside whenever it is :)

Hope your transition goes smoothly,

Matt,
please try this type of approach:

CREATE TRIGGER ti_tst ON [dbo].[tst]
FOR INSERT
AS
set xact_abort on
insert into [dhsrvskh504\stst].dba_admin.dbo.tst (name)
select name from inserted

This works fine for me - hopefully for you as well.

Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com

(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Matt,
if you still have problems, please look at these
troubleshooting guides:
http://www.kbalertz.com/Feedback.aspx?kbNumber=250367
http://support.microsoft.com/kb/306212
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com

(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Paul,
I greatly appreciate all help that you have provided. It appears that what
you have telling me the whole time was correct. After much looking,
searching, and cussing I found that it was a problem with windows 2003 and
the way it handles RPC requests. For any other here is a link to article to
work around.

http://support.microsoft.com/?kbid=555017

Once again thank you for all your help through this.