Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

trigger calling stored procedure to insert records across servers fails


Briefly, an insert trigger on table1 should do the following: (1)
appends the inserted records to a table2; (2) calls a stored procedure
that attempts to insert the records from table2 into table3.  However,
in practice, record(s) inserted into table1 cause the database to hang
- never errors, just hangs.

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]

Why would the trigger need to move these rows?

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

"Steve" <slinzl@gmail.com> wrote in message

news:1180458620.130801.316640@q66g2000hsg.googlegroups.com...

Aaron,

Thanks very much for the reply - TABLE1 and TABLE2 are not in my
control.  I'm not authorized to add columns to them - it was tough
enough to get permission to add a trigger.

That said, any thoughts on why execution of the individual components
works between servers but as a standalone process does not?  I forgot
to mention that I copied all the relevant tables, triggers, and
procedures to a test database and everything works fine when
everything's local.  The issue shows when trying to execute across
servers.

Thanks again!
-Steve Lord

On May 29, 1:34 pm, "Aaron Bertrand [SQL Server MVP]"

Allong Aaron's line of thinking, how about a trigger that puts the PK
value(s) into a "to-process" table, with a status of 0, then the move
process fires on some schedule to move them over to second server and sets
status to 1?

For your original issue, I didn't see any mention of an error that was
returned on failure.  Was it perchance a "distributed transaction" type
error?  In any case, we need that to assist you further.

--
TheSQLGuru
President
Indicium Resources, Inc.

"Steve" <slinzl@gmail.com> wrote in message

news:1180462121.722077.117130@q66g2000hsg.googlegroups.com...

DOH!   Of course, that should work.  I'll try that.  But I am still
very curious as to why the server-to-server case doesn't work, but the
local server one does.  I never let it time out to see a specific
error, I found that short of rebooting the server, I had to stop the
DTC on the SQL Server Services Manager and then re-start it.  The
error that then shows (although I guessed that it was related to the
stop/start of the DTC) is:

"[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].
Msg 7391, Level 16, State 1, Procedure
TrytoInsertIntoOurLibraryPatients, Line 19
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction."

Thanks again for the help,
Steve Lord

On May 29, 4:04 pm, "TheSQLGuru" <kgbo@earthlink.net> wrote:

Setting up DTC to work with SQL Server Linked Servers and cross-server
activity is a PITA.  Gets a bit more complicated if you happen to be running
clusters too.  :(  Been there, done that, got the T-Shirt!

Search microsoft for a number of KB articles that 'should' lead you to
success in that venture.

--
TheSQLGuru
President
Indicium Resources, Inc.

"Steve" <slinzl@gmail.com> wrote in message

news:1180469905.410227.269430@p47g2000hsd.googlegroups.com...

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc