External Data and Transact-SQL

As long as the provider supports the required OLE DB interfaces, each class of Transact-SQL statements mentioned later is allowed. Here is the subset of the Transact-SQL language allowed on remote tables accessed through linked server-based names or ad hoc names:

• All queries with the standard form of SELECT select_list FROM clause WHERE clause are allowed. The INTO new_table_name clause of SELECT is not allowed when the new_table_name refers to a remote table.
• When specifying a large object (LOB) column from a remote table as an item in the select_list of a SELECT statement, the SELECT statement cannot contain an ORDER BY clause.
• The IS NULL and IS NOT NULL predicates cannot reference large object (LOB) columns in a remote table.
• GROUP BY ALL is not allowed in a distributed query.
• INSERT statements are allowed against remote tables as long as the provider meets the OLE DB requirements for INSERT statements. For more information, see INSERT Requirements for OLE DB Providers.
• UPDATE and DELETE statements are allowed against remote tables if the provider meets the OLE DB interface requirements on the specified table. For more information about OLE DB interface requirements and the conditions under which a remote table can have data updated or deleted, see UPDATE and DELETE Requirements for OLE DB Providers.
• A remote table can be updated or deleted through a cursor defined on a distributed query when the remote table is specified in the UPDATE or DELETE statement (UPDATE or DELETE remote_table WHERE CURRENT OF cursor_name) if the provider meets the conditions for updatability on the remote table. For more information, see Using Cursors with Distributed Queries.
• READTEXT, WRITETEXT, and UPDATETEXT statements are not supported against remote tables.
• Columns with large object data types (such as text, ntext, or image) cannot be referenced in update or insert operations if the provider is instantiated outside the Microsoft® SQL Server™ process (AllowInProcess is 0 or not specified). For more information about AllowInProcess, see Configuring OLE DB Providers for Distributed Queries.
• Data Definition Language statements (such as CREATE, ALTER, or DROP statements) are not allowed against linked servers.
• No other database-level operations or statements are allowed on linked servers.
• STATIC or INSENSITIVE cursors can reference remote tables. KEYSET cursors can reference remote tables if the OLE DB provider meets the requirements documented in Keyset-driven Cursors Requirements for OLE DB Providers. No other type of cursor can reference a remote table.