Tuesday, March 20, 2012

BUG: SQLXML 3.0 attempts to update Timestamp column

When submitting an update gram generated from a dataset containing a table
having a timestamp column, SQLXML 3.0 will generate an UPDATE statement
attempting to set the timestamp column even after the timestamp column is
marked in the XSD with the attribute sql:datatype="timestamp". This of
course results in an error from SQL Server.
If the /diffgr:diffgram/me:DataSet/me:TABLE_NAME/me:TimeStamp node is
present in the diffgram and the
/diffgr:diffgram/diffgr:before/me:TABLE_NAME/me:TimeStamp node is removed.
Then SQLXML attempts to set the value of TimeStamp to the value from the
/diffgr:diffgram/me:DataSet block.
If TimeStamp node is not present in either block, then SQLXML attempts to
set the value of the TimeStamp column to NULL.
I am attempting to remove all but the primary key columns from the
/diffgr:diffgram/diffgr:before block as suggested as a work around by
Microsoft to other bugs and this is clearly not going to work with tables
having TimeStamp columns.
This bug is a showstopper for me and I am going to have to switch to a
different update mechanism.
AND ANOTHER BUG:
SQLXML 3.0 also applies exact constraints to floating point numbers when
generating update statements. This does not work all the time due to the
vagaries of string to floating point representation conversions.Hello Kevin,
Thank you for your feedback, and this has been routed to the proper
channel. In the meantime, I also encourage you submit via the link below
http://lab.msdn.microsoft.com/produ...ck/default.aspx
To be able to choose the updating logic, you may choose to use updategrams
instead of diffgrams:
Updating Data by Using XML Updategrams
<http://msdn.microsoft.com/library/d...n-us/sqlxml3/ht
m/updategram_0i5v.asp>
This lets you choose the updating logic to be sent to SQL server:
Handling Database Concurrency Issues in Updategrams
<http://msdn.microsoft.com/library/d...n-us/sqlxml3/ht
m/updategram_7tir.asp>
You will see that there is a way to use Primary Key and Timestamp (Specify
the timestamp column (if available) in the <before> block.).
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.|||I am getting the DiffGram from a changed DataSet. UpdateGram is not an
option given by DataSet.GetChanges.WriteXml.
Does MSoft have a DiffGram to UpdateGram stylesheet laying around somewhere?
My DataSet has over 30 tables having over 1000 columns. At first glance
this DiffGram approach seemed like a miracle: 5 lines of code to perform
the updates using a stylesheet generate by 100 lines of code. Wiring up
data adapters and getting all of that correct is at least another order of
magnitude and possibly 3 orders of magnitude more code. (The generated
dataset code is 86,000 lines).
It looks like I am going to have to see how I can minimally tweak the
generated Adapters that I am not using at this moment to leverage the
generated update code. I hate to do this because all future DB changes are
going to result in a very error-prone code management process.
""privatenews"" <petery@.online.microsoft.com> wrote in message
news:gHQJ9w0bGHA.2240@.TK2MSFTNGXA01.phx.gbl...
> Hello Kevin,
> Thank you for your feedback, and this has been routed to the proper
> channel. In the meantime, I also encourage you submit via the link below
> http://lab.msdn.microsoft.com/produ...ck/default.aspx
> To be able to choose the updating logic, you may choose to use updategrams
> instead of diffgrams:
> Updating Data by Using XML Updategrams
> <[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/ht[/ur
l]
> m/updategram_0i5v.asp>
> This lets you choose the updating logic to be sent to SQL server:
> Handling Database Concurrency Issues in Updategrams
> <[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/ht[/ur
l]
> m/updategram_7tir.asp>
> You will see that there is a way to use Primary Key and Timestamp
> ("Specify
> the timestamp column (if available) in the <before> block.").
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> 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.
>|||Hello Kevin,
Thank you for your comments on this feature. To the best of my knowledge,
there is currently no stylesheet to transform DiffGram to UpdateGram. You
may need to develop your own transform if it is necessary. I think it shall
be future product enhancement and this has been reported to the proper
channel.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.

No comments:

Post a Comment