Getting exception on executing ExecuteCellSet on AdomdCommand object

I'm trying to execute an mdx query in C# and getting exception on calling ExecuteCellSet() on AdomdCommand object,

This is the code snippet

// initialize a new adomdcommand object for the specified connection

objCommand = connection.CreateCommand();

// supply the command text for the adomdcommand object

objCommand.CommandText = queryString;

// attempt to run the command on the cell cellset

try

{

objCellSet = objCommand.ExecuteCellSet();

// return the retrieved cellset

return objCellSet;

}

catch (Exception ex)

{

Console.Write("Exception: " + ex);

objCellSet = null;

return objCellSet;

}

Exception: System.Data.DuplicateNameException: A DataTable named 'UOM' already b
elongs to this DataSet.
at System.Data.DataTableCollection.RegisterName(String name)
at System.Data.DataTableCollection.BaseAdd(DataTable table)
at System.Data.DataTableCollection.Add(DataTable table)
at Microsoft.AnalysisServices.AdomdClient.MDDatasetFormatter.ReadAxesInfo(Xml
Reader reader)
at Microsoft.AnalysisServices.AdomdClient.MDDatasetFormatter.ReadMDDataset(Xm
lReader reader)
at Microsoft.AnalysisServices.AdomdClient.SoapFormatter.ReadDataSetResponsePr
ivate(XmlReader reader)
at Microsoft.AnalysisServices.AdomdClient.SoapFormatter.ReadExecuteResponsePr
ivate(XmlReader reader, Boolean throwOnInlineErrors)
at Microsoft.AnalysisServices.AdomdClient.SoapFormatter.ReadResponse(XmlReade
r reader, Boolean throwOnInlineErrors)
at Microsoft.AnalysisServices.AdomdClient.XmlaClientProvider.Microsoft.Analys
isServices.AdomdClient.IExecuteProvider.ExecuteMultidimensional(ICommandContentP
rovider contentProvider, AdomdPropertyCollection commandProperties, IDataParamet
erCollection parameters)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteCellSet()
at ceat.excel_writer.ceat_writer.ceat_writer_runQuery(AdomdConnection& connec
tion, String queryString)

When I run the query in MDX sample application editor, it is returning values.

Thanks for your help

DRK12345,

I am having a similar problem. I am wondering whether you solved your problem. Please let me know..

Thanks,

Golf

|||

hello,

