Greetings,

We're currently starting to utilize updategrams with SQLXML 4.0 and SQL
2005.  Part of the research, at this point, is utilizing optimistic
concurrency in the updategram using record timestamps.  This seems pretty
straight forward, however we're having a problem that's causing me much
frustration.

I have a table called Artist, which as an ArtistID (int), ArtistName
(nvarchar), and UpdateStamp (timestamp).  if I query that information in
SSMS, I'll receive something similar to the following:



[ArtistID]   [ArtistName]   [UpdateStamp]
100           Joey Balogna   0x0000000000001064

That's all fine and dandy and expected.  However, since we're using SQLXML
and using xpath queries for all our querying, the emitted results are in XML
(using FOR XML EXPLICIT).  In that case, the UpdateStamp datatype changes to,
I *think*, the W3C HexBinary format?
eg:
<Artist ArtistID="100" Name="Joey Balogna" UpdateStamp="AAAAAAAAEGQ=" />

So on our client end I receive the UpdateStamp as being "AAAAAAAAEGQ=",
which is fine, however if I try and post an updategram, and use the
UpdateStamp as the field upon which concurrency is based, I receive the
following exception:

FrostingModel.Tests.Artist.InvalidConcurrencyUpdate :
Microsoft.Data.SqlXml.SqlXmlException : HRESULT="0x80004005"
Description="Invalid XML elements found inside sync block"

The kicker, however, is if I change the UpdateStamp in the updategram from
"AAAAAAAAEGQ=" back to "0x0000000000001064", the resulting update performs as
expected withouth the invalid xml element exception.

My question is this:  Why does SQL 2005 emit the timestamp in the AAA
format, but does not accept that same format in an updategram?  Is this a bug
in SQLXML 4.0 or SQL 2005?  Or is there some type of additional conversion I
need to do before I add the timestamp to the updategram?

Thanks in advance.

Ryan,

See if this helps (using sql server 2000).

create table dbo.t1 (
c1 int not null identity primary key,
c2 rowversion not null
)
go

insert into t1 default values
insert into t1 default values
insert into t1 default values
go

select c1, master.dbo.fn_varbintohexstr(cast(c2 as varbinary(8))) as c2
from dbo.t1
for xml auto
go

drop table dbo.t1
go


AMB


Show quoteHide quote

Alejandro,

That does help if I were issuing the query through SSMS or Query Analyser
(or manually constructing a statement through a SqlCommand object or
something), however we're passing an XPath query to a
Microsoft.Data.SqlXml.SqlXmlCommand object (which in turn generates the
appropriate sql syntax executed on the server).  I can't, therefore, use the
fn_varbintohexstr (however that is indeed handy, and I thank you for showing
it to me).

Any other ideas?
Show quoteHide quote

Hi,
Thanks for your post!

From your description, I understand that:
You had a table including a column with timestamp type. When you used
SQLXML and XPath for query, the column value of the result seemed strange.
It's different from which you saw in SQL Server Management Studio.
If I have misunderstood, please feel free to let me know.

The format of the value you saw is bin.base64. That's caused by timestamp
type mapping between the database and XML format.
I recommend you explicitly specify sql:datatype="timestamp" on the
appropriate node in the mapping schema.
You may refer to:
Client-side vs. Server-side XML Formatting (SQLXML 4.0) 
http://msdn2.microsoft.com/es-es/library/ms172641.aspx

Specifying an Annotated Mapping Schema in an Updategram (SQLXML 4.0) 
http://msdn2.microsoft.com/de-de/library/ms171772.aspx

If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.

+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.

This and other support options are available here:

BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/

If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

Charles,

Thanks for getting back to me.  I was out of town for the past 10 days so I
apologise for the lateness of my reply.

I will try your suggestion and let you know how it goes.  If it does not
succeed, I will post back with an example of my XSD schema.

Thanks.

Show quoteHide quote

Hi Charles,

Unfortunately, I'm still experiencing the same error.

Here is the error in its entirety:
FrostingModel.Tests.Artist.InvalidConcurrencyUpdate :
Microsoft.Data.SqlXml.SqlXmlException : HRESULT="0x80004005"
Description="Invalid XML elements found inside sync block"

  ----> System.Runtime.InteropServices.COMException : HRESULT="0x80004005"
Description="Invalid XML elements found inside sync block"

Here is the updategram that's being sent:
<?xml version="1.0" encoding="utf-16"?>
<u>
  <updg:sync mapping-schema=".\Schemata\Artist.xsd" nullvalue="NULL"
xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
    <updg:before>
      <ns1:ArtistConcurrency updg:id="0" ArtistID="2045"
UpdateStamp="AAAAAAAAE+Q=" xmlns:ns1="urn:Frosting" />
    </updg:before>
    <updg:after>
      <ns1:ArtistConcurrency updg:id="0" ArtistID="2045" Name="Blah Blah
Update" xmlns:ns1="urn:Frosting" />
    </updg:after>
  </updg:sync>
</u>

Here is my mapping schema:
<xsd:schema id="Artist"  targetNamespace="urn:Frosting"
elementFormDefault="qualified"
    xmlns:fm="urn:Frosting"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
    xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
    xmlns:sql2="urn:schemas-microsoft-com:mapping-schema-enhanced"
    xmlns:dt="urn:schemas-microsoft-com:datatypes"
    xmlns:code="urn:schemas-point2-com:code-mapping-schema"
    code:namespace="Frosting.Model" >
<xsd:element name="ArtistConcurrency" type="fm:ArtistConcurrency"
sql:relation="[dbo].[Artist]" sql:key-fields="ArtistID"></xsd:element>
<xsd:complexType name="ArtistConcurrency">
    <xsd:attribute name="ArtistID" type="xsd:int" use="required"
sql:identity="ignore" sql:datatype="int" />
    <xsd:attribute name="Name" type="xsd:string" use="optional"
sql:datatype="nvarchar" />
    <xsd:attribute name="UpdateStamp" use="optional" sql:datatype="timestamp"
sql2:concurrency="UseValue" />
</xsd:complexType>
</xsd:schema>

Any help you can give would be hugely appreicated.  I'm really unsure as to
why I'm receiving that "Invalid XML elements found inside sync block"
exception.  If I remove the timestamp element from my schema (and hence the
updategram), it works:

<?xml version="1.0" encoding="utf-16"?>
<u>
  <updg:sync mapping-schema=".\Schemata\Artist.xsd" nullvalue="NULL"
xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
    <updg:before>
      <ns1:ArtistConcurrency updg:id="0" ArtistID="2051"
xmlns:ns1="urn:Frosting" />
    </updg:before>
    <updg:after>
      <ns1:ArtistConcurrency updg:id="0" ArtistID="2051" Name="Blah Blah
Update" xmlns:ns1="urn:Frosting" />
    </updg:after>
  </updg:sync>
</u>

Show quoteHide quote

Hi,
Thanks for your response.

Try this:
         <xsd:attribute name="UpdateStamp" sql:field="UpdateStamp"
type="xsd:hexBinary" sql:datatype="timestamp" />

+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.

This and other support options are available here:

BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/

If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

Thanks for the reply Charles.  Unfortunately, I get the same error from
Microsoft.Data.SqlXml:

"FrostingModel.Tests.Artist.InvalidConcurrencyUpdate :
Microsoft.Data.SqlXml.SqlXmlException : HRESULT="0x80004005"
Description="Invalid XML elements found inside sync block"

  ----> System.Runtime.InteropServices.COMException : HRESULT="0x80004005"
Description="Invalid XML elements found inside sync block"

However, if I open up the updategram, I do notice a difference.  The
UpdateStamp is now in both the before and the after block - however there's a
validation error (as indicated by the blue squiggly):

The 'UpdateStamp' attribute is invalid - The value 'AAAAAAAAFBQ=' is invalid
according to its datatype 'http://www.w3.org/2001/XMLSchema:hexBinary' -
'AAAAAAAAFBQ=' is not a valid BinHex text sequence.

- ryan.

Show quoteHide quote

Hi, Ryan,
Thanks for your response.

In this case, I appreciate you send me the updategram file, schema mapping
file, the database scripts of the table (it's better with some original
data) for further research.
My email is chang***@microsoft.com.

If you have any other concerns, please don't hesitate to let me know. I'm
happy for assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.

This and other support options are available here:

BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/

If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

That would be great, Charles.

I'll get that to you as soon as possible.

Thanks.

Show quoteHide quote

Hi, Ryan,
Thanks for your response.
I'm ready for any further assistance.

Enjoy your day!

+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.

This and other support options are available here:

BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/

If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

Hi, Ryan,
This is a quick not that I haven't received your email about the script for
further research.
I would like to know whether you need further research. 
If you have any other questions or concerns, please don't hesitate to let
me know.
It's always my pleasure to be of assistance.

+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Identity keys in a One-to-Zero-or-Many relationship
Insert in every ID
SQL Newbie
Iteration in a stored procedure
How to return error from CLR Stored Procedure