Search code examples
sqlsql-serverwinapioledbjet

Are Hierarchical Rowsets supported by JET and SQL Server?

The OLE DB documentation documents a hierarchial rowset feature, with SQL examples, but doesn't indicate what DB providers support it. Do JET and SQL Server support this feature? And do they use the same sort of syntax as in the documentation, or their own extended syntax?

Solution

  • Yes, it is supported. I've used it quite a bit in the past. This functionality was available using the Data Shaping Service that shipped with MDAC (in a file named Msadds.dll). The OLE DB provider is named MSDataShape and you would use the SHAPE command to produce hierarchical results. The connection string syntax resembled the following:

    Jet:

    Provider=MSDataShape;Data Provider=Microsoft.Jet.OLEDB.4.0;Data Source=PathToMyDatabase.mdb;

    SQL Server:

    Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=ServerNameOrAddress;Initial Catalog=DatabaseName;User ID=;Password=

    A few things worth noting:

    • The SHAPE syntax can get very unwieldy (and awkward) as your queries become more complex, so keep this in mind.

    • If you are using .NET, the same functionality can be achieved with DataSet and DataRelation classes.

    • More importantly, the documentation clearly states that:

    This feature will be removed in a future version of Windows. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, applications should use XML.