David Marconnet gives out a simple but very useful tip when dealing with SQL adapter. I have to quote it here cause I need it all the time:
1. Use “add generated items” instead of trying to create the schema and ports yourself
2. Make sure your SQL in the procedure ends with “for xml auto, xmldata” BEFORE you add generated items. Then remove the “, xmldata” part when you have your orchestration running.
3. Make sure in the adapter properties of your send port that the “document target namespace“ and “response root element name“ match your SQLservice schema.
http://www.geekswithblogs.net/solid/archive/2005/09/15/53928.aspx?Pending=true
Make sure your SQL in the procedure ends with “for xml auto, xmldata” BEFORE you add generated items. Then remove the “, xmldata” part when you have your orchestration running.
What you got before remove ,xmldata
xml version="1.0" encoding="utf-16" ?>
- <GetCarbonCopy_Response xmlns="http://ffe.accenture.com/RNW_WorkRequest_EGD">
- <Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
- <ElementType name="RNW_WorkRequest_EGD" content="empty" model="closed">
<AttributeType name="WorkID" dt:type="string" />
<AttributeType name="TeamID" dt:type="string" />
<AttributeType name="WPNumber" dt:type="string" />
<AttributeType name="WRNUmber" dt:type="string" />
<AttributeType name="JobType" dt:type="string" />
<AttributeType name="JobCode" dt:type="string" />
<AttributeType name="Address1" dt:type="string" />
<AttributeType name="Address2" dt:type="string" />
<AttributeType name="Address3" dt:type="string" />
<AttributeType name="Address4" dt:type="string" />
<AttributeType name="Province" dt:type="string" />
<AttributeType name="PostalCode" dt:type="string" />
<AttributeType name="SystemOfOrigin" dt:type="string" />
<AttributeType name="WorkStatusID" dt:type="string" />
<AttributeType name="ArrivalTime" dt:type="dateTime" />
<AttributeType name="FinishingTime" dt:type="dateTime" />
<AttributeType name="WRDescription" dt:type="string" />
<AttributeType name="LastUpdateUserID" dt:type="string" />
<AttributeType name="Distric" dt:type="string" />
<AttributeType name="Area" dt:type="string" />
<attribute type="WorkID" />
<attribute type="TeamID" />
<attribute type="WPNumber" />
<attribute type="WRNUmber" />
<attribute type="JobType" />
<attribute type="JobCode" />
<attribute type="Address1" />
<attribute type="Address2" />
<attribute type="Address3" />
<attribute type="Address4" />
<attribute type="Province" />
<attribute type="PostalCode" />
<attribute type="SystemOfOrigin" />
<attribute type="WorkStatusID" />
<attribute type="ArrivalTime" />
<attribute type="FinishingTime" />
<attribute type="WRDescription" />
<attribute type="LastUpdateUserID" />
<attribute type="Distric" />
<attribute type="Area" />
ElementType>
Schema>
<RNW_WorkRequest_EGD xmlns="x-schema:#Schema1" WorkID="WorkID_15" TeamID="TeamID_14" WPNumber="WPNumber_17" WRNUmber="WRNUmber_19" JobType="JobType_9" JobCode="JobCode_8" Address1="Address1_0" Address2="Address2_1" Address3="Address3_2" Address4="Address4_3" Province="Province_12" PostalCode="PostalCode" SystemOfOrigin="SystemOfOrigin_13" WorkStatusID="Work" ArrivalTime="1999-05-31T13:20:00" FinishingTime="1999-05-31T13:20:00" WRDescription="WRDescription_18" LastUpdateUserID="LastUpdateUserID_10" Distric="Dist" Area="Are" />
GetCarbonCopy_Response>
After:
xml version="1.0" encoding="utf-16" ?>
- <GetCarbonCopy_Response xmlns="http://ffe.accenture.com/RNW_WorkRequest_EGD">
<RNW_WorkRequest_EGD WorkID="WorkID_15" TeamID="TeamID_14" WPNumber="WPNumber_17" WRNUmber="WRNUmber_19" JobType="JobType_9" JobCode="JobCode_8" Address1="Address1_0" Address2="Address2_1" Address3="Address3_2" Address4="Address4_3" Province="Province_12" PostalCode="PostalCode" SystemOfOrigin="SystemOfOrigin_13" WorkStatusID="Work" ArrivalTime="1999-05-31T13:20:00" FinishingTime="1999-05-31T13:20:00" WRDescription="WRDescription_18" LastUpdateUserID="LastUpdateUserID_10" Distric="Dist" Area="Are" />
GetCarbonCopy_Response>
As you can see the whole <Schema> section is gone.
=============================================
Found Todd's article about SQL adapter on MSDN today, good too:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/BTS_2004WP/html/ffda72df-5aec-4a1b-b97a-ac98635e81dc.asp