SQL Server annotated schema, update gram date format problem

BizTalk Utilities

Xselerator XSLT IDE

Forum main page

Most active member

Search page

Information Online

View our event calendar
Add general event such as an upcoming software release or conference
View all birthdays

Public image gallery
Upload your images in your profile

Profile editing page
Subscription list page
Address book page

Member list page
View the most active member
User groups listing
Private message page

TopXML Discussion Forum

Forums Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

SQL Server annotated schema, update gram date format problem

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> [Ask XML questions here] >> .NET programming discussions >> SQL Server annotated schema, update gram date format problem

Page: [1]

Message

<< Older Topic Newer Topic >>

SQL Server annotated schema, update gram date format pr... - 14 November 2007

nigelj

Posts: 1
Score: 0
Joined: 14 November 2007
Status: offline

I am developing an application in C# which takes an XML file generated using Infopath and uses parts of that XML to update tables on a SQL Server 2005 database. I'm trying to use an annotated schema and updategram to do this but have hit a problem with the dates.

The updategram looks like this

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram" xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<updg:sync
>
   <updg:before>
     <y0:propertyDetails xmlns:y0="htt
p://schemas.microsoft.com/office/infopath/2003/myXSD/2007-03-21T21:50:28" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
       <y0:propertyAgeID>5</y0:propertyAgeID>
       <y0:propertyLastOccupiedAsHMO>1990-12-12</y0:propertyLastOccupiedAsHMO>
       <y0:propertyTypeID>4</y0:propertyTypeID>
       <y0:propertySizeID>5</y0:propertySizeID>
     </y0:propertyDetails>
   </updg:before>
   <updg:after>
     <my:propertyDetails xmlns:y0="http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-03-21T21:50:28" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-03-21T21:50:28">
       <my:propertyAgeID>5</my:propertyAgeID>
       <my:propertyLastOccupiedAsHMO>1990-12-28</my:propertyLastOccupiedAsHMO>
       <my:propertyTypeID>4</my:propertyTypeID>
       <my:propertySizeID>5</my:propertySizeID>
     </my:propertyDetails>
   </updg:after>
</updg:sync>
</ROOT>

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram" xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<updg:sync
>
   <updg:before>
     <y0:propertyDetails xmlns:y0="http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-03-21T21:50:28" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
       <y0:propertyAgeID>5</y0:propertyAgeID>
       <y0:propertyLastOccupiedAsHMO>1990-12-12</y0:propertyLastOccupiedAsHMO>
       <y0:propertyTypeID>4</y0:propertyTypeID>
       <y0:propertySizeID>5</y0:propertySizeID>
     </y0:propertyDetails>
   </updg:before>
   <updg:after>
     <my:propertyDetails xmlns:y0="http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-03-21T21:50:28" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-03-21T21:50:28">
       <my:propertyAgeID>5</my:propertyAgeID>
       <my:propertyLastOccupiedAsHMO>1990-12-28</my:propertyLastOccupiedAsHMO>
       <my:propertyTypeID>4</my:propertyTypeID>
       <my:propertySizeID>5</my:propertySizeID>
     </my:propertyDetails>
   </updg:after>
</updg:sync>
</ROOT>

The relevant section of the schema looks like this

   <xsd:element name="propertyDetails" sql:relation="Property_Data" sql:key-fields="property_id">
       <xsd:complexType>
           <xsd:sequence>
               <xsd:element ref="my:propertyAgeID" minOccurs="0"/>
               <xsd:element ref="my:propertyLastOccupiedAsHMO" minOccurs="0" />
               <xsd:element ref="my:propertyTypeID" minOccurs="0" />
               <xsd:element ref="my:propertySizeID" minOccurs="0"/>
           </xsd:sequence>
           <xsd:attribute ref="my:txt_Grant_Case_ID" />
       </xsd:complexType>
   </xsd:element>

   <xsd:element name="propertyAgeID" nillable="true" type="xsd:integer" sql:field="int_Prop_Age_Type_ID"/>
   <xsd:element name="propertyTypeID" nillable="true" type="xsd:integer" sql:field="int_Prop_Type_ID"/>
   <xsd:element name="propertySizeID" nillable="true" type="xsd:integer" sql:field="int_Prop_Size_Type_ID"/>
   <xsd:element name="propertyLastOccupiedAsHMO" sql:datatype="dateTime" type="xsd:date" sql:field="dte_HMO_Last_Occupied_Date"/>

When I execute the the query I get the following error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
From what I can tell it I'm getting this because it is reading the date as the month and vice versa and shows the same behaviour if I use a normal update command.
From what I can tell the database has been set to British English so should be using the correct format. Using the command
SET LANGUAGE english
does get round the problem but I can't use it in the updategram.

Any suggestions would be greatly appreciated.

Thanks

Nigel