Table1 and Table2 are both on the same server (Server1) - in fact, in
the same database.  Table3 is on another server.  Both servers are MS
SQL 2000 sp4.  Server1 is on Windows Server 2000 sp4; Server2 is on
Windows Server 2003.  Each server is a linked server of the other and
RPC is set to TRUE for each (grasping at straws here).

The scripts for the tables are at the end.  I can run all of the
components individually without error.  That is, if I comment out the
EXEC statement in the trigger, records get added to TABLE2.  Likewise,
if I manually run the SP from query analyzer, it executes just fine.
But when I put it all together, the database on server1 just hangs --
I've let it go as long as 30 mins before shutting it down.  Any ideas
or pointers??

Any help is greatly appreciated!!

Thanks much,
Steve Lord

Here's the insert trigger for TABLE1:
ALTER TRIGGER [TABLE1_INSERT_Trigger_call_SP]
   ON  [dbo].[TABLE1]
   FOR INSERT
AS

INSERT INTO TABLE2 (MIS, LName, FName, SSN, DOB)
SELECT MIS, LName, FName, SSN, DOB FROM INSERTED

EXEC TryToInsertIntoOurLibraryTABLE3

HERE'S THE SP, TryToInsertIntoOurLibraryTABLE3

ALTER PROCEDURE [dbo].[TrytoInsertIntoOurLibraryTABLE3]

AS
SET NOCOUNT ON

INSERT INTO OurLibrary.OurLibrary.dbo.TABLE3
SELECT * FROM TABLE2 d
 WHERE not exists (SELECT * FROM OurLibrary.OurLibrary.dbo.TABLE3 a
WHERE a.patient_id = d.mis)
   and not exists (SELECT * FROM OurLibrary.OurLibrary.dbo.TABLE3 a
WHERE a.SSN = d.SSN)

TABLE1, i.e., TABLE1
CREATE TABLE [dbo].[TABLE1](
        [MIS] [int] NOT NULL,
        [SSN] [nvarchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [LName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [FName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [MName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Suffix] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DOB] [datetime] NULL,
        [Gender] [smallint] NULL,
        [Race] [smallint] NULL,
        [Ethnicity] [smallint] NULL,
        [Status_ID] [int] NOT NULL CONSTRAINT [DF_TABLE1_Status_ID]  DEFAULT
(0),
        [System_User_ID] [int] NOT NULL,
        [Source] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_TABLE1] PRIMARY KEY CLUSTERED
(
        [MIS] ASC
) ON [PRIMARY]
) ON [PRIMARY]

TABLE2, i.e., TABLE2
CREATE TABLE [dbo].[TABLE2](
        [MIS] [int] NULL,
        [LName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [FName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [SSN] [varchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DOB] [datetime] NULL
) ON [PRIMARY]

TABLE3, i.e., TABLE3
CREATE TABLE [dbo].[TABLE3](
        [Patient_ID] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
        [Last_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [First_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [SSN] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DOB] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_TABLE3] PRIMARY KEY CLUSTERED
(
        [Patient_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

I would much rather have a flag on that table that says "moved" defaulted to
0.  Then some background process would be responsible for waking up,
checking for any moved = 0, moving the rows, and then marking those rows as
moved = 1.  The fewer external dependencies in your trigger, the better.

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006