|
Home > Archive > BizTalk Server Orchestration > March 2005 > need to insert through sql adapter data with one to many relationship
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
need to insert through sql adapter data with one to many relationship
|
|
|
| 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.
| |
| Matt Milner 2005-03-11, 8:50 pm |
| 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.
>
| |
|
| Thanks Matt. I will try this, have you done this using a sp? I don't
currently have mine setup to use updategrams.
| |
| Matt Milner 2005-03-15, 8:49 pm |
| 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.
>
| |
|
| 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.
| |
| Matt Milner 2005-03-18, 5:54 pm |
| 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.
|
|
|
|
|