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.
  
  
• 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.
  
  
• 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 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.
  
  
• 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 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™ 2000 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.
  
  
• 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 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.