02-23-04 06:36 AM
I've got a BizTalk problem that is driving me nuts. I'm
using the BizTalk Adapter for SQL to execute a stored
procedure, return XML, and queue the messages. I know
this will work as I've seen the example and I have another
process doing the same thing. The problem seems to be
related to some triggers on the table that I'm selecting
the data from. First of all, here is the error I get in
the event log:
A worker thread reported a failure: [Error:] An error
occurred in BizTalk Server.
Details:
------------------------------
BizTalk Server failed to initialize a connection to
database: "InterchangeSQ" on server: "CANRNDNET". :''
BizTalk Server failed to join a transaction. Ensure both
the DTC and the SQL Server database are running.
[Source:] [HelpFile:] [HelpContext:] 0
The error code is shown in the data area below.
The operation was: ISQLWorker->Execute
The worker thread was working on:
Job Name: EMPRVtoeB Service SQL Adapter to Channel
SQL Script: Exec [GetFootPrintsForBiztalk]
The stored procedure I'm using is pretty simple. It just
selects the data from a table, then sets a process
indicator field so that the record doesn't get selected
the next time. Here it is:
CREATE PROCEDURE GetFootPrintsForBiztalk
AS
SET NOCOUNT ON
--Get environment and webserver information
DECLARE @App_Code VARCHAR(20),
@Web_Server VARCHAR(50),
@Database_Server VARCHAR(50),
@Environment VARCHAR(5)
SELECT @App_Code = 'EB'
SELECT @Web_Server = Web_Server, @Database_Server =
Database_Server, @Environment = Environment
FROM Common..NISSApplicationView
WHERE App_Code = @App_Code
BEGIN TRAN
SET NOCOUNT OFF
--Select new data
SELECT crdtstmp, fptype, fpname, desc0, fpstatus,
ownerid, obsoletedt, modeind, processind,
processdtm, mfrnm, modelnm,
@@ServerName AS ServerName,
@Web_Server AS eBServer,
@Database_Server AS eBDataSource,
@Environment AS Environment
FROM tpofootprint
WHERE processind = 'N'
FOR XML AUTO, XMLDATA
--Mark old data as queued by Biztalk
UPDATE tpofootprint
SET processind = 'Q',
processdtm = GetDate()
WHERE processind = 'N'
COMMIT TRAN
GO
Here is the trigger (I didn't write it, it's used for
replication):
--
-- CREATE UPDATE TRIGGER
--
CREATE trigger ra_updtrg_g on "dbo"."tpofootprint"
for UPDATE as
/**** BEGIN SYBASEREPLICATION CODE ***/
DECLARE @ramnumrows int
DECLARE @rarows int
DECLARE @raopid decimal
DECLARE @racrdtstmp datetime
DECLARE @rafptype varchar(10)
DECLARE @rafpname varchar(30)
DECLARE @radesc0 varchar(60)
DECLARE @rafpstatus varchar(10)
DECLARE @raownerid varchar(12)
DECLARE @raobsoletedt datetime
DECLARE @ramodeind char(1)
DECLARE @raprocessind char(1)
DECLARE @raprocessdtm datetime
DECLARE @ramfrnm varchar(30)
DECLARE @ramodelnm varchar(60)
DECLARE ra_updtrg_g_ins CURSOR FOR SELECT
crdtstmp,fptype,fpname,desc0,fpstatus,ow
nerid,obsoletedt,mo
deind,processind,processdtm,mfrnm,modeln
m FROM inserted
FOR READ ONLY
DECLARE ra_updtrg_g_del CURSOR FOR SELECT
crdtstmp,fptype,fpname,desc0,fpstatus,ow
nerid,obsoletedt,mo
deind,processind,processdtm,mfrnm,modeln
m FROM deleted FOR
READ ONLY
/**** END SYBASEREPLICATION CODE ***/
/**** BEGIN SYBASEREPLICATION CODE ***/
SELECT @ramnumrows = COUNT(*) FROM
CALSQLTST1_ra.ra_marked_objs_
WHERE primary_name = 'tpofootprint' AND enabled = 1 AND
owner = 'dbo'
IF @ramnumrows > 0
BEGIN
OPEN ra_updtrg_g_del
OPEN ra_updtrg_g_ins
FETCH NEXT FROM ra_updtrg_g_del INTO
@racrdtstmp,@rafptype,@rafpname,@radesc0
,@rafpstatus,@raown
erid,@raobsoletedt,@ramodeind,@raprocess
ind,@raprocessdtm,@
ramfrnm,@ramodelnm
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC CALSQLTST1_ra.ra_srp_g 'B',
@racrdtstmp,@rafptype,@rafpname,@radesc0
,@rafpstatus,@raown
erid,@raobsoletedt,@ramodeind,@raprocess
ind,@raprocessdtm,@
ramfrnm,@ramodelnm,
@raopid OUTPUT
FETCH NEXT FROM ra_updtrg_g_ins INTO
@racrdtstmp,@rafptype,@rafpname,@radesc0
,@rafpstatus,@raown
erid,@raobsoletedt,@ramodeind,@raprocess
ind,@raprocessdtm,@
ramfrnm,@ramodelnm
EXEC CALSQLTST1_ra.ra_srp_g 'A',
@racrdtstmp,@rafptype,@rafpname,@radesc0
,@rafpstatus,@raown
erid,@raobsoletedt,@ramodeind,@raprocess
ind,@raprocessdtm,@
ramfrnm,@ramodelnm,
@raopid OUTPUT
FETCH NEXT FROM ra_updtrg_g_del INTO
@racrdtstmp,@rafptype,@rafpname,@radesc0
,@rafpstatus,@raown
erid,@raobsoletedt,@ramodeind,@raprocess
ind,@raprocessdtm,@
ramfrnm,@ramodelnm
END -- fetch loop
CLOSE ra_updtrg_g_del
CLOSE ra_updtrg_g_ins
END -- numrows > 0
DEALLOCATE ra_updtrg_g_del
DEALLOCATE ra_updtrg_g_ins
/**** END SYBASEREPLICATION CODE ***/
If I comment out the UPDATE statement in the stored
procedure, it works fine, but you know the obvious
consequences of that. Also, if I comment out the trigger,
everything works fine. I thought it may be a possible
permissions issue, so I made the account dbo on the
database and a member of all the high security roles to no
avail. I'm at wits end here. I have run the stored
procedure (with the update statement in and the trigger
enabled) from the same account as the Biztalk server uses
to run the proc and it works fine. Does anyone have any
ideas? Something I've overlooked? I sure would
appreciate your help!!
[ Post a follow-up to this message ]
|
