Overcoming the openquery 8000 char limit & problem with using Call mystoredproc() in open query

sydneyausguy


Does anyone know why this statement would fail I have created my own assembly which is on the server and when i run the mdx query call myassembly.mystoredproc() it returns data, but now when I use call myassembly.mystoredproc() it returns an error.

using an mdx query this works fine
call AsmTest.Asm.Analysis.TestClass.NameMe()

When using openquery i get an error
select * from openquery(NV, 'call AsmTest.Asm.Analysis.TestClass.NameMe()' )

OLE DB provider "MSOLAP.3" for linked server "NV" returned message "Prepare is not safe during execution of the NameMe stored procedure.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "call AsmTest.Asm.Analysis.TestClass.NameMe()" for execution against OLE DB provider "MSOLAP.3" for linked server "NV".




linked server definition

USE master
GO

/* Add new linked server */
EXEC sp_addlinkedserver
@server='NV', -- local SQL name given to the linked server
@srvproduct='', -- not used
@provider='MSOLAP.3', -- OLE DB provider (the .2 means the SQL2K version)
@datasrc='nvsifwfp', -- analysis server name (machine name)
@catalog='ARTSDW' -- default catalog/database

Bogdan Crivat

SQL Server attempts to prepare the remote server call before actually executing it. Prepare on AnalysisServices results in a Prepare call to your stored procedure (a regular call with Context.ExecuteForPrepare == true). Analysis Services will fail to prepare your stored procedure unless it is marked as Safe for prepare

You should add a reference to the Microsot.AnalysisServices.AdomdServer.dll assembly in your stored procedure, then decorate the actual method with the [SafeToPrepare(true)] attribute.

This informs Analysis Services that it is "safe" to execute a Prepare invocation over the method (i.e. will not have any side effects)

If the implementation of the stored procedure is trivial and not time consuming, then that is all you need to do.

If the implementation is potentially time consuming (say, constructs a large table withc omplex calculations and returns that table as a rowset) then your stored procedure's code should start with something like:

[SafeToPrepare(true)]

DataTable MyMethod()

{

DataTable myTable = new DataTable();

// Build table metadata here

.AddColumn(...)

....

if( Context.ExecuteForPrepare )

{

// return an empty table withthe CORRECT schema

return myTable;

}

// perform calculations and add rows

...

// return the actual populated table

return myTable

}

Both the SafeToPrepareAttribute and Context are defined in Microsoft.AnalysisServices.AdomdServer.dll