You are currently offline, waiting for your internet to reconnect

FIX: Error message when you run a DB2 query that uses the WITH UR query hint in SQL Server 2005 Analysis Services and in SQL Server 2008 Analysis Services: "OLE DB error: OLE DB or ODBC error: An unexpected token "WITH" was found following "<Query>""

SYMPTOMS
Consider the following scenario. In Microsoft SQL Server 2005 Analysis Services or in Microsoft SQL Server 2008 Analysis Services, you use the Microsoft OLEDB Data Provider for DB2 provider to receive data from a DB2 database. You add the WITH UR query hint in a named query or in a partition query to use the UNCOMMITTED READ isolation level. When you run the query, you receive the following error message that comes from a DB2 syntax error:
OLE DB error: OLE DB or ODBC error: An unexpected token "WITH" was found following "<Query>". Expected token may include : ")". SQLSTATE: 42601, SQLCODE: -104; 42601.
CAUSE
This issue occurs because the cartridge wraps the query into a subselect statement. The subselect statement results in the syntax error in DB2.
RESOLUTION

Service pack information for SQL Server 2005

To resolve this problem, obtain the latest service pack for SQL Server 2005. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
913089 How to obtain the latest service pack for SQL Server 2005

Service pack information for SQL Server 2008

To resolve this problem, obtain the latest service pack for SQL Server 2008. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
968382How to obtain the latest service pack for SQL Server 2008
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. This problem was first corrected in SQL Server 2005 Service Pack 3 for SQL Server 2005.
This problem was first corrected in SQL Server 2008 Service Pack 1 for SQL Server 2008.
MORE INFORMATION
We strongly recommend that you do not use the WITH UR query hint in a query because the returned data is in an uncommitted state. Therefore, the data may end up in an inconsistent state in the cube of SQL Server 2005 Analysis Services or in the cube of SQL Server 2008 Analysis Services. This may result in various data integrity problems in the cube.

After applies this hotfix, if you want to use the UNCOMMITTED READ isolation level, follow these steps:
  1. Stop the Analysis Services service.
  2. For SQL Server 2005, locate the following folder:
    C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin\Cartridges
    For SQL Server 2008, locate the following folder:
    C:\Program Files\Microsoft SQL Server\MSAS10.SQL2008\OLAP\bin\Cartridges
  3. Edit the Db2v0801.xsl file by using Notepad.
  4. Locate the following XML tag:
    <xsl:param name="post-select-query-hint"></xsl:param>
    Change this tag to the following:
    <xsl:param name="post-select-query-hint">WITH UR</xsl:param>
  5. Start the Analysis Services service.
If you want to revert to the former behavior, follow these steps:
  1. Stop the Analysis Services service.
  2. Change the XML tag in the Cartridges folder.
  3. Edit the Db2v0801.xsl file back to the previous text.
  4. Start the Analysis Services service.
For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:
824684 Description of the standard terminology that is used to describe Microsoft software updates
Properties

Article ID: 959026 - Last Review: 04/07/2009 23:57:02 - Revision: 2.0

Microsoft SQL Server 2005 Analysis Services, Microsoft SQL Server 2008 Analysis Services

  • kbtshoot kbexpertiseadvanced kbfix kbsql2005as kbbug KB959026
Feedback