problem with nested DataTable in server-side ADOMD.NET SP

hi, have server-side stored procedure created in c#.  sp issues 1 dmx statement (within implicit connection) has prediction join shape/append. tries map results of adomddatareaders system.data.datatable objects, returning 1 system.data.datatable object. because of shape/append, end 2 nested adomddatareaders.

 

code block

using microsoft.analysisservices.adomdserver;

using system.data;

...

public datatable vivaomengosp()
        {           
                adomdcommand c = new adomdcommand(createpredicteddmx()); 

// select ... prediction join ... shape ... append

                datatable dt = new datatable();
                dt.columns.add("att1", typeof(string));
                dt.columns.add("att2", typeof(int));
                dt.columns.add("predictednestedmonster", typeof(datatable));

                object[] row = new object[3];
                try
                {
                    adomddatareader dr = c.executereader();
                    while (dr.read())
                    {
                        datatable innerdt = new datatable();
                        innerdt.columns.add("inneratt1", typeof(int));
                        innerdt.columns.add("inneratt2", typeof(string));

                        row[0] = dr[0];
                        row[1] = dr[1];
                        adomddatareader innerdr = (adomddatareader)dr[2];
                        object[] innerrow = new object[2];
                        while (innerdr.read())
                        {
                            innerrow[0] = innerdr[0];
                            innerrow[1] = innerdr[1];
                            innerdt.rows.add(innerrow);
                        }
                        row[2] = innerdt;
                        dt.rows.add(row);
                    }
                    dr.close();
                    return dt;
                }
                catch
                {
                    return null;
                }                        
        }

(the code modification listing 20-3, page 807, of book "programming microsoft sql server 2005" brust & forte)

i call directly in ssms , result:

executing query ...
execution of managed stored procedure vivaomengosp failed following error: microsoft::analysisservices::adomdserver::adomdexception.
errors in high-level relational engine. system.data.datatable type not supported analysis services code name "katmai".

execution complete

is possible have nested table sp result? is datatable appropriate class?

thanks,
gustavo frederico

it not possible return nested data table stored procedure.

however, here workaround:

- create data set

- include both tables in data set (case table , nested table)

- define relationship in data set between 2 tables

- change procedure return dataset instead of datatable

 

when executing query ssms (or ole db) single column, containing xml serialization of dataset.

when executing adomd.net client, getvalue(0) return dataset object (adomd.net detects structure , deserializes dataset).

if want access string in adomd.net have use getstring(0) (and deserialize yourself)

 

SQL Server  >  Data Mining