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

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:

This issue occurs because the cartridge wraps the query into a subselect statement. The subselect statement results in the syntax error in DB2.

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:

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:

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.

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: