I know that the SQL adapter doesn't support both update and insert at the
same time.  Could someone direct me to an example that would overcome this
problem?  I have a very complex flat file which I convert and depending on
whether it is a PO or an Item, I put the data into one of two tables.  All
works perfectly, but to use this in the real world I need to either update o
r
insert in both tables so the data isnt repeated as a new record everytime
someone updates a PO.  I have a status field marked with N for New, U for
update or D for delete.  I'm assuming that I can call a stored procedure to
do this, but I'm not clear on how to write the SP to use the XML data from
Biztalk as the new data.  Any help would be appreciated!
--
Senior Developer
Furniture Brands Import Services

[ Post a follow-up to this message ]

Just write an SQL Stored Procedure to handle the problem.  It could check to
see if row exists, update if it exists, or add it if it doesn't exist.

When you run the SQL adapter from Visual Studio, it will build a schema
called something like SqlService.xsd.  You can rename it to something more
meaningful.  You can then map your data to this schema.  The SqlAdapter also
build a "dummy" orchestration which you can delete.

All the parms of the stored procedure will be exposed as attributes in the
schema.  If the stored procedure returns a table, the table and elements wil
l
also be in the schema.  The SQL adapter will ask for a Request and Response
root element name.  The Request root element name will wrap the stored
procedure parms, and the Response root element name will wrap any returned
data.

I don't know if you are using orchestration or not, but you would create a
Request/Response port (not sure if a Send-Only port would work).   I still
haven't figured out how to get the return-code from a Stored Proc back into
Biztalk.

Neal Walters
http://Biztalk-Training.com

[ Post a follow-up to this message ]