09-03-04 12:47 PM
Hi Scott/Alan
What are the deadlocks on? Are these on BizTalk tables, or the tables
you're trying to update with your stored procedure? If they're your own
tables you'll want to bear in mind that you should access/update resources
in a predictable order to ensure you don't get deadlocked when multiple
instances run concurrently. See "Minimizing Deadlocks" in SQL Server books
online for more info.
You could also use lock hints to ensure that you can escalate your shared
locks to exclusive locks if required. See the KB article "Resolving
blocking problems that are caused by lock escalation in SQL Server" at
http://support.microsoft.com/defaul...KB;EN-US;323630 for more
info.
You'll also want to ensure you have the latest fixes (such as
http://support.microsoft.com/defaul...KB;EN-US;841618 ) and that
you're stored procedure is setting any transaction properties correctly
(such as in http://support.microsoft.com/defaul...KB;EN-US;833190
) if the problems are specific to BizTalk.
Regards,
- Bill Ticehurst [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights
--------------------
| From: "examnotes" <AlanSmith@discussions.microsoft.com>
| Subject: RE: Having errors insert into sql server 2000 on biztalk 2004
| Date: Mon, 30 Aug 2004 07:09:09 -0700
|
| Hi Scott,
|
| I have had quite a few problems with transaction deadlocks whilst using
the
| SQL adapter. This usually happened when I had multiple ochestration
| instabnces running and calling the same SP through the same SQL adapter.
|
| To resolve this I se the retry interval for the adapter to 1 minute, and
the
| retyr count to 30. This got the data in eventually, but it seemed to
insert
| more warnings in the event log than data rows in the table!
|
| Just wondering if you are looping through the message, calling the SP for
| each record. If so, it could be the same problem I have.
|
| Regards,
|
| Alan
|
|
| I also noticed slow performance with the SQL adapter (see post in
| Orchestrations).
|
|
|
| "Scot S" wrote:
|
| > I'm trying to take an 810-edi message and insert the results into
sqlserver.
| > the 810 is broken down by covast.
| > I can get it to produce an xml file as output with no problem...
| > I don't want to turn around and have biztalk read in that xml and try
| > to insert it into the database, so I tried to do it in the original
| > orchestration.
| > Instead of having it dump out the xml file, I wrote another
construction
| > to flush it over to a stored procedure in sqlserver. This logically
seems to
| > work.. and I do get some data into the table.
| > But... I start getting throttling messages.. and deadlocks.
| > So.. the single file keeps processing.
| >
| > The adapter "SQL" raised an error message. Details
"HRESULT="0x80004005"
| > Description="Transaction (Process ID 126) was deadlocked on lock
resources
| > with another process and has been chosen as the deadlock victim.
| > Note : This is just one file being processed, with lots of individual
| > invoices in it.
| >
| > After the warnings it starts to suspend the service
| > and, then get final errors.
| >
| > Uncaught exception terminated service
| >
BizTalkMajors810Project.Orchestration_1(36e34072-b034-c08a-a3e0-3204f1f43418
), instance bb808b43-9a31-4164-97a9-42e0fc113c37
| >
| > Not sure what to do at this point. I'm just looking for an easy way to
| > get data into the database. I don't necessarily need the xml files.
| > Anyone have any ideas?
| >
|
[ Post a follow-up to this message ]
| 
