SQL Adapter with Map blocks SQL Server?
- From: somefakeaddress@xxxxxxxxxxx
- Date: 13 Apr 2005 09:03:56 -0700
We have a receive port that executes a stored procedure via an SQL
Adapter on regular intervals. If we don't specify any inbound/outbound
maps on the receive/send ports, but instead just use
XmlReceive/Passthrough as receive/send pipeline and simply dump the XML
data returned from the stored proc to disk, everything works fine.
However... if we specify inbound and outbound maps, SQL Server
hangs/freezes/blocks something when executing the stored proc.
If we take a look in SQL Server, under Management->Current
Activity->Locks/Process ID, we can see the following:
- spid 58
- spid 59 (Blocking)
- spid 60...64
- spid 65 (Blocked by 59)
- spid 66
Using SQL Profiler, we see this:
Event TextData Appname Dur. SPID
(this is when we enable the adapter)
SP:Starting exec sp_oledb_ro_usrname VS.NET 73
SP:Completed exec sp_oledb_ro_usrname VS.NET 0 73
SP:Starting exec spDoStuffHere VS.NET 73
SP:Completed exec spDoStuffHere VS.NET 3865 73
(this is when the adapter executes the procedure the first time)
SP:Starting exec sp_oledb_ro_usrname BzT2004 59
SP:Completed exec sp_oledb_ro_usrname BzT2004 0 59
SP:Starting exec spDoStuffHere BzT2004 59
SP:Completed exec spDoStuffHere BzT2004 4156 59
(this is when we disable the adapter)
SP:Starting exec sp_oledb_ro_usrname VS.NET 81
SP:Completed exec sp_oledb_ro_usrname VS.NET 0 81
SP:Starting exec spDoStuffHere VS.NET 81
SP:Completed exec spDoStuffHere VS.NET 3941 81
So... what is going on here?
Why is SQL Server affected by whether or not we have inbound/outbound
maps on the receive/send ports? I thought BizTalk 2004 gathered up all
the data received from the proc, disconnected, and THEN performed the
transformation?
Why does Visual Studio.NET (Windows Operating System, if performed via
BizTalk Administration Console) execute the procedure when
enabling/disabling the adapter?
Oh, and the maps work just fine if we test them by using an XML file
containing the data, instead of running the procedure in order to get
it, so it doesn't seem to be a map problem. The stored proc also works
just fine when executed from either Query Analyzer or from a receive
port with no inbound map.
Or maybe you're not supposed to perform transformations directly on an
SQL port, but rather dump the XML to disk and then have another set of
receive/send ports pick it up and do the transform?
I'm confused...
Thanks
Niklas Uhlin
.
- Prev by Date: Re: BTS 2004 Map Error - No Byte Order Mark. Cannot Switch to Unic
- Next by Date: Re: BTS 2004 Map Error - No Byte Order Mark. Cannot Switch to Unic
- Previous by thread: TestMap Restriction
- Next by thread: Custom Pipeline Subscription Issue
- Index(es):
Relevant Pages
|
