Microsoft KB Archive/271770

= PRB: SELECT in Incremental Update Filter Might Return ODBC Error Message =

Article ID: 271770

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft SQL Server OLAP Services

-



This article was previously published under Q271770



SYMPTOMS
If you perform an incremental update on a partition of a cube that uses dimension members for partition data slicing and a SELECT statement is used in the filter for the incremental update, then the following ODBC error message may occur:

ODBC error: Syntax error or access violation;42000; Time:



CAUSE
When OLAP Services performs an incremental update on a cube partition in OLAP Services, the filter is appended to the WHERE clause of the SQL statement that is sent to the data source. If the filter contains a SELECT statement this creates a sub-SELECT in the WHERE clause.

If the partition on which the incremental update is being performed uses dimension members to define data slice information on the partition, the query is prepared as a parameterized query. Microsoft SQL Server does not allow parameterized queries to contain a sub-SELECT in the WHERE clause and returns a syntax error.



WORKAROUND
Replace the SELECT statement in the incremental update filter statement.

For automation of incremental updates, the SELECT can be placed into a separate ActiveX task that runs the query and places the results into a string that then updates the Filter property of the OLAP Data Transformation Services (DTS) Task.

For example, here is a code sample for an ActiveX task that takes the maximum Store_id of the Inventory fact table in the Foodmart database and creates a filter statement for a OLAP DTS Task named &quot;Update Quarter 2&quot;: '********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Function Main

dim oOLAPTask      'OLAP DTS Task dim props      'Properties Collection for OLAP DTS Task dim oServer        'SQL Server dim oResult    'ResultSet dim sMaxKey dim sFilter

'Create a SQLDMO Server Object. Set oServer = CreateObject(&quot;SQLDMO.SQLServer&quot;)

'Open the connection to the server. oServer.Connect &quot;SQLServer&quot;, &quot;sa&quot;

'Select the Store_ID for the filter. set oResult = oServer.Databases(&quot;Foodmart&quot;).ExecuteWithResults(&quot;Select Max(Store_ID) FROM Inventory_fact_1998 &quot;) 'Retrieve the result. sMaxKey = CStr(oResult.GetColumnLong(1,1)) 'Build the New Filter sFilter = &quot;Inventory_fact_1998.store_id = &quot; + sMaxKey + &quot; &quot;

'Find the OLAP Task. set oOLAPTask = DTSGlobalVariables.Parent.Tasks(&quot;Update Quarter 2&quot;) 'Get the Properties Collection for the OLAP Task Set props = oOLAPTask.Properties 'Set the new Filter Expression. props(&quot;Filter&quot;).Value = sFilter

'Disconnect from the SQL Server. oServer.DisConnect

Main = DTSTaskExecResult_Success End Function

NOTE: Make sure that you select the Execute on main package thread in the Workflow Properties Options tab for this task.