Re: problem - get the @@identity in MAP

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Greg,

I can't find my problem.where i missed my steps

this is my updategram schema structure(i.e Destination schema for Map)

after
+-- studentdetail(Record)
|_at-identity(attribute)
|_Name(attribute)
|_city(attribute)
+--StudentMark(Record)
|_Sno(attribute)
|_M1(attribute)
|_M2(attribute)
|_M3(attribute)
|_tot(attribute)

This my input schema structure(i.e Source Schema for Map)

+-- studentdetail(Record)
|_Name(attribute)
|_city(attribute)
+--StudentMark(Record)
|_M1(attribute)
|_M2(attribute)
|_M3(attribute)

I am expecting your reply.
give some steps how to solve this.
Thanks in advance
Jana

"Greg Forsythe" wrote:

The problem is related to the message being sent to the SQL adpater.
What does the message look like now?

Greg

"Jana" <Jana@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:64C8AE7A-EF88-4B14-A8D3-7B9BF0984AD5@xxxxxxxxxxxxxxxx
Hi Greg,

Sorry to disturbing you,
Again I am having the same problem I can able to receive a file but I
can't
able to Insert a record into SQL Table It doesn't insert any record. When
I
am watching the "Biztalk administraion Console" have the warning as
follows

Warning -- 1

The adapter "SQL" raised an error message. Details "HRESULT="0x80004005"
Description="Multiple attributes or elements map to the same column in the
database table"
?<Root xmlns:ns00="urn:schemas-microsoft-com:xml-updategram">
<?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL
Server" Description="Multiple attributes or elements map to the same
column
in the database table"?></Root>".

Warning --2
The adapter failed to transmit message going to send port
"SQL://sansvr2/Northwind/". It will be retransmitted after the retry
interval
specified for this Send Port. Details:"Unknown Error Description ".

Can you find where I done the mistake? Please Guide Me
I don't have the problem while sendport as File but I have the problem
with
SQL port can you give the solution

Thanks In Advance
Jana

"Greg Forsythe" wrote:

Yes, it complains about multiple <after> blocks without a <before> block.
You need to add a <before> block to the output message if you are using
multiple <after> blocks.
This will require a change to the schema.

Here is an example that I tested sucessfully:

<?xml version="1.0" encoding="utf-8" ?>
<ns0:insertdetailsrequest
xmlns:updg="urn:schemas-microsoft-com:xml-updategram"
xmlns:ns0="http://studentmark";>
<ns0:sync>
<ns0:before/>
<ns0:after>
<ns0:studentdetails S_name="Name_0" City="City_1" updg:at-identity="1"
/>
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
</ns0:after>
<ns0:before/>
<ns0:after>
<ns0:studentdetails S_name="Name_0" City="City_1" updg:at-identity="1"
/>
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
</ns0:after>
<ns0:before/>
<ns0:after>
<ns0:studentdetails S_name="Name_0" City="City_1" updg:at-identity="1"
/>
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
</ns0:after>
</ns0:sync>
</ns0:insertdetailsrequest>


I have also tested the following message with a single <after> block and
this also works:
<?xml version="1.0" encoding="utf-8" ?>
<ns0:insertdetailsrequest
xmlns:updg="urn:schemas-microsoft-com:xml-updategram"
xmlns:ns0="http://studentmark";>
<ns0:sync>
<ns0:after>
<ns0:studentdetails S_name="Name_0" City="City_1" updg:at-identity="1"
/>
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentdetails S_name="Name_0" City="City_1" updg:at-identity="1"
/>
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentdetails S_name="Name_0" City="City_1" updg:at-identity="1" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
</ns0:after>
</ns0:sync>
</ns0:insertdetailsrequest>

Greg

"Jana" <Jana@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C2E5C4E5-C0D3-4E35-9F65-0A94D33DA09E@xxxxxxxxxxxxxxxx
Hi Greg,

I got that updategram message but when I try to change the outbound
port
as
sql port i can't able to insert.But if the outbound port is file port
then
I
got the following message.

<?xml version="1.0" encoding="utf-8" ?>
- <ns0:InserData xmlns:updg="urn:schemas-microsoft-com:xml-updategram"
xmlns:ns0="http://studentdata";>
- <ns0:sync>
- <ns0:after>
<ns0:studentdetails S_name="Name_0" City="City_1" updg:at-identity="1"
/>
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
</ns0:after>
- <ns0:after>
<ns0:studentdetails S_name="Name_0" City="City_1" updg:at-identity="1"
/>
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
</ns0:after>
- <ns0:after>
<ns0:studentdetails S_name="Name_0" City="City_1" updg:at-identity="1"
/>
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
</ns0:after>
</ns0:sync>
</ns0:InserData>

