How to Execute a DiffGram

This article on MSDN:

"Microsoft SQL Server 9.0 Technical Articles, XML Options in Microsoft SQL Server 2005"

states that it is possible to execute a DiffGram using SqlXmlCommand. However, when I execute this code:

static string NorthwindConnString = "Provider=SQLOLEDB;Server=NJSQL06;database=Interlink_GISLink90;Integrated Security=SSPI";
SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
cmd.CommandStream = new FileStream(@."C:\Development\SQLXML\ApplyDiffGram\ApplyDiffGram\bin\Debug\DiffGramFile.xml", FileMode.Open, FileAccess.Read);
cmd.CommandType = SqlXmlCommandType.DiffGram;
cmd.SchemaPath = @."C:\Development\SQLXML\ApplyDiffGram\ApplyDiffGram\bin\Debug\XSDTypedSchema.xsd";
cmd.ExecuteNonQuery();

I get this error message:

"Empty update, no updatable rows found"

My DiffGram and mapping-schema are pasted below. Does anyone know what I'm doing wrong?

--Nancy

This is my DiffGram:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:mapping-schema="XSDTypedSchema.xsd">
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<Interlink_GISLink90DataSet>
<GISVessel diffgr:id="GISVessel1" msdata:rowOrder="0" diffgr:hasChanges="modified">
<Vessel_id>2578</Vessel_id>
<Name>SHENG FENG</Name>
<DOT_Number>2578</DOT_Number>
<gb_DB_ID>71</gb_DB_ID>
<gb_DBID_PK>2578</gb_DBID_PK>
<gb_DB_OWNER_ID>156</gb_DB_OWNER_ID>
<Logical_Cancel_Value>0</Logical_Cancel_Value>
<GB_DB_TimeStamp>2002-04-24T14:11:00.943Z</GB_DB_TimeStamp>
<Alias_Name>SHENG FENG </Alias_Name>
<Year_Built>1995</Year_Built>
<Ex_Name>SHENG FENG</Ex_Name>
<Freight_Code>SHFEN</Freight_Code>
<Vessel_State>C</Vessel_State>
<Comments>John Adams</Comments>
<Ownership_Type_id>4</Ownership_Type_id>
</GISVessel>
</Interlink_GISLink90DataSet>
<diffgr:before>
<GISVessel diffgr:id="GISVessel1" msdata:rowOrder="0">
<Vessel_id>2578</Vessel_id>
<Name>SHENG FENG</Name>
<DOT_Number>2578</DOT_Number>
<gb_DB_ID>71</gb_DB_ID>
<gb_DBID_PK>2578</gb_DBID_PK>
<gb_DB_OWNER_ID>156</gb_DB_OWNER_ID>
<Logical_Cancel_Value>0</Logical_Cancel_Value>
<GB_DB_TimeStamp>2000-01-12T10:09:00.943Z</GB_DB_TimeStamp>
<Alias_Name>SHENG FENG </Alias_Name>
<Year_Built>1995</Year_Built>
<Ex_Name>SHENG FENG</Ex_Name>
<Freight_Code>SHFEN</Freight_Code>
<Vessel_State>C</Vessel_State>
<Comments>Edmund Burke</Comments>
<Ownership_Type_id>4</Ownership_Type_id>
</GISVessel>
</diffgr:before>
</diffgr:diffgram>
</ROOT>

This is my mapping schema inside XSDTypedSchema.xsd:

