AS2005 Retreiving Data Problem

Hi All,

I'm having problem retreiving data from a AS2005.

I'm using a named instance, called Desktop126\SQL05

This code is being used:

using adomdnet = Microsoft.AnalysisServices.AdomdClient;

...

System.Text.StringBuilder result = new System.Text.StringBuilder();

using (adomdnet.AdomdConnection conn = new AdomdConnection("Data Source=Desktop126\\SQL05;Catalog=BuypassDW;Integrated Security=SSPI; ConnectTo=default")){

conn.Open();

//Create a command, using this connection

adomdnet.AdomdCommand cmd = conn.CreateCommand();

cmd.CommandText = @."

SELECT

{[Transactions Types].[ID].[All].Children} ON columns,

{[Merchants].[l Merchant Id].&[236553]} ON ROWS

FROM [Buypass DW1] WHERE( [Time].[Year].[2005])";

//Execute the query, returning a cellset

adomdnet.CellSet cs = cmd.ExecuteCellSet(); -- Fails with error:

// Either the user, DomainName\UserName, does not have access to the BuypassDW database, or the database does not exist.

conn.Close();

result.Append("\t");

adomdnet.TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples;

foreach (adomdnet.Tuple column in tuplesOnColumns)

{

result.Append(column.Members[0].Caption + "\t");

}

result.AppendLine();

//Output the row captions from the second axis and cell data

//Note that this procedure assumes a two-dimensional cellset

adomdnet.TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples;

for (int row = 0; row < tuplesOnRows.Count; row++)

{

result.Append(tuplesOnRows[row].Members[0].Caption + "\t");

for (int col = 0; col < tuplesOnColumns.Count; col++)

{

result.Append(cs.Cells[col, row].FormattedValue + "\t");

}

result.AppendLine();

}

conn.Close();

} // using connection

}

Any idea why this is failing, the user has permissionto to access the source database,

in AS the datasource has the follwoing value for impersonation info ( AS2005-->dataource-->property)

ImpersonateCurrentUser.

Also in the AS2005 service the DomainName\UserName is being added .

Any hlep will be appreciated.

Thanks.

Abdu

Looks like you havent granted your users access to Analysis Services database.

Create a new role in BypassDW database, add your domain\user to the new role and grant the role DB admin rights. See if user can issue a query.

If everything works, change your role permissions to allow new role read only access to the cubes in the database.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.