bug in qualified database.owner.table in sql:relation

bug in qualified database.owner.table in sql:relation

Post by Jason Pier » Wed, 28 Nov 2001 07:59:32



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
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_x005D_",
but then it tried to do:
INSERT
[_x005B_northwind_x005D_._x005B_dbo_x005D_._x005B_Categories_x005D_]
([CategoryID], [CategoryName], [Description])

which is just as bad.

I have a single user which is going to need to post to tables in
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).

Is there some workaround for this?  I'm really dying here.  I've done
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

 
 
 

bug in qualified database.owner.table in sql:relation

Post by Jason Pier » Wed, 28 Nov 2001 09:04:39


Well, I found a workaround.  A weird one.

sql:relation="[northwind].[dbo].[Categories]"

will work (so far).

Note that these work:
sql:relation="[northwind].dbo.Categories"
sql:relation="[northwind].[dbo].Categories"
sql:relation="[northwind].dbo.[Categories]"
sql:relation="[northwind].[dbo].[Categories]"

But these don't:
sql:relation="northwind.dbo.Categories"
sql:relation="northwind.dbo.[Categories]" ("incorrect syntax" error)
sql:relation="northwind.[dbo].[Categories]" ("incorrect syntax" error)

Basically, it seems to check for a [ in the first column.  If it's not
found there, it encloses the whole thing in [].  This looks like a
bug.



>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
>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_x005D_",
>but then it tried to do:
>INSERT
>[_x005B_northwind_x005D_._x005B_dbo_x005D_._x005B_Categories_x005D_]
>([CategoryID], [CategoryName], [Description])

>which is just as bad.

>I have a single user which is going to need to post to tables in
>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).

>Is there some workaround for this?  I'm really dying here.  I've done
>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

--
Jason Pierce
Net Edge Solutions

 
 
 

1. Refer to table without qualifying with owner name in SQL 7

In my company, we have many users with different permissions.
Obviously, we do not want to sp_addalias all users to "dbo".

When developer A creates table tblA, other users must access the table
with syntax like "select * from developerA.tblA".  We can end up with
many queries like "select * from developerB.tblB", "select * from
developerC.tblC", etc.

This sort of syntax is nightmare when there comes a need to change
codes querying tables.

We would like all different users (with appropriate permissions)to be
able to do "select * from tblA", regardless of who created the table.

Sent via Deja.com http://www.deja.com/
Before you buy.

2. illegal procedure entry

3. Accessing another user's table without qualifying table name with the owner-name

4. Query designer error when opening a SQL table

5. SP2 blues: owner object not seen by owner unless fully qualified

6. Autosizing field Crystal Reports

7. Setting Replication with Owner Qualified tables

8. Inf7 on Linux w/iBCS2 - which nettype ?

9. Replication of owner qualified tables

10. scalar user-defined function require the function to be owner qualified

11. A database owner is not reconized as a database owner

12. Change DB owner/Table owner

13. Relations between tables n:m-relation