Re: Fetch data from SQL DB without deleting records

From: Greg Forsythe (greg.forsythe_at_unisys.com)
Date: 09/19/04 

Date: Mon, 20 Sep 2004 10:24:51 +1200

You can select all records into a single result set and let Biztalk
disassemble this into multiple messages.
The following is from
==================================
BizTalk 2004 Disassembling Result Sets by Larry Franks:

Followup, here's the steps I've created for a kb article on this.
This is
based off the northwind database.
==================================
The following is a step by step guide on creating a receive port that
will
pull data from the employees table in the NorthWind database and split
the
inbound data into one document per record returned.

Create document schema:
1: Open Visual Studio and create a new BizTalk project named
NorthWindSplit
2: Right click on the project in Solution Explorer and select Add,
then Add
Generated Items. In the Generated Item wizard highilght Add Adapter
and
click Open.
3: Select the SQL entry, make sure that we are pointing to the SQL
server
that contains the BizTalk message box database and that the message
box
database is selected. Click next.
4: Click the Set button and enter the information to connect to the
SQL
server that houses the NorthWind database. Once you have entered the
information click next.
5: For the target namespace enter http://NorthwindEmpSplit, leave the
port
set to receive and for the root element enter EmpRoot. Click next.
6: On this page select to use a select statement and click next. For
the
select statement enter:
select * from employees for xml auto
click next. Then click finish.

You should have a SQLService.xsd file added to the project at this
point.

7: Open the sqlservice.xsd file and select the employees element. In
properties for this set the max occurs and min occurs to 1.
8: Move the employees record to the root level (direct child of
<schema>)
and delete the EmpRoot element.
Save this schema.

Create the Envelope schema:
1: Add a new schema to the project, name it EmpEnvelope.xsd.
2: Open the new schema and highlight the <schema> element. In the
properties window set the Envelope property to yes. Set the target
namespace to NorthwindEmpSplit. This must match the target namespace
for
the SQLService.xsd file.
3: Rename the root element to EmpRoot. In properties for EmpRoot
select
the Body XPath property and click the ... button. Select the EmpRoot
element. This should set the Body XPath to /*[local-name()='EmpRoot'
and
namespace-uri()='http://NorthwindEmpSplit'
4: Right click the EmpRoot element and insert a schema node, Any
element.
This should create a child element named <Any>
5: Select the <Any> element and in properties set the Namespace to
##any.
Set the Process Contents property to Lax.

Save this schema.

Adding a custom pipeline:
1: Right click on the BizTalk project in Solution Explorer, add a new
item.
 For the item select a receive pipeline and name it EmpSplitPipe.btp.
2: Open the pipeline file, from the toolbox drag an xml disassembler
to the
disassembler stage in the pipeline.
3: Select the disassembler you added, in the properties dialog set the
Document Schemas to the SQLService.xsd and set the Envelope Schemas to
EmpEnvelope.xsd.
Save this file.

Building and deploying:
1: Create a keyfile to sign the assembly and set this in properties
for the
project.
2: Build the project, then deploy.
3: Copy the dll for this assembly to the \program files\microsoft
biztalk
server 2004\pipeline components folder

Configuring BizTalk:
1: In Biztalk explorer create a new receive port, under this create a
new
receive location and set the transport type to SQL.
2: Select the URI entry and click the ... button. Here set the
connection
string to point to the SQL server that contains the Northwind
database.
3: Select the SQL Command entry and click the ... button. In the
Project
field select the assembly we deployed for our project and in the
Schema
field select the SQLService schema.
4: Change the Document Root Element Name to EmpRoot. Ok out of the
SQL
Transport Properties.
5: Set the receive pipeline to use the pipeline we created in our
assembly.
6: create a new send port. This should send to a file location. For
filter settings filter on BTS.ReceivePortName==<name of receive port>
7: Start the send and receive locations. The receive location should
start
polling the SQL adapter, submit the data to the pipeline which should
split
it into individual documents that are written out through the file
adapter.
==================================

Larry Franks

"newbie" <newbie@discussions.microsoft.com> wrote in message
news:B80E8B30-37C5-44B0-9D56-19A50D74591C@microsoft.com...
> I fetch data from a SQL DB using stored procedures (see below) and
> BizTalk2004. Data from a single record is collected and sent as a message.
> Since I want to send data from a single record in each message, I search
for
> the first record, fetch the data and then delete the record. I delete the
> record because I want to send the second record in the next message.
> However, I would keep all the records (data) in the database and still be
> able to send each record as a single message. Any ideas how create a
stored
> procedure that accomplish that?
>
> ---
> CREATE PROCEDURE sp_collectdata
> @index int
> AS
> set @index = (select min(pkid) from event)
> select A, B, C
> from event where pkid = @index
> for xml auto
> delete from event where pkid = @index
> GO
> ---

Loading