I am currently submitting a purchase order form that inserts to a db
using a sql adapter and returning the identity using a 2 way port.  I
would like to add some functionality to this process enabling me to
submit the purchase items associated to the purchase order in a one to
many structure to the db.  So there would be inserts to 2 tables, the
purchase order table and the purchase items table with a foreign key to
the purchase order id.

Is this possible? Any suggestions? Are there samples?  I'm having
trouble finding info on this, and it seems like a common scenario to
have in biztalk.

Thanks in advance.

[ Post a follow-up to this message ]

I've got a sample on my blog of doing this with the SQL Adapter and
updategrams.

http://www.m3technologypartners.com...a5-9cbdffbc601d

Not too difficult, but you have to understand how updategrams work a little
in order to get it all to come together.

matt

http://www.m3technologypartners.com/blogs/


"Chad" <chad.crosby@wise.com> wrote in message
news:1110578279.986382.184020@z14g2000cwz.googlegroups.com...
>I am currently submitting a purchase order form that inserts to a db
> using a sql adapter and returning the identity using a 2 way port.  I
> would like to add some functionality to this process enabling me to
> submit the purchase items associated to the purchase order in a one to
> many structure to the db.  So there would be inserts to 2 tables, the
> purchase order table and the purchase items table with a foreign key to
> the purchase order id.
>
> Is this possible? Any suggestions? Are there samples?  I'm having
> trouble finding info on this, and it seems like a common scenario to
> have in biztalk.
>
> Thanks in advance.
>

[ Post a follow-up to this message ]

Thanks Matt.  I will try this, have you done this using a sp?  I don't
currently have mine setup to use updategrams.

[ Post a follow-up to this message ]

I also have a sample in my blog of sending chunk of XML to a stored
procedure. I think I only dealt with a single table, but once you have the
xml in the SP, you can use the OPENXML to update as many tables as you want.

Matt


"Chad" <chad.crosby@wise.com> wrote in message
news:1110910878.914061.133490@l41g2000cwc.googlegroups.com...
> Thanks Matt.  I will try this, have you done this using a sp?  I don't
> currently have mine setup to use updategrams.
>

[ Post a follow-up to this message ]

So I added it as an updategram because I couldn't get it to work and I
am getting the following error in my event viewer...

Description:
The adapter "SQL" raised an error message. Details
"HRESULT=3D"0x80040e09" Description=3D"INSERT permission denied on object
'REQUEST', database 'PORequisition', owner 'dbo'."
=EF=BB=BF<Root
xmlns:ns00=3D"urn:schemas-microsoft-com:xml-updategram"><?MSSQLError
HResult=3D"0x80040e09" Source=3D"Microsoft OLE DB Provider for SQL Server"
Description=3D"INSERT permission denied on object 'REQUEST', database
'PORequisition', owner 'dbo'."?></Root>".

I have permissions on the table (REQUEST) in sql to do all operations.
I dont know what else to look for, any suggestions.

[ Post a follow-up to this message ]

See my comment on the other thread, but basically it is the biztalk service
that needs insert permissions to that table, not you.

Matt


"Chad" <chad.crosby@wise.com> wrote in message
news:1111176868.833092.156470@g14g2000cwa.googlegroups.com...
So I added it as an updategram because I couldn't get it to work and I
am getting the following error in my event viewer...

Description:
The adapter "SQL" raised an error message. Details
"HRESULT="0x80040e09" Description="INSERT permission denied on object
'REQUEST', database 'PORequisition', owner 'dbo'."
?<Root
xmlns:ns00="urn:schemas-microsoft-com:xml-updategram"><?MSSQLError
HResult="0x80040e09" Source="Microsoft OLE DB Provider for SQL Server"
Description="INSERT permission denied on object 'REQUEST', database
'PORequisition', owner 'dbo'."?></Root>".

I have permissions on the table (REQUEST) in sql to do all operations.
I dont know what else to look for, any suggestions.

[ Post a follow-up to this message ]