I'm using SQL2000 SP1 and SQLXML2.0 (final version).
SQLXML appears to be making a mistake when using fully qualified table
names in updategrams. It's putting square brackets "[]" around
whatever appears in sql:relation. Here's an example using northwind:
schema Categories.xsd:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Categories" type="Categories"
sql:relation="northwind.dbo.Categories"
sql:key-fields="CategoryID"/>
<xsd:complexType name="Categories">
<xsd:attribute name="CategoryID" type="xsd:string"/>
<xsd:attribute name="CategoryName" type="xsd:string"/>
<xsd:attribute name="Description" type="xsd:string"/>
</xsd:complexType>
</xsd:schema>
updategram:
<sqlxml xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync mapping-schema="Categories.xsd">
<updg:after>
<Categories CategoryID="100" CategoryName="test"
Description="test desc"/>
</updg:after>
</updg:sync>
</sqlxml>
Using ADO to apply this, I get an OLE error "Invalid object name
'northwind.dbo.Categories'". However, if I set my initial catelog to
be northwind and change that sql:relation="Categories", it posts the
updategram ok. This is odd, considering that this schema works fine
for selection. It just fails on insertion.
Running the sql trace showed that this was what it was attempting:
BEGIN TRAN
INSERT [northwind.dbo.Categories] ([CategoryID], [CategoryName],
[Description])
^^^^^^^^^^^^^^^^^^^^^^^^^^
VALUES (N'100', N'test', N'test desc');
Note the bit above the carets. It puts brackets around the name for which is just as bad. I have a single user which is going to need to post to tables in Is there some workaround for this? I'm really dying here. I've done --
no particular reason. And instead of making it
[northwind].[dbo].[Categories], it puts it as
[northwind.dbo.Categories], which is invalid. I tried forcing the
brackets myself using:
sql:relation="_x005B_northwind_x005D_._x005B_dbo_x005D_._x005B_Categories_x
but then it tried to do:
INSERT
[_x005B_northwind_x005D_._x005B_dbo_x005D_._x005B_Categories_x005D_]
([CategoryID], [CategoryName], [Description])
several different databases, so having to set initial catalog and
reconnect with each updategram is not a good idea (especially when I
need to post to multiple tables as a transaction).
about 90% of implementation on a project using updategrams and SQLXML
and have really pitched it to the boss as the way to start doing a lot
of our new projects. I've staked a bit of my reputation on this and
will really look bad if there's no way around this bug.
Jason Pierce
Net Edge Solutions