which version of AS server are you connecting to? If this code works against AS2000, then i think the issue is that an older version of the msolap80 is used. Installing the latest version of msolap80 available should help i think. [can be downloaded for example from http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en (under Microsoft SQL Server 2000 PivotTable Services) or hotfix http://support.microsoft.com/?kbid=819609 could be used as well i think).

hope this helps,

|||

Mary,

Installing latest version of msolap80 fixed the problem. Thanks a lot for the help. I appreciate it.

Getting exception on executing ExecuteCellSet on AdomdCommand object

I'm trying to execute an mdx query in C# and getting exception on calling ExecuteCellSet() on AdomdCommand object,

This is the code snippet

// initialize a new adomdcommand object for the specified connection

objCommand = connection.CreateCommand();

// supply the command text for the adomdcommand object

objCommand.CommandText = queryString;

// attempt to run the command on the cell cellset

try

{

objCellSet = objCommand.ExecuteCellSet();

// return the retrieved cellset

return objCellSet;

}

catch (Exception ex)

{

Console.Write("Exception: " + ex);

objCellSet = null;

return objCellSet;

}

Exception: System.Data.DuplicateNameException: A DataTable named 'UOM' already b
elongs to this DataSet.
at System.Data.DataTableCollection.RegisterName(String name)
at System.Data.DataTableCollection.BaseAdd(DataTable table)
at System.Data.DataTableCollection.Add(DataTable table)
at Microsoft.AnalysisServices.AdomdClient.MDDatasetFormatter.ReadAxesInfo(Xml
Reader reader)
at Microsoft.AnalysisServices.AdomdClient.MDDatasetFormatter.ReadMDDataset(Xm
lReader reader)
at Microsoft.AnalysisServices.AdomdClient.SoapFormatter.ReadDataSetResponsePr
ivate(XmlReader reader)
at Microsoft.AnalysisServices.AdomdClient.SoapFormatter.ReadExecuteResponsePr
ivate(XmlReader reader, Boolean throwOnInlineErrors)
at Microsoft.AnalysisServices.AdomdClient.SoapFormatter.ReadResponse(XmlReade
r reader, Boolean throwOnInlineErrors)
at Microsoft.AnalysisServices.AdomdClient.XmlaClientProvider.Microsoft.Analys
isServices.AdomdClient.IExecuteProvider.ExecuteMultidimensional(ICommandContentP
rovider contentProvider, AdomdPropertyCollection commandProperties, IDataParamet
erCollection parameters)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteCellSet()
at ceat.excel_writer.ceat_writer.ceat_writer_runQuery(AdomdConnection& connec
tion, String queryString)

When I run the query in MDX sample application editor, it is returning values.

Thanks for your help

DRK12345,

I am having a similar problem. I am wondering whether you solved your problem. Please let me know..

Thanks,

Golf

|||

hello,

which version of AS server are you connecting to? If this code works against AS2000, then i think the issue is that an older version of the msolap80 is used. Installing the latest version of msolap80 available should help i think. [can be downloaded for example from http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en (under Microsoft SQL Server 2000 PivotTable Services) or hotfix http://support.microsoft.com/?kbid=819609 could be used as well i think).

hope this helps,

|||

Mary,

Installing latest version of msolap80 fixed the problem. Thanks a lot for the help. I appreciate it.

Getting Error while using exec statement

Hi

I am trying to use exec statement like this

Code Snippet

declare @.a varchar(10)

declare @.b varchar(10)

declare @.c varchar(20)

set @.a = 'SomeData'

select @.c = '@.b=' +'@.a'

exec ('select ' + @.c)

but i am getting this error

Must declare the variable '@.a'.

well we can achieve above problem using

Code Snippet

set @.a = 'SomeData'

select @.b=@.a

select @.b

but i am expecting like this (because i am building a formula)

exec ('select ' + @.c)

please advice

Thanks

You have a scope problem; the '@.a' variable is not known in the domain of your exec string. The scope of the code that is run as part of an "exec string" is completely separate from the scope of the code that compiled the exec string. Therefore, none of your variables -- @.a, @.b, or @.c -- have any meaning from within the execution scope of what gets done while your "exec string" is being performed. You might want to use the "sp_executesql" stored procedure in this case so that you might be able to use these variables as parameters.

Here are some previous posts on the subject:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1087502&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=660466&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=578918&SiteID=1

You also need to understand that there are some inherent problems with dynamic SQL. This article might provide some insights:

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

|||

I've used like this

Code Snippet

set @.a = 'SomeData'

select @.c = '@.b=' +'@.a'

EXEC sp_executesql N'select @.c'

but this time i got this Error

Must declare the variable '@.c'.

please advice

Thanks

|||

You must also pass the parameter to sp_executesql (any the parameters included in the string.

Try something like this:

Code Snippet

set @.a = 'SomeData'

select @.c = '@.b=' +'@.a'

EXECUTE sp_executesql N'SELECT @.c', @.c

Of course, SELECT @.B='SomeData' isn't going to make a lot of sense and will cause an error.

But hey, you just asked for help with passing the parameter -not writing valide SQL statements...

|||

The following sample might help you to understand..

Code Snippet

Declare @.a as int

Declare @.b as int

Declare @.result as varchar(100)

Declare @.FormulaQuery as nvarchar(100)

Set @.a = 10

Set @.b = 20

select @.FormulaQuery = 'Set @.result = (@.b + @.a)'

EXECUTE sp_executesql

@.FormulaQuery --Your Computable Expression/Formula

, N'@.a int, @.b int, @.result int output' -- All the Param definitions

, @.a -- First Param value

, @.b -- Second Param value

, @.result output -- Third Param value which return the value from the expression

Select @.result