|
Home > Archive > BizTalk Server Applications Integration > February 2004 > Error executing Biztalk Adapter Job
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Error executing Biztalk Adapter Job
|
|
|
| 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!!
| |
| Everett Yang 2004-02-23, 8:35 am |
| Do you have the "Disassemble Result Set" option selected for the receive
service?
Sincerely,
Everett Yang
DSI - Microsoft BizTalk Server
This posting is provided “AS IS” with no warranties, and confers no rights.
Subscribe at
http://support.microsoft.com/defaul...msdn/nospam.asp
&SD=msdn
| |
|
| Yes, I do.
Actually, I've found some more information on the problem.
I found that the trigger calls a stored procedure
(ra_srp_g) which in turn calls another stored procedure
(ra_get_tx_info_). Inside ra_get_tx_info_, it reads from
a table using WITH (READPAST). By commenting this out,
the Biztalk side works (I don't know about the
replication/trigger side though).
>-----Original Message-----
>Do you have the "Disassemble Result Set" option selected
for the receive
>service?
>
>Sincerely,
>
>
>Everett Yang
>DSI - Microsoft BizTalk Server
>
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Subscribe at
>http://support.microsoft.com/default.aspx?
scid=/servicedesks/msdn/nospam.asp
>&SD=msdn
>
>.
>
| |
| Everett Yang 2004-02-24, 1:36 am |
| With Disassemble Result Set option turned on, you may encounter the issue
described in Q814804.
Sincerely,
Everett Yang
DSI - Microsoft BizTalk Server
This posting is provided “AS IS” with no warranties, and confers no rights.
Subscribe at
http://support.microsoft.com/defaul...msdn/nospam.asp
&SD=msdn
| |
|
| I'm assuming when it says "host instance of SQL Server" it
is referring to the instance of SQL that I am using to get
the data from...execute my stored procedure against. If
that is the case, it is a different instance. In test, I
have my data on server a and my Biztalk databases on
server b. However, in production, both databases will be
on the same server (a cluster). Is there a way around
this?
Also this problem doesn't seem to rear it's head when I
comment out the WITH (READPAST) option in the trigger. Is
there a way to change the transaction level with the BT
adapter? I'm thinking that may help me out.
Thanks.
Tom
>-----Original Message-----
>With Disassemble Result Set option turned on, you may
encounter the issue
>described in Q814804.
>
>
>Sincerely,
>
>
>Everett Yang
>DSI - Microsoft BizTalk Server
>
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Subscribe at
>http://support.microsoft.com/default.aspx?
scid=/servicedesks/msdn/nospam.asp
>&SD=msdn
>
>.
>
| |
|
| Another update:
I added SET TRANSACTION ISOLATION LEVEL READ COMMITTED to
the top of my stored procedure and everything seems to
work fine. Will this cause any problems with Biztalk? Is
there a reason I should not do this?
Thanks.
Tom
>-----Original Message-----
>I'm assuming when it says "host instance of SQL Server"
it
>is referring to the instance of SQL that I am using to
get
>the data from...execute my stored procedure against. If
>that is the case, it is a different instance. In test, I
>have my data on server a and my Biztalk databases on
>server b. However, in production, both databases will be
>on the same server (a cluster). Is there a way around
>this?
>
>Also this problem doesn't seem to rear it's head when I
>comment out the WITH (READPAST) option in the trigger.
Is
>there a way to change the transaction level with the BT
>adapter? I'm thinking that may help me out.
>
>Thanks.
>Tom
>encounter the issue
>confers no rights.
>scid=/servicedesks/msdn/nospam.asp
>.
>
|
|
|
|
|