Professional SQL Server™ 2005 Programming

A.7. Rowset Functions

The rowset functions return an object that can be used in place of a table reference in a T-SQL statement. The rowset functions are:

• CONTAINSTABLE

• FREETEXTTABLE

• OPENDATASOURCE

• OPENQUERY

• OPENROWSET

• OPENXML

A.7.1. CONTAINSTABLE

The CONTAINSTABLE function is used in full-text queries. Please refer to Chapter 21 for an example of its usage. The syntax is as follows:

CONTAINSTABLE (<table>, {<column> | *}, '<contains_search_condition>')

A.7.2. FREETEXTTABLE

The FREETEXTTABLE function is used in full-text queries. Please refer to Chapter 21 for an example of its usage. The syntax is as follows:

FREETEXTTABLE (<table>, {<column> | *}, '<freetext_string>')

A.7.3. OPENDATASOURCE

The OPENDATASOURCE function provides ad hoc connection information. The syntax is as follows:

OPENDATASOURCE (<provider _ name>, <init _ string>)

The provider_name is the name registered as the ProgID of the OLE DB provider used to access the data source. The init_string should be familiar to VB programmers, as this is the initialization string to the OLE DB provider. For example, the init_string could look like:

"User Id=wonderison;Password=JuniorBlues;DataSource=MyServerName"

A.7.4. OPENQUERY

The OPENQUERY function executes the specified pass-through query on the specified linked_server. The syntax is as follows:

OPENQUERY(<linked_server>, '<query>')

A.7.5. OPENROWSET

The OPENROWSET function accesses remote data from an OLE DB data source. The syntax is as follows:

OPENROWSET('<provider_name> ...