<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="GISVessel">
<xs:complexType>
<xs:sequence>
<xs:element msdata:ReadOnly="true" msdata:AutoIncrement="true" name="Vessel_id" type="xs:int" />
<xs:element name="Name">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="28" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Lloyds_No">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="7" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="DOT_Number">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="16" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Call_Sign">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="9" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="gb_DB_ID" type="xs:int" />
<xs:element name="gb_DBID_PK" type="xs:int" />
<xs:element name="gb_DB_OWNER_ID" type="xs:int" />
<xs:element name="Logical_Cancel_Value" type="xs:int" />
<xs:element name="GB_DB_TimeStamp" type="xs:dateTime" />
<xs:element name="Alias_Name">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="28" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Net_Tonnage_mt" type="xs:double" />
<xs:element minOccurs="0" name="Gross_Tonnage_mt" type="xs:double" />
<xs:element minOccurs="0" name="Registry_Flag">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="50" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Vessel_Registration_Date" type="xs:dateTime" />
<xs:element minOccurs="0" name="Vessel_Registry_Location_id" type="xs:int" />
<xs:element minOccurs="0" name="Place_Built_Country_id" type="xs:int" />
<xs:element minOccurs="0" name="Year_Built" type="xs:int" />
<xs:element minOccurs="0" name="Official_Number">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="12" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Ex_Name">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="28" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Vessel_Type">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="1" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="IMO_Number">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="12" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Hull_Number" type="xs:int" />
<xs:element minOccurs="0" name="Building_Yard_id" type="xs:int" />
<xs:element minOccurs="0" name="Freight_Code">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="5" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Vessel_State">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="1" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Registry_Country_id" type="xs:int" />
<xs:element minOccurs="0" name="Comments">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="500" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Ownership_Type_id" type="xs:int" />
<xs:element minOccurs="0" name="GVA_Hull_Id" type="xs:int" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="GISVoyage">
<xs:complexType>
<xs:sequence>
<xs:element msdata:ReadOnly="true" msdata:AutoIncrement="true" name="Voyage_id" type="xs:int" />
<xs:element name="Vessel_id" type="xs:int" />
<xs:element name="gb_DB_ID" type="xs:int" />
<xs:element name="gb_DBID_PK" type="xs:int" />
<xs:element name="gb_DB_OWNER_ID" type="xs:int" />
<xs:element name="Logical_Cancel_Value" type="xs:int" />
<xs:element name="GB_DB_TimeStamp" type="xs:dateTime" />
<xs:element minOccurs="0" name="Approved_Date" type="xs:dateTime" />
<xs:element name="Last_Update_Date" type="xs:dateTime" />
<xs:element minOccurs="0" name="Last_Update_SecUser_id" type="xs:int" />
<xs:element name="Completed_For_Replication">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="1" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Vessel_Name">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="28" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Vessel_Alias_Name">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="28" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Sch_Proforma_id" type="xs:int" />
<xs:element name="Alternate_Ship_type" type="xs:boolean" />
<xs:element name="Voyage_Number_Retired" type="xs:int" />
<xs:element minOccurs="0" name="Rotation_id" type="xs:int" />
<xs:element minOccurs="0" name="Sch_Proforma_GUID">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="TBA" type="xs:boolean" />
<xs:element minOccurs="0" name="Special_Loader_Grid_id" type="xs:int" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="GISVoyage_Port_Call">
<xs:complexType>
<xs:sequence>
<xs:element msdata:ReadOnly="true" msdata:AutoIncrement="true" name="Voyage_Port_Call_id" type="xs:int" />
<xs:element name="Voyage_id" type="xs:int" />
<xs:element name="Sequence_Number" type="xs:short" />
<xs:element name="Port_id" type="xs:int" />
<xs:element minOccurs="0" name="Actual_Arrival_Time" type="xs:dateTime" />
<xs:element minOccurs="0" name="Actual_Departure_Time" type="xs:dateTime" />
<xs:element minOccurs="0" name="Estimated_Arrival_Time" type="xs:dateTime" />
<xs:element minOccurs="0" name="Estimated_Departure_Time" type="xs:dateTime" />
<xs:element name="Bypassed_Canceled" type="xs:boolean" />
<xs:element name="gb_DB_ID" type="xs:int" />
<xs:element name="gb_DBID_PK" type="xs:int" />
<xs:element name="gb_DB_TimeStamp" type="xs:dateTime" />
<xs:element name="Is_Import_Call" type="xs:boolean" />
<xs:element name="Is_Export_Call" type="xs:boolean" />
<xs:element minOccurs="0" name="Cut_Off_Date" type="xs:dateTime" />
<xs:element name="Logical_Cancel_Value" type="xs:int" />
<xs:element minOccurs="0" name="Voyage_Export_Suffix">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="1" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Voyage_Import_Suffix">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="1" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Commercial_Arrival_Time" type="xs:dateTime" />
<xs:element minOccurs="0" name="Commercial_Departure_Time" type="xs:dateTime" />
<xs:element name="Extra_Call" type="xs:boolean" />
<xs:element minOccurs="0" name="Sch_Proforma_Port_id" type="xs:int" />
<xs:element minOccurs="0" name="Buffer_Remaining_Mins" type="xs:int" />
<xs:element minOccurs="0" name="Bypassed_Reason">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="255" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Delete_Type">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="1" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Display_Sequence_Number" type="xs:short" />
<xs:element name="Is_Transit_Call" type="xs:boolean" />
<xs:element minOccurs="0" name="Export_Documentation_Voyage_Number">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Export_DTX_Voyage_Number">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Import_Documentation_Voyage_Number">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" name="Import_DTX_Voyage_Number">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Voyage_Number_Change" type="xs:boolean" />
</xs:sequence>
</xs:complexType>
<xs:unique msdata:PrimaryKey="true" name="Constraint1">
<xs:selector xpath=".//GISVessel" />
<xs:field xpath="Vessel_id" />
</xs:unique>
<xs:unique msdata:ConstraintName="Constraint1" msdata:PrimaryKey="true" name="GISVoyage_Constraint1">
<xs:selector xpath=".//GISVoyage" />
<xs:field xpath="Voyage_id" />
</xs:unique>
<xs:unique msdata:ConstraintName="Constraint1" msdata:PrimaryKey="true" name="GISVoyage_Port_Call_Constraint1">
<xs:selector xpath=".//GISVoyage_Port_Call" />
<xs:field xpath="Voyage_Port_Call_id" />
</xs:unique>
</xs:element>
<xs:annotation>
<xs:appinfo>
<msdata:Relationship name="X_VOYG_VSSL_Vessel_id" msdata:parent="GISVessel" msdata:child="GISVoyage" msdata:parentkey="Vessel_id" msdata:childkey="Vessel_id" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" />
<msdata:Relationship name="X_PCAL_VOYG_Voyage_id" msdata:parent="GISVoyage" msdata:child="GISVoyage_Port_Call" msdata:parentkey="Voyage_id" msdata:childkey="Voyage_id" />
</xs:appinfo>
</xs:annotation>
</xs:schema>

None of your elements appear to be mapped to any tables. use the "msdata:relation" annotation to map an element to a table.|||

This error message means that you have specified a row to update, but that the row to be updated could not be found in the database. So the "before" image matched 0 rows in the database. This means either the data in the database has changed, or there are some conversion issues between the data in the diffgram and the data in the database that is causing the rows not to match.

You can try to use SQL Query analyzer to capture the SQL and see which column (or columns) are not matching, or you can try to modify the diffgram to see which colum is causing the problem.

I have a feeling that the problem may be the datetime column. One thing to try is to modify the schema to be:

<xs:element name="GB_DB_TimeStamp" type="xs:dateTime" msdata:datatype="dateTime"/>

This will let SQLXML know to do a datetime specific comparison on the SQL Server.

Hope this helps,

Todd