BizTalk Server General - SQL vs FILE Adapter question

Author

SQL vs FILE Adapter question

Ted Bae

2005-07-30, 8:47 pm

Once a file is received through FILE adapter, it is removed from the folder.
However, a SQL Table remains after it is received and the same table is
processed a multiple times unless the receive pipeline is stopped.

Is there any way to ensure a SQL table is received once only and re-received
when the table is updated? Do I have to delete the table once it is received?

Thanks for help,
--
Ted Bae

McGeeky

2005-07-31, 7:47 am

If you use a stored procedure with your SQL Receive port you can delete the
rows from the table once they have been processed. This stops the same rows
from being processed twice.

You do not have to delete the table. Only the rows in the table.

--
McGeeky
http://mcgeeky.blogspot.com

"Ted Bae" <TedBae@discussions.microsoft.com> wrote in message
news:76540326-736F-4EE7-9AF6-6E2A6E739FC5@microsoft.com...
> Once a file is received through FILE adapter, it is removed from the
folder.
> However, a SQL Table remains after it is received and the same table is
> processed a multiple times unless the receive pipeline is stopped.
>
> Is there any way to ensure a SQL table is received once only and
re-received
> when the table is updated? Do I have to delete the table once it is
received?
>
> Thanks for help,
> --
> Ted Bae

Marvin Smit

2005-08-01, 8:09 am

Hi,

just make sure you either delete the record just retrieved, or set a
"processed" flag on it (and include the processed part in the SELECT
statement)

Hope this helps,

Marvin Smit

On Sun, 31 Jul 2005 10:14:09 +0100, "McGeeky" <anon@anon.com> wrote:

>If you use a stored procedure with your SQL Receive port you can delete the
>rows from the table once they have been processed. This stops the same rows
>from being processed twice.
>
>You do not have to delete the table. Only the rows in the table.

Ted Bae

2005-08-01, 8:48 pm

Hi McGeeky and Marvin,
Thanks for the suggestions. However, I am having difficulty in generating a
SQL schema using a stored procedure. When I used a stored procedure (Select
firstname, lastname FOR XLM AUTO), it errors with "Failed to execute SQL
statement. Please ensure that the supplied symtax is correct". I have tried
with <For XML Auto, Elements>, <For XML Raw> but they all gave the same
error message.
What did I do wrong?
Thanks for your help,
Ted
--
Ted Bae

"Marvin Smit" wrote:

> Hi,
>
> just make sure you either delete the record just retrieved, or set a
> "processed" flag on it (and include the processed part in the SELECT
> statement)
>
> Hope this helps,
>
> Marvin Smit
>
> On Sun, 31 Jul 2005 10:14:09 +0100, "McGeeky" <anon@anon.com> wrote:
>
>
>

Yossi Dahan

2005-08-02, 7:50 am

Ted Bae wrote:
> Hi McGeeky and Marvin,
> Thanks for the suggestions. However, I am having difficulty in generating a
> SQL schema using a stored procedure. When I used a stored procedure (Select
> firstname, lastname FOR XLM AUTO), it errors with "Failed to execute SQL
> statement. Please ensure that the supplied symtax is correct". I have tried
> with <For XML Auto, Elements>, <For XML Raw> but they all gave the same
> error message.
> What did I do wrong?
> Thanks for your help,
> Ted
I'm not sure I remember correctly but I think when you run the add
generated schema wizard you do not need to include the for XML and after
the schema is generated you need to modify the port to include it
(or is it the other way around? :-) )

Yossi Dahan

Ted Bae

2005-08-02, 6:02 pm

Hi Yossi,
XMLDATA is required when a schema is generated and it has to be removed after.
Thanks for your help!
--
Ted Bae

"Yossi Dahan" wrote:

> Ted Bae wrote:
> I'm not sure I remember correctly but I think when you run the add
> generated schema wizard you do not need to include the for XML and after
> the schema is generated you need to modify the port to include it
> (or is it the other way around? :-) )
>
> Yossi Dahan
>