Error while using Stored Procedure

Hi All,

Finally i was able to convert my UDF to Stored procedure in MSAS 2005!

Now while trying to use the stored procedure in MDX, i am getting a strange error:

Executing the query ...

Execution of the managed stored procedure CogInStr failed with the following error: Microsoft::AnalysisServices::AdomdServer::AdomdException.

The System.SByte* type cannot be converted to an MDX type.

Execution complete

The MDX query was:

SELECT

{ FILTER([Measures].[Sales Amount], ASSP.CogInStr([Measures].[Sales Amount],"*", 0) > 0) }

ON AXIS(0)

FROM [Adventure Works]

The Stored procedure is failing even with complete function commented with only the definition as:

long ASSP::CubeInfo::Looks like you are returning string from your stored procedure. You should convert it to AdomdServer.MDXValue data type. Alternatively, you can implement the entire FILTER as sproc, just like it's done in ASSP - then you will return AdomdServer.Set data type.|||

When I include the namespace "Microsoft::AnalysisServices", i am getting compiler errors:

CubeInfo.cpp(6) : error C2039: 'AnalysisServices' : is not a member of 'Microsoft'
CubeInfo.cpp(6) : error C2871: 'AnalysisServices' : a namespace with this name does not exist

Do i need to upgrade or include some other files in order to get around these errors? I am currently using Microsoft Visual C++ .NET 2003 version.

Any pointers on this would be highly appreciated as it is blocking my work :-(

Thanks in advance,
Santosh.

|||

Hello Santosh,

You need to add a reference to server object model library, which is listed between .NET libraries as Microsoft.AnalysisServices.AdomdServer. If it’s not listed you can browse to find the Microsoft SQL Server\MSSQL.2\OLAP\bin\msmgdsrv.dll.

Hope this helps,

Irina

P.S. Officially we are not supporting stored procedures written with CLR from .Net 2003, I don't think that this particular error is caused by the version mistmatch.

|||

Hi Irina,

How to add this reference in C++? I guess you are referencing to C# in your post? Can you tell me how to add it for a .NET C++ project?

Thanks,

Santosh.

|||

Hi Santosh,

The following KB article explains how to do this: http://support.microsoft.com/kb/310674

But may I ask you a question? Why do you use managed C++ to create stored procedures"? I'm an avid follower of C++ too, but it seems that when creating a .NET application a C# is much better choice.

|||

The reason I am using C++ is my existing solution is in C++. Now, I want to add just another stored procedure project to it, rather than creating a new solution for C#.

I tried following the steps mentioned in that article:

In Visual C++ .NET 2003

By using Visual C++ .NET 2003, you can add a reference by means of the Add Reference dialog box. To add a project reference, follow these steps:

1.

In Solution Explorer, select the project.

2.

On the Project menu, click Add References.

3.

In the Add References dialog box, click the tab that corresponds with the category that you want to add a reference to.

4.

Click Browse, locate the component that you want on your local drive, and then click OK. The component is added to the Selected Components field.

5.

To add the selected reference to the current tab, click Add.

But, I am getting an error saying:

"Add Reference: Error adding reference to the project"

Any help?

Thanks,
Santosh.