Executing an SQL Query

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

• Microsoft OLE DB, including OLE DB for Online Analytical Processing (OLE DB for OLAP)
  
  
• Microsoft ActiveX® Data Objects (ADO) and ADO (Multidimensional) (ADO MD).

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

All three data connectivity tools provide objects that support SQL queries; OLE DB provides the Command object, ADO provides the Recordset object, and ADO MD provides the Cellset object. Each data connectivity tool, however, provides support in a different manner.

OLE DB

The OLE DB for OLAP Command object supports text commands in the Multidimensional Expressions (MDX) syntax by using the OLE DB ICommandText interface. The ICommandText::SetCommandText method specifies the command and the ICommand::Execute method processes the command. For commands that may be used several times, the ICommandPrepare::Prepare method prepares the command.

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 also defines another language dialect identifier, DBGUID_DEFAULT, which denotes the default language dialect. The following rules apply to the use of this identifier (from the OLE DB for OLAP specification):

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

Important  Analysis Services does not simultaneously conform to both of these semantics. Instead, when DBGUID_DEFAULT is passed, Analysis 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, Analysis Services analyzes the rowset interface identifier (IID) to determine whether a multidimensional dataset or a flattened rowset is returned.

ADO

The Open method of the ADO Recordset object retrieves the results of an SQL query.

Syntax

object.Open [Source], [ActiveConnection], CursorType As CursorTypeEnum = adOpenUnspecified], [LockType As LockTypeEnum = adLockUnspecified], [Options As Long = -1]

Parameters

object

An instance of the ADO Recordset object.

Source

(Optional) A Variant that evaluates to a valid ADO Command object, valid SQL query, table name, stored procedure call, URL, or the name of a file containing a persistently stored ADO Recordset object. This argument corresponds to the Source property.

ActiveConnection

(Optional) A Variant that evaluates to a valid ADO Connection object variable name or a string that contains a connection definition. 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.

CursorType

(Optional) A CursorTypeEnum value that determines the type of cursor that the provider should use when opening the ADO Recordset object.

LockType

(Optional) A LockTypeEnum value that determines what type of locking (concurrency) the provider should use when opening the ADO Recordset object.

Options

(Optional) A value that indicates how the provider should evaluate the Source argument if it represents something other than an ADO Command object, or if the ADO Recordset object should be restored from a file where it was previously saved. This value may be set to a value supplied from either the ADO CommandTypeEnum or ExecuteOptionEnum enumerations.

Remarks

The Open method fails if either the Source or Active Connection parameters are missing or their corresponding properties are not set.

The default value for the CursorType property is adOpenUnspecified.

The default value for the LockType is adLockUnspecified.

ADO MD

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

Syntax

object.Open [DataSource], [ActiveConnection]

Parameters

object

An instance of the ADO MD Cellset object.

DataSource

(Optional) A Variant that evaluates to a valid multidimensional query, such as an MDX query. The DataSource 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 an attempt to open the Cellset.