Guidelines for Using Distributed Queries
As long as the provider supports the required OLE DB interfaces, each class of Transact-SQL statement mentioned in this topic is allowed.
Transact-SQL Statements
The following is a list of the subsets of the Transact-SQL language that are allowed on remote tables accessed through linked server-based names or ad hoc names:
All queries that have 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.
In SELECT, INSERT, UPDATE, and DELETE statements, columns in remote tables cannot be qualified with a single-part or four-part table name. The remote tables should be aliased in the FROM clause, and the alias name should be used to qualify the column name.
Tables that have xml columns cannot be queried, even if the query accesses non-xml columns of the table.
When a large object (LOB) column from a remote table is specified 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 LOB columns in a remote table.
GROUP BY ALL is not allowed in a distributed query when the query also has a WHERE clause. GROUP BY without specifying ALL is supported.
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.
NULL constants cannot be inserted into a timestamp column through a distributed query.
$IDENTITY and $ROWGUIDCOL are not supported with distributed queries. Explicit values cannot be inserted into identity columns in remote tables.
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, 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 that have 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 SQL Server process, provider option AllowInProcess is 0. For more information, 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.
An EXECUTE statement can send a pass-through command to a linked server by using the AT keyword. The ability to execute the statement depends upon the RPC configuration of the linked server and the ability of the linked server to honor the statement execution.
No other database-level operations or statements are allowed on linked servers.
Other Guidelines
The following are some additional restrictions and guidelines:
STATIC or INSENSITIVE cursors can reference remote tables. KEYSET cursors can reference remote tables if the OLE DB provider meets certain requirements. For more information about these requirements, see Keyset-Driven Cursors Requirements for OLE DB Providers. No other type of cursor can reference a remote table.
Stored procedures are supported only against SQL Server data sources.
A connection must have the ANSI_NULLS and ANSI_WARNINGS options set ON before the connection can execute distributed queries. For more information, see SET ANSI_DEFAULTS (Transact-SQL).
To create the best query plans when you are using a table on a linked server, the query processor must have data distribution statistics from the linked server. Users that have limited permissions on any columns of the table might not have sufficient permissions to obtain all the useful statistics, and might receive a less efficient query plan and experience poor performance. If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server.