Hello,

I am looking for SQL Insert sample which returns the newly created id.I am
using HTTP adapter to send an xml message that contains 1 Order and many
order items.The message is received successfully and is then transformed
using a map and then inserted throught sql send port.When i run the
orchestration, the sql adapter compalins that it can't insert null value in
the field Order.Id because it is an identity field (which i understand).If i
turn identity off i finds that there is a new row inserted in the Order
Table but the values are null.The OrderDetails table is also receiving null
values and the rows are inserted.I think the problem is that my map is not
doing the mapping properly.

I tried Matt Musings sample:
http://www.m3technologypartners.com/Blogs/default.aspx

but his sample is not documented enough.He does not explain how the schemas
are created and how he links the identity field in the parent table to the
foreign key in the child table and how to tell the adapter to insert the
identity value in the order table.


By the way i have seen Scott Woodgate's sample but it does not talk about
identity fields.


Thanks in advance.


Madani

[ Post a follow-up to this message ]

hi,
In my case I use a stored procedure as shown below

CREATE procedure sp_AddressBook_InsertPerson
@FirstName varchar (30),
@MiddleName varchar (30) = NULL,
@LastName varchar (30),
@DoB datetime = NULL,
AS
insert into person(FirstName, MiddleName, LastName, DOB) values (@FirstName,
@MiddleName, @LastName, @DOB)
Select @@identity  as PersonID for xml raw, xmldata
GO

Note: remove the ', xmldata' that is appended at the end of the select
statement after you generate your schema for the SQL Adapter.
Note: I don't (obviously) pass a parameter for the identity column.

Regards
Benny



"Madani Benghia" <madani@hotmail.com> wrote in message
news:esFXmqgKFHA.2136@TK2MSFTNGP14.phx.gbl...
> Hello,
>
> I am looking for SQL Insert sample which returns the newly created id.I am
> using HTTP adapter to send an xml message that contains 1 Order and many
> order items.The message is received successfully and is then transformed
> using a map and then inserted throught sql send port.When i run the
> orchestration, the sql adapter compalins that it can't insert null value
in
> the field Order.Id because it is an identity field (which i understand).If
i
> turn identity off i finds that there is a new row inserted in the Order
> Table but the values are null.The OrderDetails table is also receiving
null
> values and the rows are inserted.I think the problem is that my map is not
> doing the mapping properly.
>
> I tried Matt Musings sample:
> http://www.m3technologypartners.com/Blogs/default.aspx
>
>  but his sample is not documented enough.He does not explain how the
schemas
> are created and how he links the identity field in the parent table to the
> foreign key in the child table and how to tell the adapter to insert the
> identity value in the order table.
>
>
> By the way i have seen Scott Woodgate's sample but it does not talk about
> identity fields.
>
>
> Thanks in advance.
>
>
> Madani
>
>

[ Post a follow-up to this message ]

I am doing exactly the same thing, I have had the insert working using
the order table, but I cant find any clear examples on how to insert to
a parent and a child table (i.e. Purchase Order and Purchase Items).
If anyone can add to this discussion on how to do this using a sp, it
would be appreciated.

[ Post a follow-up to this message ]

I haven't looked at it closely, but perhaps this sample would be of use:

http://blogs.msdn.com/skaufman/arch.../31/363920.aspx

Scott Colestock
www.traceofthought.net




"Chad" <chad.crosby@wise.com> wrote in message
news:1111000443.478248.85970@o13g2000cwo.googlegroups.com...
>I am doing exactly the same thing, I have had the insert working using
> the order table, but I cant find any clear examples on how to insert to
> a parent and a child table (i.e. Purchase Order and Purchase Items).
> If anyone can add to this discussion on how to do this using a sp, it
> would be appreciated.
>

[ Post a follow-up to this message ]

Thank you all.

Scott,

The sample you mentioned is the one i was looking for, and it uses SQLXML
and updategram.A northwind database insert example (order and orderDetails)
can be found here:

http://www.brainjar.co.za/blog.aspx...01_archive.html

I think we goona always depend on blogs and volunteers until Microsoft wake
up and provide us with a better documentation.

Regards,

Madani


"Scott Colestock" <scolestock@nospam_usa.net> wrote in message
news:uJG3gOoKFHA.2764@tk2msftngp13.phx.gbl...
> I haven't looked at it closely, but perhaps this sample would be of use:
>
> http://blogs.msdn.com/skaufman/arch.../31/363920.aspx
>
> Scott Colestock
> www.traceofthought.net
>
>
>
>
> "Chad" <chad.crosby@wise.com> wrote in message
> news:1111000443.478248.85970@o13g2000cwo.googlegroups.com... 
>
>

[ Post a follow-up to this message ]

I used these examples as guides and have everything setup and get this
error in my event log when running it...

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, anyone have any suggestions.

[ Post a follow-up to this message ]

You might have permissions ,but does the biztalk service that is hosting
your send port have permissions?  The account set as the identity for that
service will need insert permissions on your table.

Matt


"Chad" <chad.crosby@wise.com> wrote in message
news:1111176793.223098.60320@g14g2000cwa.googlegroups.com...
I used these examples as guides and have everything setup and get this
error in my event log when running it...

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, anyone have any suggestions.

[ Post a follow-up to this message ]

Thanks for the response, but unless I am doing something wrong...I have
already given them permission.  The BizTalk Application Users, Isolated
Host Users, and Administrators all have permissions on my user defined
tables.  It is odd because, I had no problems inserting when I used a
sql adapter setup with a stored procedure.  I have converted it to an
updategram to now handle parent child inserts ( I found more
documentation on this), but now I don't have permissions.  Any other
suggestions, or is there an account I am missing that should be added
to the db?

[ Post a follow-up to this message ]

I check by biztalk app service, and it is associated to my user account
locally on my machine.  This account already has permissions on my sql
db.   ????

[ Post a follow-up to this message ]

Turn on the SQL profiler and look for object access denied to see what
account is running and what it is getting denied on.  This should help
narrow down the problem.

matt


"Chad" <chad.crosby@wise.com> wrote in message
news:1111183721.244641.257010@o13g2000cwo.googlegroups.com...
>I check by biztalk app service, and it is associated to my user account
> locally on my machine.  This account already has permissions on my sql
> db.   ????
>

[ Post a follow-up to this message ]