Analysis Services MDX queries that include VBA functions may be slower than expected if a mapped drive is in the search path (841752)

SYMPTOMS

When you use Microsoft Visual Basic for Applications (VBA) functions in your Multidimensional Expression (MDX) queries, and you have a mapped drive listed in your Microsoft Windows environment variable search path, the performance of the MDX query may be slower than expected.

CAUSE

Microsoft SQL Server 2000 Analysis Services loads the Expression Service for VBA when a VBA function is used in an MDX query. The Expression Service is in the Vbajet32.dll file and in the Expsrv.dll file. The Expression Service tries to find the Vba6.dll file to determine what localization to use for strings that are returned by the Expression Service. If the Expression Service does not find the Vba6.dll file in the folder of the executable file that is loading the Msolap.dll OLAP provider or in the Windows System folder, the search path is used to locate the Vba6.dll file. If the Windows PATH environment variable contains a mapped drive to a network resource, the Expression Service searches for the Vba6.dll file on the mapped drive. If the mapped drive takes a long time to search, the MDX query also takes a long time to return results. The Vba6.dll file is not provided with Analysis Services. The Vba6.dll file is a component of Microsoft Visual Basic 6.0.

WORKAROUND

To work around this problem, follow these steps:

Create an empty text file, and the name the file Vba6.dll.
Save the file in the same folder as the executable file that loads the OLAP provider.

The Expression Service finds the empty Vba6.dll file and ends the search. The new Vba6.dll file does not have any adverse effect on the execution of the VBA functions or on any other application that uses VBA because the empty Vba6.dll file is only in the folder for the executable file.