|
Home > Archive > BizTalk Server Orchestration > March 2005 > SQL Insert that return Identity, any samples
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 |
SQL Insert that return Identity, any samples
|
|
| Madani Benghia 2005-03-16, 7:51 am |
| 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
| |
| Benny Mathew 2005-03-16, 5:56 pm |
| 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
>
>
| |
|
| 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.
| |
| Scott Colestock 2005-03-16, 5:56 pm |
| 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.
>
| |
| Madani Benghia 2005-03-17, 3:04 am |
| 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...
>
>
| |
|
| 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.
| |
| Matt Milner 2005-03-18, 5:54 pm |
| 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.
| |
|
| 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?
| |
|
| 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. ????
| |
| Matt Milner 2005-03-21, 2:51 am |
| 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. ????
>
| |
|
| Ok, so I used sql profiler and I am even more confused now. I do not
get any error messages in profiler. My insert calls are traced and if
I copy them into query analyzer and run them they work. However, when
I run my app and submit, I instantly get an error in my even log with
the description above. The call in profiler showing my insert code
does not show up for a few minutes after that. Even when it is showing
it called in profiler, it does not work though. The records do not
show up in the table until I run it in query analyzer. I also verified
that the user account that this is running under in profiler has access.
| |
|
| I got this working, thanks for the help.
|
|
|
|
|