- This topic has 9 replies, 1 voice, and was last updated 9 years, 2 months ago by
.
-
Posts
-
-
Hi,
I have have two nodes in the destination schema, "before" node and "after" node. "before" node has nothing in it. I just want the empty tag of it in the out put and "after node" has data to store in to table.
How can I make them to loop one after other?
I mean, I want like this:
Sync
before /
after
information to insert 1 /after
before /
after
information to insert 2 /after
before /
after
information to insert 3 /after
/SyncRight now I am geting out put some thing like following, If i use the Looping Functoid to link both nodes with one input node.
sync
before /
before /
before /
after
information to insert 1 /after
after
information to insert 2 /after
after
information to insert 3 /after
/sync
This is wrong. SQL Adaptor is raising error for this out put. -
draw from record source node to looping functoid and then looping functoid to parent node of 'before' and 'after'
-wa
-
Hi,
Thank you for your reply.
I tried that. I am getting error. It says that that new record is invalid. So I think we can not create our own node between sync and after. Because SQL Adaptor couldnt reconize it.
I am getting following error.
"HRESULT="0x80004005" Description="The specified XML namespace is invalid"
%ufeff<Root xmlns:ns00="urn:schemas-microsoft-com:xml-updategram"><?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="The specified XML namespace is invalid"?></Root>".
-
Yeah I did that draw from source to Looping functoid then to before node and after node. I am still geting same out put. All before blocks appers then..all after blocks.
-
-
[quote user="Siva"]Yeah I did that draw from source to Looping functoid then to before node and after node. I am still geting same out put. All before blocks appers then..all after blocks.[/quote]
Re-read the instructions. I said to the PARENT of before node and after node… -wa
-
Hi,
Thank you for your help.
If I connect to the parent, which is sync here, then I will get multiple sync in the out put for one input. If I have multiple sync in the out put then SQL Adapter will handle each sync as a single transaction so I will end up with multiple transaction for one input. If one transaction fails rest of the sync will commit and only the failed one will roll back. I dont want that. If one record failed for one input then I want all of them to roll back. So I want to keep one synce for one input.
If I create a parent for after and before and put that as child for sync then I can solve this problem. but it is not working.I am getting following error.
"HRESULT="0x80004005" Description="The specified XML namespace is invalid"
%ufeff<Root xmlns:ns00="urn:schemas-microsoft-com:xml-updategram"><?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="The specified XML namespace is invalid"?></Root>".
So the new node, which i create as a child for Sync and parent for after and before is invalid.
How can I solve this? Please help me to fix this issue.
Thank you in advace,
-
[quote user="Siva"]
Hi,
Thank you for your help.
If I connect to the parent, which is sync here, then I will get multiple sync in the out put for one input. If I have multiple sync in the out put then SQL Adapter will handle each sync as a single transaction so I will end up with multiple transaction for one input. If one transaction fails rest of the sync will commit and only the failed one will roll back. I dont want that. If one record failed for one input then I want all of them to roll back. So I want to keep one synce for one input.
If I create a parent for after and before and put that as child for sync then I can solve this problem. but it is not working.I am getting following error.
"HRESULT="0x80004005" Description="The specified XML namespace is invalid"
%ufeff<Root xmlns:ns00="urn:schemas-microsoft-com:xml-updategram"><?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="The specified XML namespace is invalid"?></Root>".
So the new node, which i create as a child for Sync and parent for after and before is invalid.
How can I solve this? Please help me to fix this issue.
Thank you in advace,
[/quote]
Ok, I can see here the specific convention you are working with.
http://support.microsoft.com/kb/316018
The updategram is a specific convention of SQL server and thus you must to conform to the structure of this schema. You are not free to just modify it as I had originally suggested.
Looks like you really one one Before node and one After node, with all of the updates inside that; so it looks like you might want to connect looping functoid to "information to insert" node. It would seem that this makes all of the db operations into a single transaction which would rollback like you intend. Let us know if this works.
sync
before /
after
information to insert 1
information to insert 2
information to insert 3/after
/sync– wa
-
Hi,
Thank you for your help.
I am sorry, I think I didnt explain u the situation clearly. Let me tell u clearly in this post.
We get POs from customers. Each PO will have Header, Detal and Serial information. It is possible to get duplicate PO and/or more than one PO in each batch file. So we want to create our own unique ID for each PO and store with the data into 3 tables, Header, Detail and SerialInfo. I have two option to create this ID.Use script in the map to create ID or make the ID could as Identity in the Parent table then use the at-identity atributes to pass the value to child tables.
Eg PO
H po 1
D
D
S
H po2
D
S
SThe problem I am faicing :
If I put all records in to one after node as u said in the last reply then the parent table will get the unique ID for the new records but all the newly insert records in the child table get the last ID value of the parent table. That is wrong
For this example Lest say po1 have ID 1 and po2 has ID 2 then all of the D and S of po1 should have ID 1 and others 2.If I use one after block then all the D and S have ID 2. That is why I decide to put a loop for each PO with after.
Let me know if you need more info.
-
Siva –
I understand the multiple requirements here.
Consider this thought – even if you reject a batch file there is a still a big workflow problem; what were you going to do – edit out the duplicate PO by hand and rerun the file each time?
What I have seen is this: load the batch files into temp tables and then do the work of weeding out the duplicate transactions in the database; simply don't propagate those to the production tables and drop them with the temp table. Or keep them as a history of errors.
In this scenario we queried the database for the next available index number from the map.
I recommend against creating a system where a file fails and someone has to manually do something with it.
-wa
-
-
-
-
-
-
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.