This project has moved and is read-only. For the latest updates, please go here.

problem calling sproc through SQL linked server

Aug 10, 2011 at 4:21 PM
sowmya_prabhala Tue at 10:03 PM

Im getting this error; Any suggestions please??

OLE DB provider "MSOLAP" for linked server "SSAS" returned message "Query (1, 6) The '[dbo].[DiscoverXmlMetadataFull]' function does not exist.".
Msg 7321, Level 16, State 2, Line 3
An error occurred while preparing the query "CALL dbo.DiscoverXmlMetadataFull("\Databases\Database", "" ,"<ObjectExpansion>ExpandObject</ObjectExpansion>")" for execution against OLE DB provider "MSOLAP" for linked server "SSAS".

It is coming form the view bi_metadata.dbo.DISCOVER_XML_METADATA where a call is made to the function in the linked server with the help of an OPENQUERY.

OPENQUERY(SSAS, 'CALL dbo.DiscoverXmlMetadataFull("\Databases\Database", "" ,"<ObjectExpansion>ExpandObject</ObjectExpansion>")') a

I executed the query by trying each of the following ways:

Check 1: Checked if the linked server permissions are there.  yes (since it’s on localhost anyway)
Check 2: The linked server should be made available using ‘Be made using this security context’
Check 3: Restarted the SQL server , SSAS

Aug 10, 2011 at 4:23 PM

Let's first make sure the sproc itself it working from an MDX query window. Click the little MDX button and connect to SSAS directly. Then try to run:

CALL dbo.DiscoverXmlMetadataFull("\Databases\Database", "" ,"<ObjectExpansion>ExpandObject</ObjectExpansion>")

I think the problem is that you should be saying ASSP (or whatever name you gave our assembly when you deployed it) instead of dbo. Does this work?

CALL ASSP.DiscoverXmlMetadataFull("\Databases\Database", "" ,"<ObjectExpansion>ExpandObject</ObjectExpansion>")