Microsoft KB Archive/925869

From BetaArchive Wiki
Knowledge Base


Error message when you run a Multidimensional Expressions (MDX) query from a linked server in SQL Server 2005 against a SQL Server 2005 Analysis Services database: "Msg 7341, Level 16, State 2, Line 1 Cannot get the current row value of column"

Article ID: 925869

Article Last Modified on 1/12/2007



APPLIES TO

  • Microsoft SQL Server 2005 Analysis Services
  • Microsoft SQL Server 2005 Service Pack 1, when used with:
    • Microsoft SQL Server 2005 Analysis Services



SYMPTOMS

Consider the following scenario. You have a linked server in Microsoft SQL Server 2005. You run a Multidimensional Expressions (MDX) query from the linked server against a SQL Server 2005 Analysis Services database. In this scenario, you receive an error message that resembles the following:

Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[MSOLAP.3].[Dimension].[MEMBER].[MEMBER_CAPTION]" from OLE DB provider "MSOLAP.3" for linked server "LinkedServerName".
Could not convert the data value due to reasons other than sign mismatch or overflow.

This problem occurs if the following conditions are true:

  • The length of the MDX query result is more than 55 characters.
  • You use the Microsoft OLE DB Provider for Analysis Services 9.0 (MSOLAP.3) data provider to connect to the SQL Server 2005 Analysis Services database.


CAUSE

This problem occurs because the MSOLAP.3 data provider is running in the out-of-process mode.

Note By default, SQL Server 2005 and SQL Server 2005 Service Pack 1 (SP1) run the MSOLAP.3 data provider in the out-of-process mode.

WORKAROUND

To work around this problem, set the MSOLAP.3 data provider to run in the in-process mode. Then, use either the OpenRowset function or the OpenQuery function on the MDX query. To set the MSOLAP.3 data provider to run in the in-process mode, follow these steps:

  1. Open SQL Server 2005 Management Studio, and then connect to an instance of SQL Server 2005.
  2. In Object Explorer, expand Server Objects, expand Linked Servers, expand Providers, and then double-click MSOLAP.
  3. In the Provider Options dialog box, click to select the Allow inprocess check box under Provider options.
  4. Click OK to close the Provider Options dialog box.

    Notes
    • After you click to select the Allow inprocess check box, SQL Server 2005 sets the value of the AllowInProcess registry entry to 1. The AllowInProcess registry entry is located under the following registry subkey:

      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Providers\MSOLAP

      In this registry subkey, MSSQL.X represents the instance ID of the instance of SQL Server 2005 Analysis Services that is currently running. To determine the instance ID for the instance of SQL Server 2005 Analysis Services, locate the value of the MSSQLSERVER registry entry or the named instance registry entry under the following registry subkey:

      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\OLAP

    • Both the MSOLAP item in Object Explorer and the MSOLAP registry subkey refer to the MSOLAP.3 data provider.
  5. Use either the OpenRowset function or the OpenQuery function on the MDX query.

Use the OpenRowset function

Put the MDX query in the following SELECT statement. Then, run the query from the linked server against the Analysis Services database:

Select * From OpenRowset('MSOLAP', 'Data Source=<ASServer>;Initial Catalog=<ASDB>;', '<MDXQuery>')

Notes

  • <ASServer> represents the name of the SQL Server 2005 Analysis Services server. <ASDB> represents the SQL Server 2005 Analysis Services database that you query against. <MDXQuery> represents the MDX query that you run.
  • The provider name in the OpenRowset function is specified to be MSOLAP. The provider name in the OpenRowset function must be the same as the name of the registry subkey that contains the OpenRowset registry entry. For example, if the name of the registry subkey that contains the AllowInProcess registry entry is MSOLAPTEMP, specify MSOLAPTEMP as the provider name in the OpenRowset function.
  • You cannot specify MSOLAP.3 for the provider name in the OpenRowset function if MSOLAP.3 is not the registry subkey that contains the AllowInProcess registry entry. If you specify MSOLAP.3 for the provider name in the OpenRowset function anyway, you receive a "7341" error message when you run the SELECT statement. To resolve this problem, manually add the MSOLAP.3 registry subkey under the Providers registry subkey, add an AllowInProcess registry entry in the MSOLAP.3 registry subkey, and then set the value of the AllowInProcess registry entry to 1.

Use the OpenQuery function

  1. Run the following stored procedure to create a new linked server in SQL Server 2005:

    EXEC sp_addlinkedserver 
           @server='<LinkedServerName>',
           @srvproduct='', 
           @provider='MSOLAP',
           @datasrc='<ASServer>', -- This is the name of the Analysis Services server.
           @catalog='<ASDB>' -- This is the Analysis Services database against which you query.

    Note <LinkedServerName> represents the name of the linked server that you specify.

  2. Put the MDX query in the following SELECT statement:

    Select * From OpenQuery(<LinkedServerName>, '<MDXQuery>')
  3. Run the query against the Analysis Services database.

Note Instead of running the stored procedure, you can can use the New Linked Server dialog box in SQL Server 2005 Management Studio to create the linked server. However, the New Linked Server dialog box creates the link server that uses MSOLAP.3 as the provider name. If no MSOLAP.3 registry subkey exists that contains the AllowInProcess registry entry, you receive a "7341" error message when you run the SELECT statement. To resolve this problem, manually add the MSOLAP.3 registry subkey under the Providers registry subkey, add an AllowInProcess registry entry in the MSOLAP.3 registry subkey, and then set the value of the AllowInProcess registry entry to 1.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Keywords: kberrmsg kbtshoot kbprb kbexpertiseadvanced kbsql2005engine kbsql2005as kbsql2005bi KB925869