I am new to BizTalk, so any information would be greatly appreciated. I have
to integrate three third party applications. They all are using SQL Server
databases, but we have them sitting at customer sites and syncing up to our
master site.

I was thinking of using triggers on specific tables in each database to
replicate changes made from one application to another. Any feedback on this
topic would be helpful.

Thanks

[ Post a follow-up to this message ]

This is a pretty broad "architecture" question and the answer really depends
upon what type of integration you're looking for and what integration
capabilities your third-party apps have. If you're strictly trying to
replicate SQL database changes I'd look at using "SQL Replication" since
that's what it's designed for.

If you're trying to "integrate" transaction data between three different LOB
applications then BizTalk "may" work for you. It's hard to suggest an
architecture without knowing more about what you are trying to accomplish.

--
Jeff Lynch
"Ramblings From A Connected Universe"
http://codebetter.com/blogs/jeff.lynch


"LouRain" <lourain@nospam.nospam> wrote in message
news:%23hJ2zDf5FHA.3312@TK2MSFTNGP15.phx.gbl...
>I am new to BizTalk, so any information would be greatly appreciated. I
>have
> to integrate three third party applications. They all are using SQL Server
> databases, but we have them sitting at customer sites and syncing up to
> our
> master site.
>
> I was thinking of using triggers on specific tables in each database to
> replicate changes made from one application to another. Any feedback on
> this
> topic would be helpful.
>
> Thanks
>
>

[ Post a follow-up to this message ]

Hi Lou,

If you want to use SQL receive function in BizTalk to perform data
synchronization or replication task, you must have field in the SQL table
to determine which rows are not processed yet. Otherwise BizTalk will
retrieve duplicate data during polling. For your reference:

Stored Procedures
Just as the Receive adapter supports SQL statements with static WHERE
clauses, it also supports calls to stored procedures with static parameter
values. An example of a supported stored procedure call follows:

exec [Sales by Year] @Beginning_date = '1996-06-01', @Ending_Date =
'1996-07-10'
Similar to SQL statements, the adapter will run the SQL command at the
specified polling interval. Therefore, at each interval the adapter will
retrieve the same result set. Depending on your business requirements, this
may not be what you want. When you use a stored procedure, you may code
your procedure in such a way that the records you select are flagged, or
possibly deleted so they are not selected in future calls. An example of
such a stored procedure is as follows:

CREATE PROCEDURE SubScriptionProcedure
AS
DECLARE @Process_Date DateTime
SET @Process_Date=GetDate()
Update SubScription Table Set ProcessedDate=@Process_Date Where
ProcessedDate is NULL
SELECT SubscriptionId, Lastname, Firstname, MagazineName, DateOrdered FROM
SubscriptionTable WHERE ProcessedDate=@ProcessDate FOR XML AUTO, ELEMENTS
GO
The above example will first set a variable to the current date and time.
It then updates the ProcessedDate field to the current date and time for
any record where this field is null. The last step is to select the records
that just had their ProcessedDate updated and this result set is what is
returned to the Receive adapter.

Note: The Receive adapter does not support stored procedures that return
multiple result sets. However, the adapter does support multiple records in
a single result set.

Stored procedures must follow the same syntax rules as SQL statements to
return result sets as XML. Again, this additional syntax is either for xml
auto or for xml auto, elements as explained above.

Working With BizTalk Adapter for SQL Server
http://msdn.microsoft.com/library/d...-us/BTS_2004WP/
html/74cfc9d0-0974-4f4a-81f5-6768ff245df1.asp

Best regards,

WenJun Zhang
Microsoft Online Partner Support

This posting is provided "AS IS" with no warranties, and confers no rights.

[ Post a follow-up to this message ]