Search code examples
sql-serverstored-proceduresmdxopenquery

OpenQuery inside a stored procedure

I have an OpenQuery that works only outside the stored procedure.

This works:

SELECT * 
FROM OpenQuery(LINKED_OLAP,
               'SELECT [Measures].[Sales] on columns FROM [Volumes] WHERE ([Calendar].[Day].&[' + convert(nvarchar(4),@Date,120) + ']&[' + DATENAME(MONTH, GETDATE()) + ']&[' + RIGHT((convert(nvarchar(10),@Date,120)),2) + ']')

When I put this inside a stored procedure, and then execute the stored procedure, I get the error:

Cannot process the object "SELECT * FROM OpenQuery(LINKED_OLAP,'SELECT [Measures].[Sales] on columns FROM [Volumes]')". The OLE DB provider "MSOLAP.5" for linked server "LINKED_OLAP" indicates that either the object has no columns or the current user does not have permissions on that object.

I have permissions and there are columns in there. The query runs outside stored procedure, why doesn't it work when I call the stored procedure? I tried removing the datename function and the stored procedure worked. How can I fix this? I need this function to run.

Solution

  • Ok I found out how to solve it.

    Instead of using the DATENAME function, I passed it to a variable:

    declare @TheMonth NVARCHAR(10) = DATENAME(MONTH, GETDATE())