Thanks In Advance
Jana

"Greg Forsythe" wrote:

The problem here is relating the identity value to the dependent
records.
In this example I would imagine the the studentmark records all end up
using
the identity from the last student details record.

There are a couple of ways to address this.
1. Use an updg:id attribute - see SQLXML documentation
2. Change the structure of the updategram
<ns0:sync>
<ns0:after>
<ns0:studentdetails S_name="Name_1" City="City_1" updg:at-identity="1"
/>
<ns0:studentmark sno="1" .../>
<ns0:studentmark sno="1" .../>
<ns0:studentmark sno="1" .../>
<ns0:studentdetails S_name="Name_2" City="City_2" updg:at-identity="1"
/>
<ns0:studentmark sno="1" .../>
<ns0:studentmark sno="1" .../>
<ns0:studentmark sno="1" .../>
<ns0:studentdetails S_name="Name_3" City="City_3" updg:at-identity="1"
/>
<ns0:studentmark sno="1" .../>
<ns0:studentmark sno="1" .../>
<ns0:studentmark sno="1" .../>
</ns0:after>
</ns0:sync>

or possibly splitting each record into separate after blocks
<ns0:sync>
<ns0:after>
<ns0:studentdetails S_name="Name_1" City="City_1" updg:at-identity="1"
/>
<ns0:studentmark sno="1" .../>
<ns0:studentmark sno="1" .../>
<ns0:studentmark sno="1" .../>
</ns0:after>
<ns0:after>
<ns0:studentdetails S_name="Name_2" City="City_2" updg:at-identity="1"
/>
<ns0:studentmark sno="1" .../>
<ns0:studentmark sno="1" .../>
<ns0:studentmark sno="1" .../>
</ns0:after>
<ns0:after>
<ns0:studentdetails S_name="Name_3" City="City_3" updg:at-identity="1"
/>
<ns0:studentmark sno="1" .../>
<ns0:studentmark sno="1" .../>
<ns0:studentmark sno="1" .../>
</ns0:after>
</ns0:sync>

This would be done in the map, by altering the looping functoid
connections.

Greg




"Jana" <Jana@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7AD8734D-D688-4266-A3B9-5CD846008AC2@xxxxxxxxxxxxxxxx
hi Greg,

Which I send the updategram message is wrong i am able to get this
message
alone I have pasted below

<?xml version="1.0" encoding="utf-8" ?>
- <ns0:InserData
xmlns:updg="urn:schemas-microsoft-com:xml-updategram"
xmlns:ns0="http://studentdata";>
- <ns0:sync>
- <ns0:after>
<ns0:studentdetails S_name="Name_1" City="City_1"
updg:at-identity="1"
/>
<ns0:studentdetails S_name="Name_2" City="City_2"
updg:at-identity="1"
/>
<ns0:studentdetails S_name="Name_3" City="City_3"
updg:at-identity="1"
/>
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_1" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_2" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_1" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_2" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_0" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_1" M1="80" M2="80" M3="80"
tot="240" />
<ns0:studentmark sno="1" EName="ExamName_2" M1="80" M2="80" M3="80"
tot="240" />
</ns0:after>
</ns0:sync>
</ns0:InserData>

Thanks In Advance
Jana

"Jana" wrote:

Hi Greg,

I am new to this Biztalk server Please help me to get the solution.
This is the exact updategram message which i have send to the SQL.
.

Relevant Pages

  • Re: problem - get the @@identity in MAP
    ... this is my updategram schema structure(i.e Destination schema for Map) ... The adapter "SQL" raised an error message. ... The adapter failed to transmit message going to send port ...
    (microsoft.public.biztalk.general)
  • Re: Filtering on a send port based on node name
    ... define a property schema with the property you want to use to route on -- ... Setup your filters on the send port to route based on this property. ... > 2) Create a map from your original schema to this new schema. ...
    (microsoft.public.biztalk.general)
  • Re: Help in sending Biztalk messages to SQL Server 2005
    ... Use the SQL Adapter to generate an insert schema for you, ... From here you will want to create a map from your current schema to ... After that when you deploy it will create the SQL port for you and ...
    (microsoft.public.biztalk.general)
  • Re: Error: The document type XXX does not match any of the given schemas
    ... creating a custom send pipeline and include the flat file assembler ... There is a Send Port configured with the Flat File Assembler ... I have a map defined that converts the XML file to ... the doc schema property of the FF assembler is set to the FF schema. ...
    (microsoft.public.biztalk.general)
  • Re: SQL adapter/updategram error
    ... I think you forgot to include the map? ... >I have an updategram which inserts 9 text elements into a table after reading ... PolicyEffectDate is the second item in the schema (and ... here's my schema and the ...
    (microsoft.public.biztalk.general)