Search code examples
sqlsql-servermdxlinked-server

SQL OpenRowset - object has no columns

I have a dynamically Stored Procedure, which create a mdx statement for a OpenRowset Query. So it can happen that the objects from the mdx statement are empty. In this case I want back an empty string

SQL Server gives me this error: "The OLE DB provider "" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object."

I tried different things (Catch Statement, sp_describe_first_result_set) but nothing was working...

How can I handle this error ? In my statement below ie. the customer 'Abbas' is not available...

SELECT A.* FROM OpenRowset('MSOLAP', 'DATASOURCE=.; Initial Catalog=CUBE','

SELECT      
    {[Dim Customer].[v Dim Customer Name].[Customer].&[Abbas]} *
    {[Dim Salesperson].[Lastname].&[Fima 1]} *
    {[Dim Creditcard].[v Dim Creditcard Cardtype].[Cardtype].&[Vista]} ON 0


FROM CUBE

WHERE [Measures].[total]

') as A
Solution

  • Please try putting measures on columns and dimensions on rows:

    SELECT A.* FROM OpenRowset('MSOLAP', 'DATASOURCE=.; Initial Catalog=CUBE','

SELECT      {[Measures].[total]} ON 0,
    {[Dim Customer].[v Dim Customer Name].[Customer].&[Abbas]} *
    {[Dim Salesperson].[Lastname].&[Fima 1]} *
    {[Dim Creditcard].[v Dim Creditcard Cardtype].[Cardtype].&[Vista]} ON 1
FROM CUBE

') as A