Tag Archives: SQL Server 2008

MSC-ERR-0008 An error has occured at the transport layer and OP-ERR-0181 At least one invalid member – Cognos and SSAS errors


MSC-ERR-0008 An error has occurred at the transport layer and OP-ERR-0181 At least one invalid member – Cognos and SSAS errors

A client has found a solution to two errors occurring in an environment with Cognos 10.1 and SQL Server 2008.

  • MSC-ERR-008 An error as occurred at the transport layer.
  • OP-ERR-0181 At least one invalid member reference was encountered in the query.

A hot-fix was applied to Windows as described in these two following articles and initial testing has been positive. The scenario is a single sign-on Cognos environment with Active Directory using Kerberos to authenticate to SQL Server Analysis Services 2008. The errors seemed intermittent.

http://blogs.msdn.com/b/psssql/archive/2009/04/03/errors-may-occur-after-configuring-analysis-services-to-use-kerberos-authentication-on-advanced-encryption-standard-aware-operating-systems.aspx

http://support.microsoft.com/kb/969083

Advertisements

The correct order to set up a single server PowerPivot installation.


The order in which you install SQL Server 2008 R2, SharePoint 2010, and PowerPivot for SharePoint on a single server currently matters.  There is a glitch that occurs when you install some components of SQL Server 2008 R2 prior to installing PowerPivot.

This is the order that works for me:

  1. Provision a new Windows 2008 SP2 server.
  2. Add IIS role.
  3. Make it a domain controller with dcpromo.
  4. Install SharePoint 2010 prerequisites.
  5. Install SharePoint 2010, do not check configure at the end.
  6. Install PowerPivot instance (choose new server) from SQL Server 2008 R2 installation.

Now you may install any other instances of SQL Server 2008 R2.

 Here is a link that helped me identify the problem:

http://powerpivotgeek.com/2010/06/14/installation-failure-could-not-load-file-or-assembly-microsoft-analysisservices-sharepoint-integration-dll/

IBM Cognos BI 8.4.1 release supports SQL Server Analysis Services 2008


IBM has released Cognos BI 8.4.1 and they claim that it finally supports SSAS 2008. I will be upgrading a client in early January 2010 and put this to the test. Prior to 8.4.1 there was a way to access an SSAS 2008 cube but it would not pass the user credentials with Kerberos as it does with SSAS 2005. I will be testing the new release for integrated security in particular.

Oracle 1/1/1700 date causes error in SSIS load to SQL Server 2008


When loading DATE type data from an Oracle database into a datetime column in a SQL Server database using SSIS an error such as this can occur:

 

[OLE DB Destination [638]] Error: There was an error with input column “EFFECT_DTE” (998) on input “OLE DB Destination Input” (651). The column status returned was: “Conversion failed because the data value overflowed the specified type.”

 

This can be caused when the data value in the Oracle database is 1/1/1700, Oracle’s implementation of a null date. To fix this, create a Derived Column transformation after the OLE DB Source item. In the Derived Column, using EFFECT_DTE as the example field name, enter the following:

Derived Column Name

Derived Column

Expression

EFFECT_DTE

Replace ‘EFFECT_DTE’

EFFECT_DTE == (DT_DATE) “1700-01-01” ? NULL(DT_DATE) : EFFECT_DTE

 

This expression checks if the field is 1/1/1700 and if so converts it to a null date for SQL Server otherwise it passes the date through.

Installing PerformancePoint on SQL Server 2008


If you are installing PerformancePoint Monitoring Server on SQL Server 2008 there are some prerequisite steps.  PerformancePoint Monitoring Server requires SQL Server 2005 client component software.  After SQL Server 2008 is installed and properly updated, then Cumulative update package 9 for SQL Server 2005 Service Pack 2 must be installed.  This CU9 is a hotfix and has to be requested at http://support.microsoft.com/kb/953752

 

After applying the SQL Server 2005 update, PerformancePoint 2007 can be installed but not configured until after PPS SP2 is applied.

 

Microsoft’s Technet has step by step instructions for these installations and updates at:

http://technet.microsoft.com/en-us/library/dd335966.aspx