Executing an SQL Query

SQL queries can be passed to Microsoft® SQL Server™ OLAP Services using either of the following data connectivity tools:

• Microsoft OLE DB and OLE DB for OLAP
• Microsoft ActiveX® Data Objects (ADO) and ActiveX Data Objects (Multidimensional) (ADO MD)

OLE DB for OLAP extends OLE DB to include objects specific to multidimensional data. ADO MD extends ADO in the same fashion.

OLE DB

The OLE DB for OLAP Command object supports text commands in the MDX syntax by using the OLE DB ICommandText interface. Consumers use the ICommandText::SetCommandText method to specify the command and the ICommand::Execute method to process the command. For commands that may be used several times, consumers can prepare the command with the ICommandPrepare::Prepare method. For a complete discussion of OLE DB Command object, see the OLE DB documentation. For more information about the MDX syntax defined by OLE DB for OLAP and its use, see the OLE DB 2.0 and later documentation.

OLE DB for OLAP defines a language dialect identifier GUID (MDGUID_MDX) that denotes the MDX syntax. This GUID is used in ICommandText::SetCommandText to identify to the provider that the query language being used is the MDX syntax.

OLE DB defines another language dialect identifier, DBGUID_DEFAULT, which denotes the default language dialect. The OLE DB for OLAP specification requires that the following semantics apply to the use of this identifier:

• If the provider is both a multidimensional data provider (MDP) and a tabular data provider (TDP), then it must interpret the command string as SQL when DBGUID_DEFAULT is passed as the dialect identifier.
• If the provider is an MDP, it should interpret the command string as MDX when DBGUID_DEFAULT is passed as the dialect identifier.

Important Microsoft SQL Server™ OLAP Services does not simultaneously conform to the requirement for both TDP and MDP providers as specified above. Instead, when DBGUID_DEFAULT is passed, OLAP Services analyzes the query string text and attempts to determine which dialect is being used.

If the SQL dialect is used, a flattened rowset is returned. If the MDX dialect is used, OLAP Services analyzes the rowset interface identifier (IID) to determine if it is a multidimensional dataset or a flattened rowset.

ADO and ADO MD

The ADO MD Cellset object’s Open method retrieves the results of a multidimensional query.

Syntax

Cellset.Open Source, ActiveConnection

Parameters

Source

Optional. A Variant that evaluates to a valid multidimensional query, such as an MDX query. The Source argument corresponds to the Source property.

ActiveConnection

Optional. A Variant that evaluates to a string specifying either a valid ADO Connection object variable name or a definition for a connection. The ActiveConnection argument specifies the connection in which to open the Cellset object. If you pass a connection definition for this argument, ADO opens a new connection using the specified parameters. The ActiveConnection argument corresponds to the ActiveConnection property.

Remarks

The Open method generates an error if either of its parameters is omitted and its corresponding property value has not been set prior to attempting to open the Cellset.

For more information, see the ADO documentation.