Post reply

Using Openrowset with MSOLAP

  • November 17, 2010 at 9:12 am

    #226769

    We are trying to use the reports listed here:http://www.ssas-info.com/analysis-services-tools/1365-ssrs-reports-to-document-ssas-2008-db-structure-using-dmvs

    As our cubes/catalogue are dynamic we need to be able to dynamically change the default catalog on the linked server, yet use the same report. We are trying to parameterize the database name. Since creating and dropping the linked server for each report, doesn't make much sense, we are trying to use the OPENROWSET method.

    We are unable to get it to work. Any suggesting would be greatly apppreciated.

    Here is what we have so far:

    DECLARE @dbname varchar(100)

    SET @dbname='CPOE_DEV'

    OpenRowset('MSOLAP','DATASOURCE=***************;Initial Catalog = @dbname;User Id=**************;Password=**********',

    SELECT distinct @dbname FROM $SYSTEM.MDSCHEMA_CUBES') AS derivedtbl_1

    Msg 7303, Level 16, State 1, Line 4

    Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "(null)".

  • November 17, 2010 at 1:54 pm

    #1252148

    I don't think your OPENROWSET() syntax is quite correct.

    In particular, I think you need to capture (i.e., SELECT) the recordset that OPENROWSET()

    returns, and stuff it into a temporary table, or something like that.

    Here is the example that SQL BOL uses for OPENROWSET:

    SELECT a.*

    FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',

    'SELECT GroupName, Name, DepartmentID

    FROM AdventureWorks.HumanResources.Department

    ORDER BY GroupName, Name') AS a;

    You might try to model your query after that example.

  • November 17, 2010 at 3:50 pm

    #1252199

    Thanks john. We actually did notice that, but BOL doesn't talk about MSOLAP too clearly. We had to take a look at connectionstrings.com to get the correct connection synatx. and yes, we are using a username and password for MSOLAP.

    Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply