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>""

Article translations Article translations
Article ID: 959026 - View products that this article applies to.
Expand all | Collapse all

On This Page

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:
968382 How 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: April 7, 2009 - Revision: 2.0
APPLIES TO
  • Microsoft SQL Server 2005 Analysis Services
  • Microsoft SQL Server 2008 Analysis Services
Keywords: 
kbtshoot kbexpertiseadvanced kbfix kbsql2005as kbbug KB959026

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com